[sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

[Bert Huijben]

Hi,

As part of the Subversion 1.8.6 release we tried introducing some data in
the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+
compatibility to tell sqlite about our 'bad indexes':
[[
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES 
INSERT INTO "sqlite_stat1" VALUES 
...
ANALYZE sqlite_master;
]]
(this was discussed on this list a few months ago and worked fine in all our
internal testing)

During release testing we found that some distributions decided to enable
SQLITE_ENABLE_STAT3, and with this flag at least one of our queries doesn't
work the way we expect it with Sqlite 3.8.3 after that second ANALYZE call.
(The distribution: OS/X 'Homebrew'
https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
e826b314 )

Trimmed testcase on
http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql

Original/full testcase on
http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql


The simplified query
[[
SELECT local_relpath, moved_to, op_depth, 1
 FROM nodes n
WHERE wc_id = 1
  AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND (local_relpath
< 'A/B0')))
  AND moved_to IS NOT NULL
  AND op_depth >= 0;
]]

Returns 1 row in the sqlite versions as we normally compile it, but when
3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.

In my opinion the missing heuristics in the statistics table shouldn't make
the query return invalid data. (It could make it faster, slower, ...).


I'm guessing that this is a bug that needs some fix.

But for our usage of Sqlite in Subversion we can probably better block usage
of an sqlite that has STAT2, STAT3 (or higher) enabled.

What is the recommended approach for detecting this scenario?

Bert

--
The schema of the database and the testcase are part of Subversion and
Apache 2 licensed, so feel free to use any part for future testing.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

[Richard Hipp]

On Mon, Feb 10, 2014 at 9:02 PM, Richard Hipp  wrote:

>
> Your work-arounds pending the patch release:
>
> (1) Do not compile with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4
> (2) Change your query so that it says "+moved_to IS NOT NULL" - add a
> unary "+" operator  before every column name that is subject to an IS NOT
> NULL operator.
>

A third work-around:

(3) After opening each database connection ("db") run the following code:

 if( strcmp(sqlite3_sourceid(),"2013-08-26 23:00")>0
 && strcmp(sqlite3_sourceid(),"2014-02-11")<0 ){
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, 0x800);
}

The call to sqlite3_test_control() will disable the STAT3 and STAT4 logic,
which will suppress the bug.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

[Richard Hipp]

Thanks for the bug report.

This was a real problem.  It is now fixed on the SQLite trunk (
http://www.sqlite.org/src/info/c950d6c411).  It will be a few days before
we can get a patch release (3.8.3.1) together.

Your work-arounds pending the patch release:

(1) Do not compile with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4
(2) Change your query so that it says "+moved_to IS NOT NULL" - add a unary
"+" operator  before every column name that is subject to an IS NOT NULL
operator.

Work-around (2) really only needs to be done for queries that have a WHERE
clause of the form:

   ( ... OR ... OR ... ) AND column IS NOT NULL

and where each sub-term of the (...OR...OR...) part is indexable.  But
adding unary "+" before every column operand of an IS NOT NULL operator is
harmless.

This problem exists in SQLite version 3.8.1, 3.8.2 and 3.8.3 only.



On Mon, Feb 10, 2014 at 2:05 PM, Bert Huijben  wrote:

> [Retrying with the user I subscribed with to avoid the moderation]
>
> Hi,
>
> As part of the Subversion 1.8.6 release we tried introducing some data in
> the 'sqlitstat_stat1' table using the recommended approach for Sqlite
> 3.8.0+
> compatibility to tell sqlite about our 'bad indexes':
> [[
> ANALYZE sqlite_master;
> INSERT INTO "sqlite_stat1" VALUES 
> INSERT INTO "sqlite_stat1" VALUES 
> ...
> ANALYZE sqlite_master;
> ]]
> (this was discussed on this list a few months ago and worked fine in all
> our
> internal testing)
>
> During release testing we found that some distributions decided to enable
> SQLITE_ENABLE_STAT3, and with this flag at least one of our queries doesn't
> work the way we expect it with Sqlite 3.8.3 after that second ANALYZE call.
> (The distribution: OS/X 'Homebrew'
>
> https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
> e826b314 )
>
> Trimmed testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql
>
> Original/full testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql
>
>
> The simplified query
> [[
> SELECT local_relpath, moved_to, op_depth, 1
>  FROM nodes n
> WHERE wc_id = 1
>   AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND
> (local_relpath
> < 'A/B0')))
>   AND moved_to IS NOT NULL
>   AND op_depth >= 0;
> ]]
>
> Returns 1 row in the sqlite versions as we normally compile it, but when
> 3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.
>
> In my opinion the missing heuristics in the statistics table shouldn't make
> the query return invalid data. (It could make it faster, slower, ...).
>
>
> I'm guessing that this is a bug that needs some fix.
>
> But for our usage of Sqlite in Subversion we can probably better block
> usage
> of an sqlite that has STAT2, STAT3 (or higher) enabled.
>
> What is the recommended approach for detecting this scenario?
>
> Bert
>
> --
> The schema of the database and the testcase are part of Subversion and
> Apache 2 licensed, so feel free to use any part for future testing.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

