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

Re: [sqlite] SELECT result different after ANALYZE

[Richard Hipp]

On 11/21/17, Richard Hipp  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT result different after ANALYZE

[Richard Hipp]

On 11/20/17, David Raymond  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] SELECT result different after ANALYZE

[R Smith]

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

Re: [sqlite] SELECT result different after ANALYZE

[David Raymond]

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 p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 19000  Start at 19
1 Null   1 5 008  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 Explain0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1  
00
5 Rewind 3 181 0  00
6   Explain0 1 0 SEARCH TABLE t1 USING COVERING INDEX 
t1_1 (t2_id=?)  00
7   IdxRowid   3 1 000  r[1]=rowid
8   SeekGE 4 171 1  00  key=r[1]
9 IdxGT  4 171 1  00  key=r[1]
10Column 4 0 200  r[2]=t1.t2_id
11Eq 3 162 (BINARY)   54  if r[2]==r[3] 
goto 16
12Column 3 0 400  r[4]=t2.a
13Eq 4 165 (BINARY)   80  if r[5]==r[4] 
goto 16
14Copy   4 5 000  r[5]=r[4]
15ResultRow  4 1 000  output=r[4]
16  Next   4 9 100
17Next   3 6 001
18Halt   0 0 000
19Transaction0 0 4 0  01  usesStmtJournal=0
20Integer-13 000  r[3]=-1
21Goto   0 1 000
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 p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 22000  Start at 22
1 Null   1 5 008  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 Explain0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1  
00
5 Rewind 3 211 0  00
6 Explain0 1 0 SEARCH TABLE t1 USING COVERING INDEX 
t1_1 (t2_id=?)  00
7 IdxRowid   3 1 000  r[1]=rowid
8 SeekGE 4 171 1  00  key=r[1]
9   IdxGT  4 171 1  00  key=r[1]
10  Column 4 0 200  r[2]=t1.t2_id
11  Eq 3 162 (BINARY)   54  if r[2]==r[3] 
goto 16
12  Column 3 0 400  r[4]=t2.a
13  Eq 4 165 (BINARY)   80  if r[5]==r[4] 
goto 16
14  Copy   4 5 000  r[5]=r[4]
15  ResultRow  4 1 000  output=r[4]
16Next   4 9 100
17Column 3 0 600  r[6]=
18SeekGT 3 216 1  00  key=r[6]
19  Goto   1 6 000
20Ne

Re: [sqlite] SELECT result different after ANALYZE

[David Raymond]

000  r[3]=-1
24Goto   0 1 000

sqlite>




-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ralf Junker
Sent: Monday, November 20, 2017 10:40 AM
To: sqlite-users@mailinglists.sqlite.org
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
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

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