Closed Bug 446118 Opened 12 years ago Closed 12 years ago
Sqlite query "SELECT DISTINCT X FROM Y WHERE X IS NOT NULL" returns invalid result set if X is indexed
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:220.127.116.11) Gecko/2008070208 Firefox/3.0.1 Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:18.104.22.168) Gecko/2008070208 Firefox/3.0.1 Using Firefox 3.0.1 and SQLiteManager 0.3.0, open the property-bee.sqlite database (available from from http://www.sqlite.org/cvstrac/tktview?tn=3236), and run the query SELECT DISTINCT Reference FROM Imported_Data WHERE Reference IS NOT NULL Two rows are returned; rm4521767<null char> rm9990684 There should be 1422 rows returned, and this can be confirmed by run the query (non of the Reference values are null) SELECT DISTINCT Reference FROM Imported_Data If the Index Idx_Reference is dropped then both queries return the correct number of rows (note re-indexing Idx_Reference doesn't resolve the problem) Adding the index back in; CREATE INDEX `Idx_Reference` ON `Imported_Data` (`Reference` ASC) and the 1st query returns 2 rows again. Reproducible: Always Steps to Reproduce: 1. 2. 3. Also reported here http://www.sqlite.org/cvstrac/tktview?tn=3236
Component: General → Storage
Product: Firefox → Toolkit
QA Contact: general → storage
Version: unspecified → 1.9.0 Branch
Feedback from sqlite.org; Same bug as #3201. Fixed by check-in  and available in the latest version [3.6.0] See http://www.sqlite.org/cvstrac/rlog?f=sqlite%2Fsrc%2Fexpr.c&sms=1 for detail. Will FF 3.0.2 move to sqlite 3.6.0?
Severity: critical → normal
Status: UNCONFIRMED → NEW
Depends on: 445042
Ever confirmed: true
Fixed by the sqlite upgrade on mozilla-central. I've nominated that for branch as well, and I suspect we'll take it.
Status: NEW → RESOLVED
Closed: 12 years ago
Resolution: --- → FIXED
Target Milestone: --- → mozilla1.9.1b1
Version: 1.9.0 Branch → Trunk
nevermind - sqlite got backed out
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
sqlite upgrade stuck this time. Resolving as FIXED.
Status: REOPENED → RESOLVED
Closed: 12 years ago → 12 years ago
Resolution: --- → FIXED
Verified for 22.214.171.124 with Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:126.96.36.199pre) Gecko/2009051304 GranParadiso/3.0.11pre.
You need to log in before you can comment on or make changes to this bug.