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
