446118 - Sqlite query "SELECT DISTINCT X FROM Y WHERE X IS NOT NULL" returns invalid result set if X is indexed

Status: RESOLVED FIXED

(Toolkit :: Storage, defect)

Description

[Mark Blewett]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.1) Gecko/2008070208 Firefox/3.0.1
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.1) 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

Comment 1

[Mark Blewett]

Attachment: A test database which exhibites the bug

Comment 2

[Mark Blewett]

Feedback from sqlite.org; 

Same bug as #3201. Fixed by check-in [5341] 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?

Comment 3

[Shawn Wilsher :sdwilsh]

Fixed by the sqlite upgrade on mozilla-central.  I've nominated that for branch
as well, and I suspect we'll take it.

Comment 4

[Shawn Wilsher :sdwilsh]

nevermind - sqlite got backed out

Comment 5

[Shawn Wilsher :sdwilsh]

sqlite upgrade stuck this time.  Resolving as FIXED.

Comment 6

[Shawn Wilsher :sdwilsh]

Fixed in 1.9.0.10 with bug 488710

Comment 7

[Al Billings [:abillings - ex-MoCo]]

Verified for 1.9.0.11 with Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.0.11pre) Gecko/2009051304 GranParadiso/3.0.11pre.