Comment 6 for bug 1010116

Yes, the workaround solves the problem, but disabling that optimization also reduces the performance benefit of using MariaDb to almost nothing over MySql in my scenario. Unfortunately, the Entity framework uses many subqueries, and that type in particular to represent object inheritance. thanks for answering back though. It's nice just to get a response!

> Date: Fri, 24 Aug 2012 16:36:05 +0000
> From: <email address hidden>
> To: <email address hidden>
> Subject: [Bug 1010116] Re: Incorrect query results in subqueries
>
> Hi Jared,
>
> The bug is queued, but unfortunately I cannot tell you for sure at the
> moment whether the fix will make it to the next 5.5 release.
>
> Did you try the workaround as described above? (turn off derived_merge
> in optimizer_switch)
>
> --
> 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:
> New
>
> 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