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 <rhuij...@apache.org> 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 <snip>
> INSERT INTO "sqlite_stat1" VALUES <snip>
> ...
> 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
