x wrote: > The explain for > > Select * from Tbl where Col = (select min(OtherCol) from OtherTbl); > > I’m having problems with.
EXPLAIN Select * from Tbl where Col = (select min(OtherCol) from OtherTbl); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 23 0 00 Start at 23 1 OpenRead 0 2 0 2 00 root=2 iDb=0; tbl 2 Rewind 0 22 0 00 3 Column 0 0 1 00 r[1]=tbl.col 4 Once 0 17 0 00 5 Null 0 3 3 00 r[3..3]=NULL; Init subquery result 6 Integer 1 4 0 00 r[4]=1; LIMIT counter 7 Null 0 5 6 00 r[5..6]=NULL 8 OpenRead 1 3 0 1 00 root=3 iDb=0; OtherTbl 9 Rewind 1 14 0 00 10 Column 1 0 7 00 r[7]=OtherTbl.OtherCol 11 CollSeq 0 0 0 (BINARY) 00 12 AggStep0 0 7 5 min(1) 01 accum=r[5] step(r[7]) 13 Next 1 10 0 01 14 AggFinal 5 1 0 min(1) 00 accum=r[5] N=1 15 Copy 5 3 0 00 r[3]=r[5] 16 DecrJumpZero 4 17 0 00 if (--r[4])==0 goto 17 17 Ne 3 21 1 (BINARY) 51 if r[1]!=r[3] goto 21 18 Column 0 0 8 00 r[8]=tbl.col 19 Column 0 1 9 00 r[9]=tbl.y 20 ResultRow 8 2 0 00 output=r[8..9] 21 Next 0 3 0 01 22 Halt 0 0 0 00 23 Transaction 0 0 2 0 01 usesStmtJournal=0 24 Goto 0 1 0 00 There are two nested loops; the outer one scans Tbl, the inner one scans OtherTbl. However, the database has determined that the result of the inner loop does not depend on the current row in the outer loop, so the inner loop is executed only once (the Once opcode in line 4 jumps to line 17 when it is encountered the second time). > I’m even having problems with explain query plan (shown below) for that one. > > Scan table Tbl > Execute scalar subquery 1 > Scan table OtherTbl > > Why would it scan Tbl first? It doesn't; it _begins_ scanning Tbl first. If Tbl does not happen to have any rows, the inner loop would never by executed. With nesting like this, it would be hard to define which (sub)query is executed first. (And then there are queries that use coroutines ...) Regards, Clemens > This communication (including any attachments) is intended for the use of the > intended recipient(s) only and may contain information that is confidential, > privileged or legally protected. Any unauthorized use or dissemination of > this communication is strictly prohibited. If you have received this > communication in error, please immediately notify the sender by return e-mail > message and delete all copies of the original communication. Thank you for > your cooperation. This e-mail contains public information intended for any subscriber of this mailing list and for anybody else who bothers to read it; it will be copied, disclosed and distributed to the public. If you think you are not the intended recipient, please commit suicide immediately. These terms apply also to any e-mails quoted in, referenced from, or answering this e-mail, and supersede any confidentiality notices in those e-mails. Additionally, confidentiality notices in those e-mails will incur legal processing fees of $42 per line; you have agreed to this by reading this confidentiality notice. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
