Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
10.4.22, 10.5.13, 10.6.5, 10.7.1
Description
The following query returns wrong result:
CREATE TABLE t1( |
K1 INT PRIMARY KEY, |
Name VARCHAR(15) |
);
|
INSERT INTO t1 VALUES |
(1,'T1Row1'), (2,'T1Row2'); |
CREATE TABLE t2( |
K2 INT PRIMARY KEY, |
K1r INT, |
rowTimestamp DATETIME,
|
Event VARCHAR(15) |
);
|
|
INSERT INTO t2 VALUES |
(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), |
(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), |
(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); |
|
SELECT t1a.*, t2a.*, |
t2i.K2 AS K2B, t2i.K1r AS K1rB, |
t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB |
FROM
|
t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 |
LEFT JOIN |
( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) |
ON (t1i.K1 = 1) AND |
(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR |
(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) |
OR (t2i.K2 IS NULL)) |
WHERE
|
t2a.K1r = 1 AND t2i.K2 IS NULL; |
MariaDB [test]> SELECT t1a.*, t2a.*, t2i.K2 AS K2B, t2i.K1r AS K1rB, t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB FROM t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 LEFT JOIN ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) ON (t1i.K1 = 1) AND (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) OR (t2i.K2 IS NULL)) WHERE t2a.K1r = 1 AND t2i.K2 IS NULL; |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ |
| K1 | Name | K2 | K1r | rowTimestamp | Event | K2B | K1rB | rowTimestampB | EventB | |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ |
| 1 | T1Row1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL | NULL | |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ |
1 row in set (0.000 sec) |
it should be:
mysql> SELECT t1a.*, t2a.*, |
-> t2i.K2 AS K2B, t2i.K1r AS K1rB, |
-> t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB |
-> FROM |
-> t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 |
-> LEFT JOIN |
-> ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) |
-> ON (t1i.K1 = 1) AND |
-> (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR |
-> (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) |
-> OR (t2i.K2 IS NULL)) |
-> WHERE |
-> t2a.K1r = 1 AND t2i.K2 IS NULL; |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ |
| K1 | Name | K2 | K1r | rowTimestamp | Event | K2B | K1rB | rowTimestampB | EventB | |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ |
| 1 | T1Row1 | 1 | 1 | 2015-04-13 10:42:11 | T1Row1Event1 | NULL | NULL | NULL | NULL | |
| 1 | T1Row1 | 2 | 1 | 2015-04-13 10:42:12 | T1Row1Event2 | NULL | NULL | NULL | NULL | |
| 1 | T1Row1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL | NULL | |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ |
3 rows in set (0.01 sec) |
|
Attachments
Issue Links
- duplicates
-
MDEV-27624 Wrong result for nested left join using not_exists optimization
- Closed