On 12/14/19 11:12 AM, Simon Slavin wrote:
> On 14 Dec 2019, at 10:46am, František Kučera <konfere...@frantovo.cz> wrote:
>
>> SELECT cast(dump+100 AS integer) FROM fstab;
>>
>> the sqlite3_column_decltype() still does not return the integer type.
>>
>> Would it be possible to modify this function or add a new one, to tell the
>> correct type at least if there is an explicit cast like this in given query?
> It works fine for me:
>
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE t (a TEXT, b INTEGER, c REAL);
> sqlite> INSERT INTO t VALUES ('123', 123, 123.4);
> sqlite> INSERT INTO t VALUES (CAST ('456' AS INTEGER), CAST (456 AS INTEGER),
> CAST (456.7 AS INTEGER));
> sqlite> SELECT a,typeof(a),b,typeof(b),c,typeof(c) FROM t;
> 123|text|123|integer|123.4|real
> 456|text|456|integer|456.0|real
> sqlite> SELECT cast(a AS INTEGER),typeof(cast(a AS INTEGER)),cast(b AS
> INTEGER),typeof(cast(b AS INTEGER)),cast(c AS INTEGER),typeof(cast(c AS
> INTEGER)) FROM t;
> 123|integer|123|integer|123|integer
> 456|integer|456|integer|456|integer
>
> When you do your CAST when you store, and the column type is compatible with
> the input value, the column type is what you declared the column type to be.
>
> But whether you do your cast() when you recall, the column type is always
> INTEGER. Which is what you want.
What he wants is different. He takes a basically arbitrary database
(user provided) and an arbitrary SQL statement (also user provided) and
he wants to determine what type a given column will present.
He is willing to assume that columns hold their declared data type
(otherwise the problem is truly impossible), and if the results goes
back to just a column, the answer is simple, the declared type of the
column (even though in many cases, the answer could also be NULL). The
problem is that there is no built in algebra to deduce what type an
expression will produce (assuming you know the types of the inputs), in
part because in SQLite you can't 'know' the type that the input would
be, and in part because sometimes result type will depend on the values
received.
The only answers I can think of are
1) retrieve all the results, taking whatever type SQLite says that value
is, and then process all the results for a give column to figure out
what (and if) that column produces (if because it might be inconsistent,
so you need to respond 'mixed' and maybe handle mixed INTEGER and REAL
some way).
2) Parse the expression yourself and determine the types (and know ahead
of time if there are problem columns). There might be ways to use some
internals of SQLite to help, but SQLite isn't going to do the job
itself, as it has no need for that answer (as it is based on assumptions
that SQLite doesn't make on the data).
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
