[MDEV-15523] Different result with LEFT OUTER JOIN and GROUP BY starting with 10.0.30 - Jira

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)

duplicates

MDEV-14779 using left join causes incorrect results with materialization and derived tables

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server