[Richard Hipp]

Ticket here:  http://www.sqlite.org/src/info/4c86b126f2

The work-around until a fix is available is to avoid compiling with
SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4.


On Mon, Feb 10, 2014 at 4:38 PM, Richard Hipp  wrote:

> STAT3 should never change the answer.  It should only help the answer to
> appear faster.  The fact that the queries gives different answers with and
> without STAT3 clearly indicates a bug.  We are working the problem now.
> Thanks for providing an simplified test case.
>
>
> On Mon, Feb 10, 2014 at 2:05 PM, Bert Huijben  wrote:
>
>> [Retrying with the user I subscribed with to avoid the moderation]
>>
>> Hi,
>>
>> As part of the Subversion 1.8.6 release we tried introducing some data in
>> the 'sqlitstat_stat1' table using the recommended approach for Sqlite
>> 3.8.0+
>> compatibility to tell sqlite about our 'bad indexes':
>> [[
>> ANALYZE sqlite_master;
>> INSERT INTO "sqlite_stat1" VALUES 
>> INSERT INTO "sqlite_stat1" VALUES 
>> ...
>> ANALYZE sqlite_master;
>> ]]
>> (this was discussed on this list a few months ago and worked fine in all
>> our
>> internal testing)
>>
>> During release testing we found that some distributions decided to enable
>> SQLITE_ENABLE_STAT3, and with this flag at least one of our queries
>> doesn't
>> work the way we expect it with Sqlite 3.8.3 after that second ANALYZE
>> call.
>> (The distribution: OS/X 'Homebrew'
>>
>> https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
>> e826b314)
>>
>> Trimmed testcase on
>> http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql
>>
>> Original/full testcase on
>> http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql
>>
>>
>> The simplified query
>> [[
>> SELECT local_relpath, moved_to, op_depth, 1
>>  FROM nodes n
>> WHERE wc_id = 1
>>   AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND
>> (local_relpath
>> < 'A/B0')))
>>   AND moved_to IS NOT NULL
>>   AND op_depth >= 0;
>> ]]
>>
>> Returns 1 row in the sqlite versions as we normally compile it, but when
>> 3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.
>>
>> In my opinion the missing heuristics in the statistics table shouldn't
>> make
>> the query return invalid data. (It could make it faster, slower, ...).
>>
>>
>> I'm guessing that this is a bug that needs some fix.
>>
>> But for our usage of Sqlite in Subversion we can probably better block
>> usage
>> of an sqlite that has STAT2, STAT3 (or higher) enabled.
>>
>> What is the recommended approach for detecting this scenario?
>>
>> Bert
>>
>> --
>> The schema of the database and the testcase are part of Subversion and
>> Apache 2 licensed, so feel free to use any part for future testing.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

[Richard Hipp]

STAT3 should never change the answer.  It should only help the answer to
appear faster.  The fact that the queries gives different answers with and
without STAT3 clearly indicates a bug.  We are working the problem now.
Thanks for providing an simplified test case.


