Details
Minor
Description
https://bugs.launchpad.net/maria/+bug/1010116
Test case 1 (with default optimizer_switch):
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`;
Expected result:
id Val1 30631 NULL
Actual result:
id Val1 30631 1
Reproducible on maria/5.3 revno 3562, maria/5.5 revno 3508
Not reproducible on mysql/5.1 revno 3770, mysql/5.5 revno 3900, mysql/5.6 revno 3917, maria/5.2 revno 3168
Test case 2
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
Reproducible on maria/5.2, maria/5.3, maria/5.5, mysql/5.1, mysql/5.5, mysql/5.6
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-3914 Wrong result (NULLs instead of real values) with INNER and RIGHT JOIN in a FROM subquery, derived_merge=on
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
substitution made during preparation (setup_fields).
Problem can be even expression over table fields (not only constants):
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, id is null as bbb, 1 as val1 from t2;
select t1., v2. from t1 left join v2 on t1.id = v2.id;
drop view v2;
drop table t1,t2;
View variant also repeatable on 5.6 (probably earlier also).
repeatable on mysql-5.5 too
Patch committed, waiting for review...
pushed in 5.5
explain extended shows why result is incorrect (see constants in the select list in transformed query):
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
select 30631 AS `id`,1 AS `Val1` from (select 30631 AS `id`) `Derived1` left join ((select 30631 AS `id`) `Derived3`) on(0) where 1;