Incorrect query results in subqueries
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Oleksandr "Sanja" Byelkin |
Bug Description
I recently attempted to upgrade to MariaDB 5.5.24 from MySQL 5.1.61, especially to take advantage of Sub-query optimizations. We are developing with the MSFT Entity framework which tends to create a lot of subqueries in its generated sql. Most simple queries have run fine, but I found a scenario that does not work correctly in Maria 5.5.23-24, but works correctly on MySQL 5.1.61:
SELECT
`Derived1`.`id`,
`Derived2`.`Val1` = 1
FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
2 as `id`,
1 AS `Val1`
FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`
WHERE `Derived1`.`id` = 30631 LIMIT 2;
I removed any actual table references, so you can run this query on any system, and the result is still wrong. The "1 AS `Val1`" is the same as the original query, all other values could be from actual tables.
MariaDB Returns: 30631,1
MySQL Returns: 30631, NULL
A similar query works correctly on both MariaDb and MySql:
SELECT Derived1.Clientid, Val1
FROM
(SELECT 1234 AS Clientid) Derived1
right outer JOIN (SELECT 123 AS Clientid, 999 AS Val1) Derived2
ON Derived1.Clientid = Derived2.ClientID
WHERE
Derived2.Clientid = 123;
Returns: Null, 999
Changed in maria: | |
status: | Fix Committed → Fix Released |
Hi Jared,
Thank you. As a workaround for now, you can turn off derived_merge in optimizer_switch, either in the session, or globally, or in your cnf file (optimizer_ switch= 'derived_ merge=off' ).