Incorrect query results in subqueries

[Jared Lucas]

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

[Elena Stepanova (elenst)]

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').

[Elena Stepanova (elenst)]

Reproducible on MariaDB 5.3 and 5.5, not reproducible on MySQL trunk.

Minimal optimizer_switch: derived_merge=on

EXPLAIN EXTENDED
SELECT
`Derived1`.`id`,
`Derived2`.`Val1`
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`;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 1 100.00 Using where
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select 30631 AS `id`,1 AS `Val1` from (select 30631 AS `id`) `Derived1` left join ((select 30631 AS `id`) `Derived3`) on(0) where 1

However, if the same scenario is converted to views, the problem is reproducible on mysql-5.1 and maria-5.2 as well, regardless the optimizer_switch:

create table t1 ( id int );
insert into t1 values (30631);
create table t2 ( id int );
insert into t2 values (30631);
create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2;
select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;

# Expected result:
# id id val1
# 30631 NULL NULL

# Actual result:
# id id val1
# 30631 2 1

[Jared Lucas (jared-r-lucas)]

Wow, thank you for the quick response!

[Jared Lucas (jared-r-lucas)]

Please fix my bug....

Doesn't hurt to ask?
(Would be helpful to utilize the performance I would gain from this)

[Elena Stepanova (elenst)]

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)

[Jared Lucas (jared-r-lucas)]

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

[Elena Stepanova (elenst)]

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).

[Elena Stepanova (elenst)]

Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-486

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Waiting for review...

[Jared Lucas (jared-r-lucas)]

that's fantastic Sanja!

> Date: Tue, 4 Sep 2012 13:16:04 +0000
> From: <email address hidden>
> To: <email address hidden>
> 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