Re: Patch: VTable Column Affinity Question and Change Request

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Patch: VTable Column Affinity Question and Change Request

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

Hick Gunter
>Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Keith Medcalf
>Betreff: [EXTERNAL] Re: [sqlite] Patch: VTable Column Affinity Question and Change Request
>
>
>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.

Very nice. but detrimental for our use case. Please refrain from adding this tot he distribution by default.

We are almost exclusively using virtual tables to allow queries against our internal data sources, which are C language structs and thus strictly typed. The column affinities provided by the VTab implementations are used for documentation purposes and the xColumn methods always return the same type (calling the "wrong" sqlite3_result function is considered a programming error). Coercing the returned value to the same type would be just a waste of memory and CPU cycles.

We do have our own implementation of csv for export/import purposes (which predates the c orresponding SQLite features) that also allow changing the schema by providing default values for new columns. But this is only used in the context of INSERT INTO ... SELECT which applies the affinity of the target table.


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

Dominique Devienne
On Thu, Feb 6, 2020 at 9:32 AM Hick Gunter <[hidden email]> wrote:
> >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.
>
> Very nice. but detrimental for our use case. Please refrain from adding this tot he distribution by default.
> We are almost exclusively using virtual tables to allow queries against our internal data sources, which are C language structs and thus strictly typed. The column affinities provided by the VTab implementations are used for documentation purposes and the xColumn methods always return the same type (calling the "wrong" sqlite3_result function is considered a programming error). Coercing the returned value to the same type would be just a waste of memory and CPU cycles.

+1. I fear what it would do to our app, also making extensive use of vtables.
Like Gunter mentions, the type is there more for documentation, I'm
unsure "what havoc this could wreak".

Note that our vtables are all read-only, if that matters here. It's
unclear to me if the above applies to writes only,
or also applies to reads. If to writes only, then I don't care much at
the moment, although I might in the future,
and would likely prefer seeing the raw value in my code, than the
result of affinity-coercion. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

Nelson, Erik - 2-2
In reply to this post by Hick Gunter
Hick Gunter wrote on  Thursday, February 6, 2020 3:32 AM

>We are almost exclusively using virtual tables to allow queries against our internal data sources,
>which are C language structs and thus strictly typed. The column affinities provided by the VTab
>implementations are used for documentation purposes and the xColumn methods always return
>the same type (calling the "wrong" sqlite3_result function is considered a programming error).
>Coercing the returned value to the same type would be just a waste of memory and CPU cycles.

That sounds really cool- is it something that could possibly be added  to the list of vtables? (https://www.sqlite.org/vtablist.html)

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

Hick Gunter
Unfortunately no, since the code is all proprietary and includes extensive generated code.


-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nelson, Erik - 2
Gesendet: Donnerstag, 6. Februar 2020 14:57
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

Hick Gunter wrote on  Thursday, February 6, 2020 3:32 AM

>We are almost exclusively using virtual tables to allow queries against
>our internal data sources, which are C language structs and thus
>strictly typed. The column affinities provided by the VTab
>implementations are used for documentation purposes and the xColumn methods always return the same type (calling the "wrong" sqlite3_result function is considered a programming error).
>Coercing the returned value to the same type would be just a waste of memory and CPU cycles.

That sounds really cool- is it something that could possibly be added  to the list of vtables? (https://www.sqlite.org/vtablist.html)

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

Hick Gunter
In reply to this post by Dominique Devienne
>Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
>
>On Thu, Feb 6, 2020 at 9:32 AM Hick Gunter <[hidden email]> wrote:
>> >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.
>>
>> Very nice. but detrimental for our use case. Please refrain from adding this tot he distribution by default.
>> We are almost exclusively using virtual tables to allow queries against our internal data sources, which are C language structs and thus strictly typed. The column affinities provided by the VTab implementations are used for documentation purposes and the xColumn methods always return the same type (calling the "wrong" sqlite3_result function is considered a programming error). Coercing the returned value to the same type would be just a waste of memory and CPU cycles.
>
>+1. I fear what it would do to our app, also making extensive use of vtables.
>Like Gunter mentions, the type is there more for documentation, I'm unsure "what havoc this could wreak".
>
>Note that our vtables are all read-only, if that matters here. It's unclear to me if the above applies to writes only, or also applies to reads. If to writes only, then I don't care much at the moment, although I might in the future, and would likely prefer seeing the raw value in my code, than the result of affinity-coercion. --DD

The patch affects the value returned from the VTable implementation if affinities are provided by the create table statement from the xCreate method


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users