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 [link] [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 [source]
Fixed by check-in 54b54f02c66c5aea