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 <d...@sqlite.org> 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 <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<https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1e826b314>) >> >> 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
