SELECT result different after ANALYZE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

SELECT result different after ANALYZE

Ralf Junker
I am presenting a scenario where a SELECT produces a different result
after running ANALYZE.

To reproduce, download this database file (5.6MB, SHA1
12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):

   https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html

This SQL returns a single result row with a value of 1:

SELECT DISTINCT t2.a FROM t1
  INNER JOIN t2 ON t1.t2_id = t2.id
  WHERE t1.t2_id <> -1;

Then run ANALYZE and run the above select again. This time I receive no
result.

Assuming that SQL SELECTs should always return the same results
regardless of optimization, I assume that this might be a bug in SQLite.

Tested with the SQLite 3.21.0 CLI on Windows.

Ralf
_______________________________________________
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: SELECT result different after ANALYZE

David Raymond
Confirming it's doing the same thing for me. Taking out the distinct keyword will return a bunch of 1's, adding it in doesn't show them. Definitely something buggy here.



D:\Temp>sqlite3 "analyze_problem - Copy.db"
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.

sqlite> .timer off

sqlite> .eqp off

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|t1|t1|2|CREATE TABLE t1 (id integer primary key, t2_id integer)
index|t1_1|t1|738|CREATE INDEX t1_1 on t1  (t2_id asc)
table|t2|t2|1409|CREATE TABLE t2 (id integer primary key, a integer)
index|t2_1|t2|1423|CREATE INDEX t2_1 on t2 (a asc)

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     19    0                    00  Start at 19
1     Null           1     5     0                    08  r[5]=NULL
2     OpenRead       3     1423  0     k(2,,)         00  root=1423 iDb=0; t2_1
3     OpenRead       4     738   0     k(2,,)         00  root=738 iDb=0; t1_1
4     Explain        0     0     1     SCAN TABLE t2 USING COVERING INDEX t2_1  00
5     Rewind         3     18    1     0              00
6       Explain        0     1     0     SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)  00
7       IdxRowid       3     1     0                    00  r[1]=rowid
8       SeekGE         4     17    1     1              00  key=r[1]
9         IdxGT          4     17    1     1              00  key=r[1]
10        Column         4     0     2                    00  r[2]=t1.t2_id
11        Eq             3     16    2     (BINARY)       54  if r[2]==r[3] goto 16
12        Column         3     0     4                    00  r[4]=t2.a
13        Eq             4     16    5     (BINARY)       80  if r[5]==r[4] goto 16
14        Copy           4     5     0                    00  r[5]=r[4]
15        ResultRow      4     1     0                    00  output=r[4]
16      Next           4     9     1                    00
17    Next           3     6     0                    01
18    Halt           0     0     0                    00
19    Transaction    0     0     4     0              01  usesStmtJournal=0
20    Integer        -1    3     0                    00  r[3]=-1
21    Goto           0     1     0                    00
1

sqlite> .eqp off

sqlite> analyze;

sqlite> select * from sqlite_stat1;
tbl|idx|stat
t2|t2_1|6152 6152
t1|t1_1|248503 41

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     22    0                    00  Start at 22
1     Null           1     5     0                    08  r[5]=NULL
2     OpenRead       3     1423  0     k(2,,)         00  root=1423 iDb=0; t2_1
3     OpenRead       4     738   0     k(2,,)         00  root=738 iDb=0; t1_1
4     Explain        0     0     1     SCAN TABLE t2 USING COVERING INDEX t2_1  00
5     Rewind         3     21    1     0              00
6         Explain        0     1     0     SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)  00
7         IdxRowid       3     1     0                    00  r[1]=rowid
8         SeekGE         4     17    1     1              00  key=r[1]
9           IdxGT          4     17    1     1              00  key=r[1]
10          Column         4     0     2                    00  r[2]=t1.t2_id
11          Eq             3     16    2     (BINARY)       54  if r[2]==r[3] goto 16
12          Column         3     0     4                    00  r[4]=t2.a
13          Eq             4     16    5     (BINARY)       80  if r[5]==r[4] goto 16
14          Copy           4     5     0                    00  r[5]=r[4]
15          ResultRow      4     1     0                    00  output=r[4]
16        Next           4     9     1                    00
17        Column         3     0     6                    00  r[6]=
18        SeekGT         3     21    6     1              00  key=r[6]
19      Goto           1     6     0                    00
20    Next           3     6     0                    01
21    Halt           0     0     0                    00
22    Transaction    0     0     5     0              01  usesStmtJournal=0
23    Integer        -1    3     0                    00  r[3]=-1
24    Goto           0     1     0                    00

