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>
```