From your output: .version SQLite 3.23.0 2018-03-24 13:24:02 cf171abe954a5f25262161dd69f2e8cecdbf9446c3f6b298201507dbc743567e zlib version 1.2.3 gcc-4.0.1 (Apple Inc. build 5465)
The release date of 3.23.0 was April 2nd. So are you using in-testing code rather than the full release? -----Original Message----- From: David Raymond Sent: Tuesday, April 10, 2018 10:41 AM To: SQLite mailing list Subject: RE: [sqlite] An artificial query whose outcome is changed after left join reduction That's weird. My compiled CLI for 3.23 gets what you're showing for 3.22 SQLite version 3.23.0 2018-04-02 11:04:16 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .version SQLite 3.23.0 2018-04-02 11:04:16 736b53f57f70b23172c30880186dce7ad9baa3b74e3838cae5847cffb98f5cd2 gcc-7.3.0 sqlite> create table t1(a integer primary key); --EQP-- 0,0,0,SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> create table t2(a integer primary key, b); --EQP-- 0,0,0,SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> insert into t1 values(1),(2),(3); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> insert into t2 values(1,10),(2,2); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> select a, b ...> from t1 ...> left join t2 using (a) ...> where a in (b,3) ...> ; --EQP-- 0,0,0,SCAN TABLE t1 --EQP-- 0,1,1,SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) a|b 2|2 3| Run Time: real 0.015 user 0.000000 sys 0.000000 sqlite> explain select a, b from t1 left join t2 using (a) where a in (b, 3); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 23 0 00 Start at 23 1 OpenRead 0 2 0 0 00 root=2 iDb=0; t1 2 OpenRead 1 3 0 2 00 root=3 iDb=0; t2 3 Explain 0 0 0 SCAN TABLE t1 00 4 Rewind 0 22 0 00 5 Explain 0 1 1 SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) 00 6 Integer 0 1 0 00 r[1]=0; init LEFT JOIN no-match flag 7 Rowid 0 2 0 00 r[2]=rowid 8 SeekRowid 1 18 2 00 intkey=r[2]; pk 9 Integer 1 1 0 00 r[1]=1; record LEFT JOIN hit 10 Noop 0 0 0 00 begin IN expr 11 Rowid 0 3 0 00 r[3]=rowid 12 Column 1 1 4 00 r[4]=t2.b 13 Eq 3 15 4 44 if r[4]==r[3] goto 15 14 Ne 3 18 5 54 if r[5]!=r[3] goto 18; end IN expr 15 Rowid 0 6 0 00 r[6]=rowid 16 Column 1 1 7 00 r[7]=t2.b 17 ResultRow 6 2 0 00 output=r[6..7] 18 IfPos 1 21 0 00 if r[1]>0 then r[1]-=0, goto 21 19 NullRow 1 0 0 00 20 Goto 0 9 0 00 21 Next 0 5 0 01 22 Halt 0 0 0 00 23 Transaction 0 0 2 0 01 usesStmtJournal=0 24 Integer 3 5 0 00 r[5]=3 25 Goto 0 1 0 00 Run Time: real 0.047 user 0.000000 sys 0.000000 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of E.Pasma Sent: Tuesday, April 10, 2018 10:24 AM To: SQLite mailing list Subject: [sqlite] An artificial query whose outcome is changed after left join reduction Hello, the nature of this case is purely artificial and I thought it is worth considering in the light of real world problem as reported by Raphael Michel. E Pasma .version SQLite 3.23.0 2018-03-24 13:24:02 cf171abe954a5f25262161dd69f2e8cecdbf9446c3f6b298201507dbc743567e zlib version 1.2.3 gcc-4.0.1 (Apple Inc. build 5465) create table t1(a integer primary key); create table t2(a integer primary key, b); insert into t1 values(1),(2),(3); insert into t2 values(1,10),(2,2); select a, b from t1 left join t2 using (a) where a in (b,3) ; 2|2 In version 3.22: 2|2 3| _______________________________________________ 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
