Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Incorrect query results in subqueries
If this is the only type that produces a wrong result set in your workflow, maybe you could consider disabling the optimization only for this particular query, on the session level, and restoring it when the query is finished (of course, if it's possible in your application).
Re: Incorrect query results in subqueries
Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-486
Re: Incorrect query results in subqueries
Waiting for review...
RE: [Bug 1010116] Re: Incorrect query results in subqueries
that's fantastic Sanja!
> Date: Tue, 4 Sep 2012 13:16:04 +0000
> From: sanja@askmonty.org
> To: jared.r.lucas@hotmail.com
> Subject: [Bug 1010116] Re: Incorrect query results in subqueries
>
> Waiting for review...
>
> ** Changed in: maria
> Status: New => Fix Committed
>
> –
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1010116
>
> Title:
> Incorrect query results in subqueries
>
> Status in Maria:
> Fix Committed
>
> 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
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/maria/+bug/1010116/+subscriptions
Launchpad bug id: 1010116
Re: Incorrect query results in subqueries
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').