[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

From: Bert Huijben <bert_at_vmoo.com>
Date: Mon, 10 Feb 2014 16:37:57 +0100

        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.
Received on 2014-02-10 16:38:43 CET

This is an archived mail posted to the Subversion Dev mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.