[MDEV-4172] Wrong result (missing row, NULL instead of a field value) with semijoin=on, LEFT JOIN in the outer query and INNER JOIN in EXISTS subquery - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.1, 5.5.29
Fix Version/s: 10.0.2, 5.5.30, 5.3.13
Component/s: None
Labels:
None

Description

The following test case

set optimizer_switch='exists_to_in=on';

CREATE TABLE t1 (i1 INT, c1 VARCHAR(1)) ENGINE=MyISAM;

INSERT t1 VALUES (7,'v'),(3,'y');

CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t2 VALUES ('y'),('y');

CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t3 VALUES

  ('j'),('v'),('c'),('m'),('d'),

  ('d'),('y'),('t'),('d'),('s');

SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')

WHERE EXISTS ( SELECT 1 FROM t1, t3 WHERE c3 = c1 AND c1 = outer_t1.c1 );

Produces 2 rows when it's executed with exists_to_in=on:

i1	c1	c2

7	v	NULL

3	y	NULL

and 3 rows without exists_to_in:

i1	c1	c2

3	y	y

3	y	y

7	v	NULL

The latter result is correct.

EXPLAIN with exists_to_in=on, otherwise default optimizer_switch:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	outer_t1	ALL	NULL	NULL	NULL	NULL	2	100.00

1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00

1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)

2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00

2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1276	Field or reference 'test.outer_t1.c1' of SELECT #2 was resolved in SELECT #1

Note	1003	select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` semi join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`)

revision-id: sanja@askmonty.org-20130213131832-b1sk3puofj29jubr

revno: 3502

branch-nick: 10.0-base-exists2in

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2013-02-15 00:52

Elena Stepanova made changes - 2013-02-15 00:52

Field	Original Value	New Value
Link		This issue relates to TODO-38 [ TODO-38 ]

Oleksandr Byelkin made changes - 2013-02-15 11:49

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2013-02-15 11:55

transformed join (without semi-join) produces more or less expected results ...

select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`);
i1 c1 c2
3 y y
3 y y
3 y y
3 y y
7 v NULL
7 v NULL

Oleksandr Byelkin added a comment - 2013-02-15 11:55 transformed join (without semi-join) produces more or less expected results ... select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`); i1 c1 c2 3 y y 3 y y 3 y y 3 y y 7 v NULL 7 v NULL

Oleksandr Byelkin made changes - 2013-02-15 16:01

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2013-02-15 18:45

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2013-02-15 19:05

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2013-02-16 22:23

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2013-02-16 22:45

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2013-02-18 10:38

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2013-02-18 11:22

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2013-02-18 11:56

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2013-02-18 12:40

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2013-02-18 21:31

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2013-02-18 22:33

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin added a comment - 2013-02-19 20:34

new test suite is:

CREATE TABLE t1 (i1 INT, c1 VARCHAR(1));
INSERT t1 VALUES (7,'v'),(3,'y');

CREATE TABLE t2 (c2 VARCHAR(1));
INSERT INTO t2 VALUES ('y'),('y');

CREATE TABLE t3 (c3 VARCHAR(1));
INSERT INTO t3 VALUES
('j'),('v'),('c'),('m'),('d'),
('d'),('y'),('t'),('d'),('s');

SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1);

set optimizer_switch='semijoin=off';

SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1);

drop table t1,t2,t3;

Oleksandr Byelkin added a comment - 2013-02-19 20:34 new test suite is: CREATE TABLE t1 (i1 INT, c1 VARCHAR(1)); INSERT t1 VALUES (7,'v'),(3,'y'); CREATE TABLE t2 (c2 VARCHAR(1)); INSERT INTO t2 VALUES ('y'),('y'); CREATE TABLE t3 (c3 VARCHAR(1)); INSERT INTO t3 VALUES ('j'),('v'),('c'),('m'),('d'), ('d'),('y'),('t'),('d'),('s'); SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1); set optimizer_switch='semijoin=off'; SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1); drop table t1,t2,t3;

Oleksandr Byelkin made changes - 2013-02-19 20:35

Summary

Exists2In: Wrong result (missing row, NULL instead of a field value) with exists_to_in=on, LEFT JOIN in the outer query and INNER JOIN in EXISTS subquery

Wrong result (missing row, NULL instead of a field value) with semijoin=on, LEFT JOIN in the outer query and INNER JOIN in EXISTS subquery

Oleksandr Byelkin made changes - 2013-02-19 20:35

Fix Version/s		5.5.30 [ 11800 ]
Affects Version/s		5.5.29 [ 12102 ]
Affects Version/s		10.0.1 [ 11400 ]

Oleksandr Byelkin made changes - 2013-02-19 21:23

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Petrunia [ psergey ]

Igor Babaev (Inactive) added a comment - 2013-02-20 08:05 - edited

Sergey,

It looks like this bug is also a legacy bug in multiple equalities:

MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1);
--------------------------------------------------------------------------------------------------------------------------------

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

--------------------------------------------------------------------------------------------------------------------------------

1	PRIMARY	outer_t1	ALL	NULL	NULL	NULL	NULL	2	100.00
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer (flat, BNL join)

--------------------------------------------------------------------------------------------------------------------------------
5 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Level

Code

Message

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note

1003

select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` semi join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`)

I will investigate the problem.

Igor Babaev (Inactive) added a comment - 2013-02-20 08:05 - edited Sergey, It looks like this bug is also a legacy bug in multiple equalities: MariaDB [test] > EXPLAIN EXTENDED SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') WHERE outer_t1.c1 IN ( SELECT c1 FROM t1, t3 WHERE c3 = c1); --- ------------ ----------- ------ ------------- ------------ ------- ---- ---- -------- ------------------------------------------------ id select_type table type possible_keys key key_len ref rows filtered Extra --- ------------ ----------- ------ ------------- ------------ ------- ---- ---- -------- ------------------------------------------------ 1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (flat, BNL join) --- ------------ ----------- ------ ------------- ------------ ------- ---- ---- -------- ------------------------------------------------ 5 rows in set, 1 warning (0.00 sec) MariaDB [test] > show warnings; ------ ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Level Code Message ------ ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Note 1003 select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` semi join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`) ------ ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- I will investigate the problem.

Igor Babaev (Inactive) made changes - 2013-02-20 08:09

Assignee

Sergei Petrunia [ psergey ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) added a comment - 2013-02-25 19:50

The fix for the bug was pushed into the 5.3 tree on 21-02-2013

Igor Babaev (Inactive) added a comment - 2013-02-25 19:50 The fix for the bug was pushed into the 5.3 tree on 21-02-2013

Igor Babaev (Inactive) made changes - 2013-02-25 19:50

Fix Version/s		5.3.13 [ 12602 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

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

Workflow

defaullt [ 26219 ]

MariaDB v2 [ 46410 ]

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

Workflow

MariaDB v2 [ 46410 ]

MariaDB v3 [ 67111 ]

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

Workflow

MariaDB v3 [ 67111 ]

MariaDB v4 [ 146430 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2013-02-15 00:52

Updated:: 2013-03-08 08:25

Resolved:: 2013-02-25 19:50

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration