An artificial query whose outcome is changed after left join reduction

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

An artificial query whose outcome is changed after left join reduction

E.Pasma
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: An artificial query whose outcome is changed after left join reduction

David Raymond
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: An artificial query whose outcome is changed after left join reduction

David Raymond
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: An artificial query whose outcome is changed after left join reduction

Richard Hipp-3
In reply to this post by E.Pasma
On 4/10/18, E.Pasma <[hidden email]> wrote:
> 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

cf171abe9 is a pre-release snapshot.  See
https://www.sqlite.org/src/timeline?c=cf171abe&n=12 for context.
There is another fix a couple of hours later (e88cf3d4) that addresses
the issue you identify here.  This later fix was in the official
3.23.0 release.

Thanks for the report, though.  Bug reports are always welcomed, even
when they have already been fixed.  :-)

We are currently in testing for a 3.23.1 patch release to fix the
problem identified by Raphael.  See the checklist at
https://www.sqlite.org/checklists/3230100/index

Off Topic Addendum:

Last night I wrote up a new document [1] on why the SQLite project
does not use Git.  The URL above
(https://www.sqlite.org/src/timeline?c=cf171abe&n=12) is a good
example of Reason #1.  I bring this up simply because both the article
and the URL are both fresh in my mind.

[1] https://sqlite.org/whynotgit.html

> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: An artificial query whose outcome is changed after left join reduction

Graham Holden
In reply to this post by E.Pasma
Off-topic addendum minor typo:
"They both store content has a sequence"
in the first paragraph should be "as".


Sent from my Samsung Galaxy S7 - powered by Three
-------- Original message --------From: Richard Hipp <[hidden email]> Date: 10/04/2018  15:50  (GMT+00:00) To: SQLite mailing list <[hidden email]> Subject: Re: [sqlite] An artificial query whose outcome is changed after
  left join reduction

Off Topic Addendum:

Last night I wrote up a new document [1] on why the SQLite project
does not use Git.  The URL above
(https://www.sqlite.org/src/timeline?c=cf171abe&n=12) is a good
example of Reason #1.  I bring this up simply because both the article
and the URL are both fresh in my mind.

[1] https://sqlite.org/whynotgit.html

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users