The expression is calculated as given in each case. The difference being that a constant constraint is recognized in the query planner (and thus calculated once with the result being saved), whereas a constant expression as a return value is not.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Mai 2017 14:58 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Does prepare do arithmetic? Thanks Clemens, that clears that up. I’m still left wondering though why it calculates 2+2 every step in ‘select 2+2 from Tbl;’ but only once in ‘select * from Tbl where Col=2+2;’ I’m writing code that makes certain changes to the SQL before it submits it to sqlite3_prepare. Initially I assumed prepare took care of any arithmetic but now I’m wondering if I my code should do so before submitting to prepare. From: Clemens Ladisch<mailto:clem...@ladisch.de> Sent: 29 May 2017 12:54 To: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Does prepare do arithmetic? 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at 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. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
