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

Categories

(Toolkit :: Storage, defect)

x86
Windows XP
defect
Not set
normal

Tracking

()

RESOLVED FIXED
mozilla1.9.1b1

People

(Reporter: mozilla, Unassigned)

References

Details

(Keywords: verified1.9.0.11)

Attachments

(1 file)

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
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 [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?
Severity: critical → normal
Status: UNCONFIRMED → NEW
Depends on: 445042
Ever confirmed: true
No longer depends on: 445042
Depends on: 449443
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 ago12 years ago
Resolution: --- → FIXED
Fixed in 1.9.0.10 with bug 488710
Keywords: fixed1.9.0.10
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.
You need to log in before you can comment on or make changes to this bug.