[MDEV-15523] Different result with LEFT OUTER JOIN and GROUP BY starting with 10.0.30 Created: 2018-03-09  Updated: 2018-03-12  Resolved: 2018-03-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.30, 10.0.31, 10.0.32, 10.0.33, 10.0.34
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sander Hoentjen Assignee: Alice Sherepa
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-14779 using left join causes incorrect resu... Closed

 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)



 Comments   
Comment by Alice Sherepa [ 2018-03-12 ]

probably the same as MDEV-14779. Please monitor the progress there (or use set optimizer_switch='materialization=off' as a temporary workaround)

Generated at Thu Feb 08 08:21:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.