SQLite

View Ticket
Login
2016-11-03
18:35
Make sure left-join markings are transferred to the virtual scalar subexpressions when decomposing a vector comparison in the ON clause of a LEFT JOIN. Fix for ticket [fef4bb4bd9185ec8f]. (check-in: aba1e22b user: drh tags: branch-3.15)
2016-10-27
01:08 Fixed ticket [fef4bb4b]: Incorrect result from a LEFT JOIN with a row-value constraint plus 5 other changes (artifact: 9eee2c5f user: drh)
01:02
Make sure left-join markings are transferred to the virtual scalar subexpressions when decomposing a vector comparison in the ON clause of a LEFT JOIN. Fix for ticket [fef4bb4bd9185ec8f]. (check-in: 619f5cc7 user: drh tags: trunk)
00:27 New ticket [fef4bb4b] Incorrect result from a LEFT JOIN with a row-value constraint. (artifact: b2735a64 user: drh)

Ticket Hash: 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.