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

Reply via email to