It seems that "column affinities" are not respected in Virtual Table implementations -- that is the value that is returned is the datatype provided by the the vtab_cursor sqlite3_result_* function and the "column affinity" from the vtab declaration is not applied. In effect the column affinity specified in the vtab declaration seems to be ignored (or treated as none/blob) no matter what the declaration.
Somehow, I don't think this was always the case but I could be wrong. In any case, what is the point in specifying the column affinity in the vtab declaration if it is just going to be ignored? Example, using the current tip of trunk and the ext\misc\csv.c extension with the following input file: a,b,c,d 1,2,3,4 2,3 or 4,4,5 3,4,5,6 4,5,6,7 SQLite version 3.32.0 2020-02-05 16:13:24 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create virtual table t using csv(filename='t.csv', header=on); sqlite> .mode col sqlite> .head on sqlite> select typeof(a), a, typeof(b), b, typeof(c), c, typeof(d), d from t; typeof(a) a typeof(b) b typeof(c) c typeof(d) d ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- text 1 text 2 text 3 text 4 text 2 text 3 or 4 text 4 text 5 text 3 text 4 text 5 text 6 text 4 text 5 text 6 text 7 sqlite> pragma table_xinfo(t); cid name type aff coll notnull dflt_value pk rowid autoinc hidden ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -1 INTEGER 0 1 1 0 1 0 a TEXT TEXT 0 0 0 0 0 1 b TEXT TEXT 0 0 0 0 0 2 c TEXT TEXT 0 0 0 0 0 3 d TEXT TEXT 0 0 0 0 0 sqlite> drop table t; sqlite> create virtual table t using csv(filename='t.csv', header=off, schema='create table t(a numeric, b numeric, c numeric, d numeric)'); sqlite> select typeof(a), a, typeof(b), b, typeof(c), c, typeof(d), d from t; typeof(a) a typeof(b) b typeof(c) c typeof(d) d ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- text a text b text c text d text 1 text 2 text 3 text 4 text 2 text 3 or 4 text 4 text 5 text 3 text 4 text 5 text 6 text 4 text 5 text 6 text 7 sqlite> pragma table_xinfo(t); cid name type aff coll notnull dflt_value pk rowid autoinc hidden ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -1 INTEGER 0 1 1 0 1 0 a numeric NUMERIC 0 0 0 0 0 1 b numeric NUMERIC 0 0 0 0 0 2 c numeric NUMERIC 0 0 0 0 0 3 d numeric NUMERIC 0 0 0 0 0 (note that the pragma table_xinfo is my slightly modified version that shows some additional information from the schema object) If I put the rows generated by the virtual table into a similarly declared temp table, I get the expected result: sqlite> create temporary table u(a numeric, b numeric, c numeric, d numeric); sqlite> insert into u select * from t; sqlite> select typeof(a), a, typeof(b), b, typeof(c), c, typeof(d), d from u; typeof(a) a typeof(b) b typeof(c) c typeof(d) d ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- text a text b text c text d integer 1 integer 2 integer 3 integer 4 integer 2 text 3 or 4 integer 4 integer 5 integer 3 integer 4 integer 5 integer 6 integer 4 integer 5 integer 6 integer 7 sqlite> pragma table_xinfo(u); cid name type aff coll notnull dflt_value pk rowid autoinc hidden ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -1 INTEGER 0 1 1 0 1 0 a numeric NUMERIC 0 0 0 0 0 1 b numeric NUMERIC 0 0 0 0 0 2 c numeric NUMERIC 0 0 0 0 0 3 d numeric NUMERIC 0 0 0 0 0 -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users