/ View Ticket
Login
Ticket UUID: fef4bb4bd9185ec8f18d9912abb444da61d02ff2
Title: Incorrect result from a LEFT JOIN with a row-value constraint
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-10-27 01:08:32
Version Found In: 3.15.0
User Comments:
drh added on 2016-10-27 00:27:33:

The second SELECT statement below gives an incorrect answer, even though it is logically identical to the first SELECT statement:

CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 2);
CREATE TABLE t2(a, b);
INSERT INTO t2 VALUES(3, 4);
SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.b = t1.b;
SELECT * FROM t1 LEFT JOIN t2 ON (t2.a, t2.b) = (t1.a, t1.b);

Since row values are a new feature beginning with version 3.15.0, this bug could not have existed in any prior versions.

Reported by Ján Hric on the sqlite-users mailing list.