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