On Mon, Feb 10, 2014 at 2:05 PM, Bert Huijben  wrote:

> [Retrying with the user I subscribed with to avoid the moderation]
>
> Hi,
>
> As part of the Subversion 1.8.6 release we tried introducing some data in
> the 'sqlitstat_stat1' table using the recommended approach for Sqlite
> 3.8.0+
> compatibility to tell sqlite about our 'bad indexes':
> [[
> ANALYZE sqlite_master;
> INSERT INTO "sqlite_stat1" VALUES 
> INSERT INTO "sqlite_stat1" VALUES 
> ...
> ANALYZE sqlite_master;
> ]]
> (this was discussed on this list a few months ago and worked fine in all
> our
> internal testing)
>
> During release testing we found that some distributions decided to enable
> SQLITE_ENABLE_STAT3, and with this flag at least one of our queries doesn't
> work the way we expect it with Sqlite 3.8.3 after that second ANALYZE call.
> (The distribution: OS/X 'Homebrew'
>
> https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
> e826b314 )
>
> Trimmed testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql
>
> Original/full testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql
>
>
> The simplified query
> [[
> SELECT local_relpath, moved_to, op_depth, 1
>  FROM nodes n
> WHERE wc_id = 1
>   AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND
> (local_relpath
> < 'A/B0')))
>   AND moved_to IS NOT NULL
>   AND op_depth >= 0;
> ]]
>
> Returns 1 row in the sqlite versions as we normally compile it, but when
> 3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.
>
> In my opinion the missing heuristics in the statistics table shouldn't make
> the query return invalid data. (It could make it faster, slower, ...).
>
>
> I'm guessing that this is a bug that needs some fix.
>
> But for our usage of Sqlite in Subversion we can probably better block
> usage
> of an sqlite that has STAT2, STAT3 (or higher) enabled.
>
> What is the recommended approach for detecting this scenario?
>
> Bert
>
> --
> The schema of the database and the testcase are part of Subversion and
> Apache 2 licensed, so feel free to use any part for future testing.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

[Petite Abeille]

On Feb 10, 2014, at 8:05 PM, Bert Huijben  wrote:

> As part of the Subversion 1.8.6 release we tried introducing some data in
> the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+
> compatibility to tell sqlite about our 'bad indexes’:

( Not directly related to your question, but… why, oh why is svn log 
--use-merge-history so excruciatingly slow? )

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

[Bert Huijben]

[Retrying with the user I subscribed with to avoid the moderation]

Hi,

As part of the Subversion 1.8.6 release we tried introducing some data in
the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+
compatibility to tell sqlite about our 'bad indexes':
[[
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES 
INSERT INTO "sqlite_stat1" VALUES 
...
ANALYZE sqlite_master;
]]
(this was discussed on this list a few months ago and worked fine in all our
internal testing)

During release testing we found that some distributions decided to enable
SQLITE_ENABLE_STAT3, and with this flag at least one of our queries doesn't
work the way we expect it with Sqlite 3.8.3 after that second ANALYZE call.
(The distribution: OS/X 'Homebrew'
https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
e826b314 )

Trimmed testcase on
http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql

Original/full testcase on
http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql


The simplified query
[[
SELECT local_relpath, moved_to, op_depth, 1
 FROM nodes n
WHERE wc_id = 1
  AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND (local_relpath
< 'A/B0')))
  AND moved_to IS NOT NULL
  AND op_depth >= 0;
]]

Returns 1 row in the sqlite versions as we normally compile it, but when
3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.

In my opinion the missing heuristics in the statistics table shouldn't make
the query return invalid data. (It could make it faster, slower, ...).


I'm guessing that this is a bug that needs some fix.

But for our usage of Sqlite in Subversion we can probably better block usage
of an sqlite that has STAT2, STAT3 (or higher) enabled.

What is the recommended approach for detecting this scenario?

Bert

--
The schema of the database and the testcase are part of Subversion and
Apache 2 licensed, so feel free to use any part for future testing.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users