[MDEV-486] LP:1010116 - Incorrect query results in subqueries Created: 2012-08-24  Updated: 2012-12-04  Resolved: 2012-09-06

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.25, 5.3.7
Fix Version/s: 5.5.27, 5.3.9

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Relates
relates to MDEV-3914 Wrong result (NULLs instead of real v... Closed

 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

 Comments   
Comment by Oleksandr Byelkin [ 2012-08-28 ]

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;

Comment by Oleksandr Byelkin [ 2012-08-28 ]

substitution made during preparation (setup_fields).

Comment by Oleksandr Byelkin [ 2012-08-30 ]

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;

Comment by Oleksandr Byelkin [ 2012-08-30 ]

View variant also repeatable on 5.6 (probably earlier also).

Comment by Sergei Golubchik [ 2012-08-31 ]

repeatable on mysql-5.5 too

Comment by Oleksandr Byelkin [ 2012-09-04 ]

Patch committed, waiting for review...

Comment by Sergei Golubchik [ 2012-09-06 ]

pushed in 5.5

Generated at Thu Feb 08 06:29:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.