Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0.30, 10.0.31, 10.0.32, 10.0.33, 10.0.34
-
None
Description
Assuming the following in a database bugtest:
bugtest_dump.sql |
DROP TABLE IF EXISTS `t1`; |
CREATE TABLE `t1` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`works` VARCHAR(1) DEFAULT NULL, |
PRIMARY KEY (`id`) |
);
|
INSERT INTO `t1` VALUES (1,'y'),(2,'n'); |
DROP TABLE IF EXISTS `t2`; |
CREATE TABLE `t2` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`t1_id` int(11) DEFAULT NULL, |
PRIMARY KEY (`id`) |
);
|
INSERT INTO `t2` VALUES (1,1),(2,1),(3,3),(4,3);}} |
And the following query:
SELECT * FROM t1 LEFT OUTER JOIN (SELECT t1_id FROM t2 WHERE id IN (SELECT MAX(id) FROM t2 GROUP BY t1_id)) tmpname on tmpname.t1_id = t1.id; |
pre 10.0.30 the result is:
+----+-------+-------+
|
| id | works | t1_id |
|
+----+-------+-------+
|
| 1 | y | 1 |
|
| 2 | n | NULL |
|
+----+-------+-------+
|
2 rows in set (0.00 sec)
|
Since 10.0.30 it is:
+----+-------+-------+
|
| id | works | t1_id |
|
+----+-------+-------+
|
| 1 | y | 1 |
|
+----+-------+-------+
|
1 row in set (0.00 sec)
|
Attachments
Issue Links
- duplicates
-
MDEV-14779 using left join causes incorrect results with materialization and derived tables
- Closed