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 <[email protected]> On
>Behalf Of Keith Medcalf
>Sent: Wednesday, 5 February, 2020 10:58
>To: SQLite Users ([email protected]) <sqlite-
>[email protected]>
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
