Patch to Fix Column Affinity not applied to Virtual Columns. In expr.c function sqlite3ExprCodeGetColumnOfTable At or about line 3555 where the OP_Column or OP_VColumn opcode is added to the VDBE program, and the default code is generated, make this:
sqlite3VdbeAddOp3(v, op, iTabCur, x, regOut); sqlite3ColumnDefault(v, pTab, iCol, regOut); look like this: sqlite3VdbeAddOp3(v, op, iTabCur, x, regOut); if ( IsVirtual(pTab) && pTab->aCol[iCol].affinity > SQLITE_AFF_BLOB) sqlite3VdbeAddOp4(v, OP_Affinity, regOut, 1, 0, &(pTab->aCol[iCol].affinity), 1); sqlite3ColumnDefault(v, pTab, iCol, regOut); Of course, it may be that the writer of the VTable should know what they are doing and generate a VTable definition that is consistent with how their cursor methods return data, however ... this will omit the OP_Affinity if no column type was specified when the VTable was defined and most of the VTable declarations in the existing code that I looked at do not specify column affinities in the declarations. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Keith Medcalf >Sent: Wednesday, 5 February, 2020 10:58 >To: SQLite Users (sqlite-users@mailinglists.sqlite.org) <sqlite- >us...@mailinglists.sqlite.org> >Subject: [sqlite] VTable Column Affinity Question and Change Request > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users