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

Reply via email to