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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
