Thanks, Richard. Something triggered me when I looked at the generated code: you use the contents of register 2 for the constant value each time through the loop. What if the select looks like this, with more than one function call in the coalesce? Do you handle it properly?
SELECT coalesce(a, abs(0), abs(1), abs(2)) FROM t1; Just curious... Doug > -----Original Message----- > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> > On Behalf Of Richard Hipp > Sent: Thursday, March 12, 2020 7:41 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short- > circuit sometimes > > On 3/12/20, Doug <dougf....@comcast.net> wrote: > > Richard, what does the explain look like with your code change, > please. > > Test case: > > CREATE TABLE t1(a); > explain SELECT coalesce(a, abs(-9223372036854775808)) FROM t1; > > Before the change: > > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- -------- > ----- > 0 Init 0 10 0 00 Start at > 10 > 1 OpenRead 0 2 0 1 00 root=2 > iDb=0; t1 > 2 Explain 2 0 0 SCAN TABLE t1 00 > 3 Rewind 0 9 0 00 > 4 Column 0 0 1 00 > r[1]=t1.a > 5 NotNull 1 7 0 00 if > r[1]!=NULL goto 7 > 6 SCopy 2 1 0 00 > r[1]=r[2] > 7 ResultRow 1 1 0 00 > output=r[1] > 8 Next 0 4 0 01 > 9 Halt 0 0 0 00 > 10 Transaction 0 0 1 0 01 > usesStmtJournal=0 > 11 Int64 0 3 0 -9223372036854775808 00 > r[3]=-9223372036854775808 > 12 Function 1 3 2 abs(1) 00 > r[2]=func(r[3]) > 13 Goto 0 1 0 00 > > Notice that the abs() function is invoked in the "prologue" code. > The > prologue begins on instruction 10 and continues through the Goto > at > instruction 13. > > After the change: > > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- -------- > ----- > 0 Init 0 13 0 00 Start at > 13 > 1 OpenRead 0 2 0 1 00 root=2 > iDb=0; t1 > 2 Explain 2 0 0 SCAN TABLE t1 00 > 3 Rewind 0 12 0 00 > 4 Column 0 0 1 00 > r[1]=t1.a > 5 NotNull 1 10 0 00 if > r[1]!=NULL goto 10 > 6 Once 0 9 0 00 > 7 Int64 0 3 0 -9223372036854775808 00 > r[3]=-9223372036854775808 > 8 Function 1 3 2 abs(1) 00 > r[2]=func(r[3]) > 9 SCopy 2 1 0 00 > r[1]=r[2] > 10 ResultRow 1 1 0 00 > output=r[1] > 11 Next 0 4 0 01 > 12 Halt 0 0 0 00 > 13 Transaction 0 0 1 0 01 > usesStmtJournal=0 > 14 Goto 0 1 0 00 > > Now the prologue is just instructions 13 and 14 and omits the > abs() > function. The abs() function is now computed on instructions 7 > and 8, > but those instructions only run one time due to the "Once" opcode > on > instruction 6. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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
