SQLite

View Ticket
Login
2013-03-06
01:55 Fixed ticket [fc7bd635]: Incorrect query result in a 3-way join due to affinity issues plus 4 other changes (artifact: 921b0574 user: drh)
01:55
Fix a bug (ticket [fc7bd6358f59]) that caused incorrect query results in three way queries that involved comparing INTEGER and TEXT columns for equality. (check-in: 7097241c user: drh tags: trunk)
2013-03-05
19:20 New ticket [fc7bd635] Incorrect query result in a 3-way join due to affinity issues. (artifact: 13ecf3d1 user: drh)

Ticket Hash: fc7bd6358f59b67c85757dc28e41132bff635c70
Title: Incorrect query result in a 3-way join due to affinity issues
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2013-03-06 01:55:45
Version Found In: 3.7.15.2
User Comments:
drh added on 2013-03-05 19:20:59:

The following SQL yields an incorrect result (zero rows) in all versions of SQLite between 3.6.14 and 3.7.15.2:

CREATE TABLE t(textid TEXT);
INSERT INTO t VALUES('12');
INSERT INTO t VALUES('34');
CREATE TABLE i(intid INTEGER PRIMARY KEY);
INSERT INTO i VALUES(12);
INSERT INTO i VALUES(34);

SELECT t1.textid AS a, i.intid AS b, t2.textid AS c
  FROM t t1, i, t t2
 WHERE t1.textid = i.intid
   AND t1.textid = t2.textid;

The correct result should be two rows, one with 12|12|12 and the other with 34|34|34. With this bug, no rows are returned. Bisecting shows that this bug was introduced with check-in [dd4d67a67454] on 2009-04-23.