[MDEV-486] LP:1010116 - Incorrect query results in subqueries - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Fixed
Affects Version/s: 5.5.25, 5.3.7
Fix Version/s: 5.5.27, 5.3.9
Component/s: None
Labels:
- upstream

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

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

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2012-08-24 20:23

Elena Stepanova made changes - 2012-08-24 20:26

Field	Original Value	New Value
Description	https://bugs.launchpad.net/maria/+bug/1010116	https://bugs.launchpad.net/maria/+bug/1010116 Reproducible on maria/5.3

Elena Stepanova made changes - 2012-08-24 21:03

Description

https://bugs.launchpad.net/maria/+bug/1010116

Reproducible on maria/5.3

https://bugs.launchpad.net/maria/+bug/1010116

Test case 2 (with default optimizer_switch):

{noformat}
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`;
{noformat}

Expected result:
{noformat}
id Val1
30631 NULL
{noformat}

Actual result:
{noformat}
id Val1
30631 1
{noformat}

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

{noformat}
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;
{noformat}

Expected result:
{noformat}
id id val1
30631 NULL NULL
{noformat}

Actual result:
{noformat}
id id val1
30631 2 1
{noformat}

Reproducible on maria/5.2, maria/5.3, maria/5.5, mysql/5.1, mysql/5.5, mysql/5.6

Elena Stepanova made changes - 2012-08-24 21:04

Description

https://bugs.launchpad.net/maria/+bug/1010116

Test case 1 (with default optimizer_switch):

{noformat}
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`;
{noformat}

Expected result:
{noformat}
id Val1
30631 NULL
{noformat}

Actual result:
{noformat}
id Val1
30631 1
{noformat}

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

{noformat}
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;
{noformat}

Expected result:
{noformat}
id id val1
30631 NULL NULL
{noformat}

Actual result:
{noformat}
id id val1
30631 2 1
{noformat}

Reproducible on maria/5.2, maria/5.3, maria/5.5, mysql/5.1, mysql/5.5, mysql/5.6

Oleksandr Byelkin made changes - 2012-08-27 21:21

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2012-08-28 10:21

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;

Oleksandr Byelkin added a comment - 2012-08-28 10:21 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;

Oleksandr Byelkin added a comment - 2012-08-28 11:36

substitution made during preparation (setup_fields).

Oleksandr Byelkin added a comment - 2012-08-28 11:36 substitution made during preparation (setup_fields).

Oleksandr Byelkin made changes - 2012-08-28 14:12

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2012-08-29 09:43

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2012-08-29 11:24

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2012-08-29 12:25

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2012-08-30 12:01

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;

Oleksandr Byelkin added a comment - 2012-08-30 12:01 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;

Oleksandr Byelkin added a comment - 2012-08-30 12:20

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

Oleksandr Byelkin added a comment - 2012-08-30 12:20 View variant also repeatable on 5.6 (probably earlier also).

Oleksandr Byelkin made changes - 2012-08-31 11:40

Status

In Progress [ 3 ]

Open [ 1 ]

Sergei Golubchik made changes - 2012-08-31 19:46

Priority

Major [ 3 ]

Minor [ 4 ]

Sergei Golubchik made changes - 2012-08-31 19:46

Labels

upstream

Sergei Golubchik added a comment - 2012-08-31 19:46

repeatable on mysql-5.5 too

Sergei Golubchik added a comment - 2012-08-31 19:46 repeatable on mysql-5.5 too

Oleksandr Byelkin made changes - 2012-08-31 19:57

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2012-09-03 17:13

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2012-09-04 13:09

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2012-09-04 16:14

Patch committed, waiting for review...

Oleksandr Byelkin added a comment - 2012-09-04 16:14 Patch committed, waiting for review...

Sergei Golubchik added a comment - 2012-09-06 10:17

pushed in 5.5

Sergei Golubchik added a comment - 2012-09-06 10:17 pushed in 5.5

Sergei Golubchik made changes - 2012-09-06 10:17

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Elena Stepanova made changes - 2012-12-04 01:27

Link

This issue relates to ~~MDEV-3914~~ [ ~~MDEV-3914~~ ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 13932 ]

MariaDB v2 [ 45260 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 45260 ]

MariaDB v3 [ 65924 ]

Sergei Golubchik made changes - 2021-12-06 21:37

Workflow

MariaDB v3 [ 65924 ]

MariaDB v4 [ 144950 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2012-08-24 20:23

Updated:: 2012-12-04 01:27

Resolved:: 2012-09-06 10:17

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration