SQLite Forum

Virtual Table Error
Login

Virtual Table Error

(1) By Keith Medcalf (kmedcalf) on 2020-10-07 07:19:18 [link] [source]

I do not know if this is specific to the CSV module or is something generic. Here is a small test case. Note that the max(x) returns the wrong result for the virtual table vs the real table even though the plans are (from what I can tell) equivalent.

file = test.csv

1,1
4,4
3,3
2,2
1,1
8,8
7,7

Session:

SQLite version 3.34.0 2020-10-05 19:05:20
Enter ".help" for usage hints.
sqlite> create virtual table text using csv(filename=test.csv,schema='create table test(x,y)',columns=2);
sqlite> select * from text;
1|1
4|4
3|3
2|2
1|1
8|8
7|7
sqlite> create table test as select * from text;
sqlite> select * from test;
1|1
4|4
3|3
2|2
1|1
8|8
7|7
sqlite> .eqp full
sqlite> select max(x) from text;
QUERY PLAN
`--SEARCH TABLE text VIRTUAL TABLE INDEX 0: (~24 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     19    0                    0   Start at 19
1     Null           0     1     2                    0   r[1..2]=NULL
2     VOpen          0     0     0     vtab:2BE057595E0  0
3     Explain        3     0     0     SEARCH TABLE text VIRTUAL TABLE INDEX 0: (~24 rows)  0
4     Noop           0     0     0                    0   Begin WHERE-loop0: text
5     Integer        0     3     0                    0   r[3]=0
6     Integer        0     4     0                    0   r[4]=0
7     VFilter        0     14    3                    0   iplan=r[3] zplan=''
8       Noop           0     0     0                    0   Begin WHERE-core
9       VColumn        0     0     5                    0   r[5]=vcolumn(0); text.x
10      CollSeq        0     0     0     BINARY-8       0
11      AggStep        0     5     1     max(1)         1   accum=r[1] step(r[5])
12      Noop           0     0     0                    0   End WHERE-core
13    VNext          0     8     0                    0
14    Noop           0     0     0                    0   End WHERE-loop0: text
15    AggFinal       1     1     0     max(1)         0   accum=r[1] N=1
16    Copy           1     6     0                    0   r[6]=r[1]
17    ResultRow      6     1     0                    0   output=r[6]
18    Halt           0     0     0                    0
19    Transaction    0     0     2     0              1   usesStmtJournal=0
20    Goto           0     1     0                    0
7
sqlite> select max(x) from test;
QUERY PLAN
`--SEARCH TABLE test (~1048576 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     18    0                    0   Start at 18
1     Null           0     1     2                    0   r[1..2]=NULL
2     OpenRead       0     2     0     1              0   root=2 iDb=0; test
3     ColumnsUsed    0     0     0     1              0
4     Explain        4     0     0     SEARCH TABLE test (~1048576 rows)  0
5     Noop           0     0     0                    0   Begin WHERE-loop0: test
6     Rewind         0     13    0                    0
7       Noop           0     0     0                    0   Begin WHERE-core
8       Column         0     0     3                    0   r[3]=test.x
9       CollSeq        0     0     0     BINARY-8       0
10      AggStep        0     3     1     max(1)         1   accum=r[1] step(r[3])
11      Noop           0     0     0                    0   End WHERE-core
12    Next           0     7     0                    1
13    Noop           0     0     0                    0   End WHERE-loop0: test
14    AggFinal       1     1     0     max(1)         0   accum=r[1] N=1
15    Copy           1     4     0                    0   r[4]=r[1]
16    ResultRow      4     1     0                    0   output=r[4]
17    Halt           0     0     0                    0
18    Transaction    0     0     2     0              1   usesStmtJournal=0
19    Goto           0     1     0                    0
8
sqlite> select max(x) from (select x from text order by x);
QUERY PLAN
|--CO-ROUTINE 1
|  |--SCAN TABLE text VIRTUAL TABLE INDEX 0: (~24 rows)
|  `--USE TEMP B-TREE FOR ORDER BY
`--SEARCH SUBQUERY 1 (~24 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     39    0                    0   Start at 39
1     InitCoroutine  1     23    2                    0   subquery_1
2     SorterOpen     2     3     0     k(1,B)         0
3     VOpen          1     0     0     vtab:2BE057595E0  0
4     Explain        4     0     0     SCAN TABLE text VIRTUAL TABLE INDEX 0: (~24 rows)  0
5     Noop           0     0     0                    0   Begin WHERE-loop0: text
6     Integer        0     2     0                    0   r[2]=0
7     Integer        0     3     0                    0   r[3]=0
8     VFilter        1     15    2                    0   iplan=r[2] zplan=''
9       Noop           0     0     0                    0   Begin WHERE-core
10      VColumn        1     0     4                    0   r[4]=vcolumn(0); text.x
11      MakeRecord     4     1     6                    0   r[6]=mkrec(r[4])
12      SorterInsert   2     6     4     1              0   key=r[6]
13      Noop           0     0     0                    0   End WHERE-core
14    VNext          1     9     0                    0
15    Noop           0     0     0                    0   End WHERE-loop0: text
16    OpenPseudo     3     7     3                    0   3 columns in r[7]
17    SorterSort     2     22    0                    0
18      SorterData     2     7     3                    0   r[7]=data
19      Column         3     0     5                    0   r[5]=x
20      Yield          1     0     0                    0
21    SorterNext     2     18    0                    0
22    EndCoroutine   1     0     0                    0
23    Null           0     8     9                    0   r[8..9]=NULL
24    Explain        24    0     0     SEARCH SUBQUERY 1 (~24 rows)  0
25    Noop           0     0     0                    0   Begin WHERE-loop0: subquery_1
26    InitCoroutine  1     0     2                    0
27      Yield          1     34    0                    0   next row of subquery_1
28      Noop           0     0     0                    0   Begin WHERE-core
29      Copy           5     10    0                    0   r[10]=r[5]; subquery_1.x
30      CollSeq        0     0     0     BINARY-8       0
31      AggStep        0     10    8     max(1)         1   accum=r[8] step(r[10])
32      Noop           0     0     0                    0   End WHERE-core
33    Goto           0     27    0                    0
34    Noop           0     0     0                    0   End WHERE-loop0: subquery_1
35    AggFinal       8     1     0     max(1)         0   accum=r[8] N=1
36    Copy           8     11    0                    0   r[11]=r[8]
37    ResultRow      11    1     0                    0   output=r[11]
38    Halt           0     0     0                    0
39    Transaction    0     0     2     0              1   usesStmtJournal=0
40    Goto           0     1     0                    0
8
sqlite>

(2) By anonymous on 2020-10-07 10:51:48 in reply to 1 [link] [source]

Some additional testcases:

create temporary view vtext as select * from text order by x;
select max(x) as max_from_view from vtext;

┌───────────────┐
│ max_from_view │
├───────────────┤
│ 8             │
└───────────────┘

Add a line 0,0 to test.csv:

select max(x) from text;

Now max = 0 .....

Repeat with a line 9,9:

select max(x) from text;

Now max = 9 .....

(3) By Ulrich Telle (utelle) on 2020-10-07 11:22:49 in reply to 1 [source]

It seems that always the x value from the last line of the CSV file is returned for SELECT MAX(x) FROM text;. If you change the SQL command to SELECT MIN(x) FROM text;, you get the same result as for the MAX function.

Obviously, there is something going wrong on evaluating the aggregate function MIN or MAX.

(4) By Richard Hipp (drh) on 2020-10-07 11:25:56 in reply to 1 [link] [source]