sqlite>




-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Ralf Junker
Sent: Monday, November 20, 2017 10:40 AM
To: [hidden email]
Subject: [sqlite] SELECT result different after ANALYZE

I am presenting a scenario where a SELECT produces a different result
after running ANALYZE.

To reproduce, download this database file (5.6MB, SHA1
12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):

   https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html

This SQL returns a single result row with a value of 1:

SELECT DISTINCT t2.a FROM t1
  INNER JOIN t2 ON t1.t2_id = t2.id
  WHERE t1.t2_id <> -1;

Then run ANALYZE and run the above select again. This time I receive no
result.

Assuming that SQL SELECTs should always return the same results
regardless of optimization, I assume that this might be a bug in SQLite.

Tested with the SQLite 3.21.0 CLI on Windows.

Ralf
_______________________________________________
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: SELECT result different after ANALYZE

David Raymond
In reply to this post by Ralf Junker
3.18.0 gets it correct, 3.19.0 gets it wrong.


-----Original Message-----
From: David Raymond
Sent: Monday, November 20, 2017 11:03 AM
To: 'SQLite mailing list'
Subject: RE: [sqlite] SELECT result different after ANALYZE

Confirming it's doing the same thing for me. Taking out the distinct keyword will return a bunch of 1's, adding it in doesn't show them. Definitely something buggy here.



D:\Temp>sqlite3 "analyze_problem - Copy.db"
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.

sqlite> .timer off

sqlite> .eqp off

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|t1|t1|2|CREATE TABLE t1 (id integer primary key, t2_id integer)
index|t1_1|t1|738|CREATE INDEX t1_1 on t1  (t2_id asc)
table|t2|t2|1409|CREATE TABLE t2 (id integer primary key, a integer)
index|t2_1|t2|1423|CREATE INDEX t2_1 on t2 (a asc)

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     19    0                    00  Start at 19
1     Null           1     5     0                    08  r[5]=NULL
2     OpenRead       3     1423  0     k(2,,)         00  root=1423 iDb=0; t2_1
3     OpenRead       4     738   0     k(2,,)         00  root=738 iDb=0; t1_1
4     Explain        0     0     1     SCAN TABLE t2 USING COVERING INDEX t2_1  00
5     Rewind         3     18    1     0              00
6       Explain        0     1     0     SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)  00
7       IdxRowid       3     1     0                    00  r[1]=rowid
8       SeekGE         4     17    1     1              00  key=r[1]
9         IdxGT          4     17    1     1              00  key=r[1]
10        Column         4     0     2                    00  r[2]=t1.t2_id
11        Eq             3     16    2     (BINARY)       54  if r[2]==r[3] goto 16
12        Column         3     0     4                    00  r[4]=t2.a
13        Eq             4     16    5     (BINARY)       80  if r[5]==r[4] goto 16
14        Copy           4     5     0                    00  r[5]=r[4]
15        ResultRow      4     1     0                    00  output=r[4]
16      Next           4     9     1                    00
17    Next           3     6     0                    01
18    Halt           0     0     0                    00
19    Transaction    0     0     4     0              01  usesStmtJournal=0
20    Integer        -1    3     0                    00  r[3]=-1
21    Goto           0     1     0                    00
1

sqlite> .eqp off

sqlite> analyze;

sqlite> select * from sqlite_stat1;
tbl|idx|stat
t2|t2_1|6152 6152
t1|t1_1|248503 41

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     22    0                    00  Start at 22
1     Null           1     5     0                    08  r[5]=NULL
2     OpenRead       3     1423  0     k(2,,)         00  root=1423 iDb=0; t2_1
3     OpenRead       4     738   0     k(2,,)         00  root=738 iDb=0; t1_1
4     Explain        0     0     1     SCAN TABLE t2 USING COVERING INDEX t2_1  00
5     Rewind         3     21    1     0              00
6         Explain        0     1     0     SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)  00
7         IdxRowid       3     1     0                    00  r[1]=rowid
8         SeekGE         4     17    1     1              00  key=r[1]
9           IdxGT          4     17    1     1              00  key=r[1]
10          Column         4     0     2                    00  r[2]=t1.t2_id
11          Eq             3     16    2     (BINARY)       54  if r[2]==r[3] goto 16
12          Column         3     0     4                    00  r[4]=t2.a
13          Eq             4     16    5     (BINARY)       80  if r[5]==r[4] goto 16
14          Copy           4     5     0                    00  r[5]=r[4]
15          ResultRow      4     1     0                    00  output=r[4]
16        Next           4     9     1                    00
17        Column         3     0     6                    00  r[6]=
18        SeekGT         3     21    6     1              00  key=r[6]
19      Goto           1     6     0                    00
20    Next           3     6     0                    01
21    Halt           0     0     0                    00
22    Transaction    0     0     5     0              01  usesStmtJournal=0
23    Integer        -1    3     0                    00  r[3]=-1
24    Goto           0     1     0                    00

