Sorting output can have different (incorrect) output from sqlite #2923
Description
While working on migrating to a larger test database with broader schema and more rows, I discovered that a query:
select u.first_name, length(group_concat(u.last_name)) from users u group by u.first_name order by max(u.email) desc limit 5;Has different output from SQLite.
To reproduce, going to have to wait for my upcoming PR which changes the test database.
Info:
SQLite3:
Andrew|947
Patricia|467
Donna|286
Laura|580
Taylor|228
Plan:
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 63 0 0 Start at 63
1 OpenEphemeral 1 4 0 k(1,-B) 0 nColumn=4
2 Integer 5 1 0 0 r[1]=5; LIMIT counter
3 SorterOpen 2 3 0 k(1,-B) 0
4 Integer 0 3 0 0 r[3]=0; clear abort flag
5 Null 0 6 6 0 r[6..6]=NULL
6 Gosub 5 54 0 0
7 OpenRead 0 2 0 4 0 root=2 iDb=0; users
8 Rewind 0 15 0 0
9 Column 0 1 13 0 r[13]= cursor 0 column 1
10 Column 0 2 14 0 r[14]= cursor 0 column 2
11 Column 0 3 15 0 r[15]= cursor 0 column 3
12 MakeRecord 13 3 16 0 r[16]=mkrec(r[13..15])
13 SorterInsert 2 16 0 0 key=r[16]
14 Next 0 9 0 1
15 OpenPseudo 3 16 3 0 3 columns in r[16]
16 SorterSort 2 57 0 0 GROUP BY sort
17 SorterData 2 16 3 0 r[16]=data
18 Column 3 0 7 0 r[7]= cursor 3 column 0
19 Compare 6 7 1 k(1,-B) 0 r[6] <-> r[7]
20 Jump 21 25 21 0
21 Gosub 4 38 0 0 output one row
22 Move 7 6 1 0 r[6]=r[7]
23 IfPos 3 57 0 0 if r[3]>0 then r[3]-=0, goto 57; check abort flag
24 Gosub 5 54 0 0 reset accumulator
25 Column 3 1 17 0 r[17]=users.last_name
26 AggStep 0 17 11 group_concat(1) 1 accum=r[11] step(r[17])
27 Column 3 2 17 0 r[17]=users.email
28 CollSeq 18 0 0 BINARY-8 0
29 AggStep 0 17 12 max(1) 1 accum=r[12] step(r[17])
30 If 18 32 0 0
31 Column 3 0 8 0 r[8]=users.first_name
32 Integer 1 2 0 0 r[2]=1; indicate data in accumulator
33 SorterNext 2 17 0 0
34 Gosub 4 38 0 0 output final row
35 Goto 0 57 0 0
36 Integer 1 3 0 0 r[3]=1; set abort flag
37 Return 4 0 0 0
38 IfPos 2 40 0 0 if r[2]>0 then r[2]-=0, goto 40; Groupby result generator entry point
39 Return 4 0 0 0
40 AggFinal 11 1 0 group_concat(1) 0 accum=r[11] N=1
41 AggFinal 12 1 0 max(1) 0 accum=r[12] N=1
42 Copy 12 19 0 0 r[19]=r[12]
43 Sequence 1 20 0 0 r[20]=cursor[1].ctr++
44 IfNotZero 1 48 0 0 if r[1]!=0 then r[1]--, goto 48
45 Last 1 0 0 0
46 IdxLE 1 53 19 1 0 key=r[19]
47 Delete 1 0 0 0
48 Copy 8 21 0 0 r[21]=r[8]
49 SCopy 11 17 0 0 r[17]=r[11]
50 Function 0 17 22 length(1) 0 r[22]=func(r[17])
51 MakeRecord 19 4 23 0 r[23]=mkrec(r[19..22])
52 IdxInsert 1 23 19 4 0 key=r[23]
53 Return 4 0 0 0 end groupby result generator
54 Null 0 8 12 0 r[8..12]=NULL
55 Integer 0 2 0 0 r[2]=0; indicate accumulator empty
56 Return 5 0 0 0
57 Sort 1 62 0 0
58 Column 1 3 22 0 r[22]=length(group_concat(u.last_name))
59 Column 1 2 21 0 r[21]=u.first_name
60 ResultRow 21 2 0 0 output=r[21..22]
61 Next 1 58 0 0
62 Halt 0 0 0 0
63 Transaction 0 0 17 0 1 usesStmtJournal=0
64 Null 0 6 6 0 r[6..6]=NULL
65 Goto 0 1 0 0
turso:
Result:
Andrew|947
Patricia|467
Donna|286
Laura|580
Bobby|96
Plan:
addr opcode p1 p2 p3 p4 p5 comment
---- ----------------- ---- ---- ---- ------------- -- -------
0 Init 0 61 0 0 Start at 61
1 Integer 5 1 0 0 r[1]=5
2 SorterOpen 0 1 0 k(1,-Binary) 0 cursor=0
3 Null 0 10 11 0 r[10..11]=NULL
4 SorterOpen 1 3 0 k(1,B) 0 cursor=1
5 Integer 0 7 0 0 r[7]=0; clear group by abort flag
6 Null 0 8 0 0 r[8]=NULL; initialize group by comparison registers to NULL
7 Gosub 16 49 0 0 ; go to clear accumulator subroutine
8 OpenRead 3 2 0 0 table=users, root=2, iDb=0
9 Rewind 3 16 0 0 Rewind table users
10 Column 3 1 13 0 r[13]=users.first_name
11 Column 3 2 14 0 r[14]=users.last_name
12 Column 3 3 15 0 r[15]=users.email
13 MakeRecord 13 3 12 0 r[12]=mkrec(r[13..15])
14 SorterInsert 1 12 0 0 0 key=r[12]
15 Next 3 10 0 0
16 OpenPseudo 2 12 3 0 3 columns in r[12]
17 SorterSort 1 34 0 0
18 SorterData 1 12 2 0 r[12]=data
19 Column 2 0 17 0 r[17]=pseudo.column 0
20 Compare 8 17 1 k(1, Binary) 0 r[8..8]==r[17..17]
21 Jump 22 26 22 0 ; start new group if comparison is not equal
22 Gosub 5 38 0 0 ; check if ended group had data, and output if so
23 Move 17 8 1 0 r[8..8]=r[17..17]
24 IfPos 7 52 0 0 r[7]>0 -> r[7]-=0, goto 52; check abort flag
25 Gosub 16 49 0 0 ; goto clear accumulator subroutine
26 Column 2 1 18 0 r[18]=pseudo.column 1
27 AggStep 0 18 10 group_concat 0 accum=r[10] step(r[18])
28 Column 2 2 20 0 r[20]=pseudo.column 2
29 AggStep 0 20 11 max 0 accum=r[11] step(r[20])
30 If 6 32 0 0 if r[6] goto 32; don't emit group columns if continuing existing group
31 Column 2 0 9 0 r[9]=pseudo.column 0
32 Integer 1 6 0 0 r[6]=1; indicate data in accumulator
33 SorterNext 1 18 0 0
34 Gosub 5 38 0 0 ; emit row for final group
35 Goto 0 52 0 0 ; group by finished
36 Integer 1 7 0 0 r[7]=1
37 Return 5 0 0 0
38 IfPos 6 40 0 0 r[6]>0 -> r[6]-=0, goto 40; output group by row subroutine start
39 Return 5 0 0 0
40 AggFinal 0 10 0 group_concat 0 accum=r[10]
41 AggFinal 0 11 0 max 0 accum=r[11]
42 Copy 11 21 0 0 r[21]=r[11]
43 Copy 9 22 0 0 r[22]=r[9]
44 Copy 10 24 0 0 r[24]=r[10]
45 Function 0 24 23 length 0 r[23]=func(r[24])
46 MakeRecord 21 3 4 0 r[4]=mkrec(r[21..23])
47 SorterInsert 0 4 0 0 0 key=r[4]
48 Return 5 0 0 0
49 Null 0 9 11 0 r[9..11]=NULL; clear accumulator subroutine start
50 Integer 0 6 0 0 r[6]=0
51 Return 16 0 0 0
52 OpenPseudo 4 4 3 0 3 columns in r[4]
53 SorterSort 0 60 0 0
54 SorterData 0 4 4 0 r[4]=data
55 Column 4 1 2 0 r[2]=pseudo.column 1
56 Column 4 2 3 0 r[3]=pseudo.column 2
57 ResultRow 2 2 0 0 output=r[2..3]
58 DecrJumpZero 1 60 0 0 if (--r[1]==0) goto 60
59 SorterNext 0 54 0 0
60 Halt 0 0 0 0
61 Transaction 0 0 17 0 iDb=0 write=false
62 String8 0 19 0 , 0 r[19]=','
63 Goto 0 1 0 0