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