sqlite>




-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Ralf Junker
Sent: Monday, November 20, 2017 10:40 AM
To: [hidden email]
Subject: [sqlite] SELECT result different after ANALYZE

I am presenting a scenario where a SELECT produces a different result
after running ANALYZE.

To reproduce, download this database file (5.6MB, SHA1
12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):

   https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html

This SQL returns a single result row with a value of 1:

SELECT DISTINCT t2.a FROM t1
  INNER JOIN t2 ON t1.t2_id = t2.id
  WHERE t1.t2_id <> -1;

Then run ANALYZE and run the above select again. This time I receive no
result.

Assuming that SQL SELECTs should always return the same results
regardless of optimization, I assume that this might be a bug in SQLite.

Tested with the SQLite 3.21.0 CLI on Windows.

Ralf
_______________________________________________
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: SELECT result different after ANALYZE

R Smith
In reply to this post by Ralf Junker
Just to Add to what Ralf and David already pointed out:

Works for me on 3.18, not in 3.20.1 and more importantly,  the
sqlite_stat1 table itself seems to have zero impact, once Analyze is
run, the query always does not work, even if you drop the sqlite_stat1
table or mess with its values.


Hope the point is useful,
Cheers


On 2017/11/20 5:39 PM, Ralf Junker wrote:

> I am presenting a scenario where a SELECT produces a different result
> after running ANALYZE.
>
> To reproduce, download this database file (5.6MB, SHA1
> 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from
> now):
>
> https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html
>
> This SQL returns a single result row with a value of 1:
>
> SELECT DISTINCT t2.a FROM t1
>  INNER JOIN t2 ON t1.t2_id = t2.id
>  WHERE t1.t2_id <> -1;
>
> Then run ANALYZE and run the above select again. This time I receive
> no result.
>
> Assuming that SQL SELECTs should always return the same results
> regardless of optimization, I assume that this might be a bug in SQLite.
>
> Tested with the SQLite 3.21.0 CLI on Windows.
>
> Ralf
> _______________________________________________
> 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: SELECT result different after ANALYZE

R Smith
Apologies for the Spam, and this may be of no importance whatsoever, but
just in case it is useful...

I already mentioned that dropping/messing with the sqlite_stat1 table
doesn't help - BUT it seems if you close the connection and re-open in a
new connection (after you have dropped the sqlite_stat1 table of
course), the Query works again.

Also note: When you leave the stat1 table be, and only close and re-open
the DB (with a new connection instance), the Query still does not work,
so the stat1 table's presence does have an effect.



_______________________________________________
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: SELECT result different after ANALYZE

Richard Hipp-3
In reply to this post by David Raymond
On 11/20/17, David Raymond <[hidden email]> wrote:

>
> To reproduce, download this database file (5.6MB, SHA1
> 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):
>
>    https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html
>
> This SQL returns a single result row with a value of 1:
>
> SELECT DISTINCT t2.a FROM t1
>   INNER JOIN t2 ON t1.t2_id = t2.id
>   WHERE t1.t2_id <> -1;
>
> Then run ANALYZE and run the above select again. This time I receive no
> result.

Thank you for the bug report.

To work around this problem, please DROP all indexes on the INTEGER
PRIMARY KEY columns.  Such indexes are accomplish nothing (they will
never be used on a real query - they are just slow down inserts and
updates and take up extra space on disk) except in this case they do
appear to be confusing the query planner.

This problem will be corrected in the next release.


--
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: SELECT result different after ANALYZE

Richard Hipp-3
On 11/21/17, Richard Hipp <[hidden email]> wrote:
>
> To work around this problem, please DROP all indexes on the INTEGER
> PRIMARY KEY columns.

Except, you don't have any indexes on INTEGER PRIMARY KEY columns.  I
misread the schema.

I'll be working on some other solution for you.

--
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: SELECT result different after ANALYZE

Ralf Junker
On 21.11.2017 15:36, Richard Hipp wrote:

> I'll be working on some other solution for you.

Many thanks, but this is not necessary. I can rebuild from Fossil.

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