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

Re: Incorrect query results in subqueries
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;

1. Expected result:
2. id id val1
3. 30631 NULL NULL

1. Actual result:
2. id id val1
3. 30631 2 1

Re: Incorrect query results in subqueries
Wow, thank you for the quick response!

Re: Incorrect query results in subqueries
Please fix my bug....

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

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)

RE: [Bug 1010116] Re: Incorrect query results in subqueries

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: 1010116@bugs.launchpad.net
> To: jared.r.lucas@hotmail.com
> 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

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