[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 Created: 2013-02-15  Updated: 2013-03-08  Resolved: 2013-02-25

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.1, 5.5.29
Fix Version/s: 10.0.2, 5.5.30, 5.3.13

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 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



 Comments   
Comment by Oleksandr Byelkin [ 2013-02-15 ]

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

Comment by Oleksandr Byelkin [ 2013-02-19 ]

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;

Comment by Igor Babaev [ 2013-02-20 ]

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.

Comment by Igor Babaev [ 2013-02-25 ]

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

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