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

2018-04-10 Thread Graham Holden 
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 <d...@sqlite.org> Date: 
10/04/2018  15:50  (GMT+00:00) To: SQLite mailing list 
<sqlite-users@mailinglists.sqlite.org> 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=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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-04-10 Thread Richard Hipp 
On 4/10/18, E.Pasma  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=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=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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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

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

2018-04-10 Thread 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.00 sys 0.00

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.00 sys 0.00

sqlite> insert into t1 values(1),(2),(3);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> insert into t2 values(1,10),(2,2);
Run Time: real 0.000 user 0.00 sys 0.00

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.00 sys 0.00

sqlite> explain select a, b from t1 left join t2 using (a) where a in (b, 3);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 23000  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 Explain0 0 0 SCAN TABLE t1  00
4 Rewind 0 22000
5   Explain0 1 1 SEARCH TABLE t2 USING INTEGER PRIMARY 
KEY (rowid=?)  00
6   Integer0 1 000  r[1]=0; init LEFT 
JOIN no-match flag
7   Rowid  0 2 000  r[2]=rowid
8   SeekRowid  1 18200  intkey=r[2]; pk
9   Integer1 1 000  r[1]=1; record LEFT 
JOIN hit
10  Noop   0 0 000  begin IN expr
11  Rowid  0 3 000  r[3]=rowid
12  Column 1 1 400  r[4]=t2.b
13  Eq 3 15444  if r[4]==r[3] goto 
15
14  Ne 3 18554  if r[5]!=r[3] goto 
18; end IN expr
15  Rowid  0 6 000  r[6]=rowid
16  Column 1 1 700  r[7]=t2.b
17  ResultRow  6 2 000  output=r[6..7]
18  IfPos  1 21000  if r[1]>0 then 
r[1]-=0, goto 21
19  NullRow1 0 000
20  Goto   0 9 000
21Next   0 5 001
22Halt   0 0 000
23Transaction0 0 2 0  01  usesStmtJournal=0
24Integer3 5 000  r[5]=3
25Goto   0 1 000
Run Time: real 0.047 user 0.00 sys 0.00

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-u

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

2018-04-10 Thread 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.00 sys 0.00

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.00 sys 0.00

sqlite> insert into t1 values(1),(2),(3);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> insert into t2 values(1,10),(2,2);
Run Time: real 0.000 user 0.00 sys 0.00

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.00 sys 0.00

sqlite> explain select a, b from t1 left join t2 using (a) where a in (b, 3);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 23000  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 Explain0 0 0 SCAN TABLE t1  00
4 Rewind 0 22000
5   Explain0 1 1 SEARCH TABLE t2 USING INTEGER PRIMARY 
KEY (rowid=?)  00
6   Integer0 1 000  r[1]=0; init LEFT 
JOIN no-match flag
7   Rowid  0 2 000  r[2]=rowid
8   SeekRowid  1 18200  intkey=r[2]; pk
9   Integer1 1 000  r[1]=1; record LEFT 
JOIN hit
10  Noop   0 0 000  begin IN expr
11  Rowid  0 3 000  r[3]=rowid
12  Column 1 1 400  r[4]=t2.b
13  Eq 3 15444  if r[4]==r[3] goto 
15
14  Ne 3 18554  if r[5]!=r[3] goto 
18; end IN expr
15  Rowid  0 6 000  r[6]=rowid
16  Column 1 1 700  r[7]=t2.b
17  ResultRow  6 2 000  output=r[6..7]
18  IfPos  1 21000  if r[1]>0 then 
r[1]-=0, goto 21
19  NullRow1 0 000
20  Goto   0 9 000
21Next   0 5 001
22Halt   0 0 000
23Transaction0 0 2 0  01  usesStmtJournal=0
24Integer3 5 000  r[5]=3
25Goto   0 1 000
Run Time: real 0.047 user 0.00 sys 0.00

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