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

Reply via email to