BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view

- Fix equality propagation to work with SJM nests and OR clauses (full descirption of problem and
  solution in the comment in the patch)
(The second commit with post-review fixes)

1 files changed, 103 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 911ae4372ca..3e24e06e162 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -940,5 +940,108 @@ WHERE a = d AND ( pk < 2 OR d = 'z' )
 );
 a	b	c
 DROP TABLE t1, t2;
+#
+# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view
+#
+CREATE TABLE t1 (
+a VARCHAR(1),
+b VARCHAR(1) NOT NULL,
+KEY(a)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('j','j'),('v','v'),('c','c'),('m','m'),('d','d'),
+('y','y'),('t','t'),('d','d'),('s','s'),('r','r'),
+('m','m'),('b','b'),('x','x'),('g','g'),('p','p'),
+('q','q'),('w','w'),('d','d'),('e','e');
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+# This query returned 6 rows instead of 19
+SELECT * FROM v1
+WHERE ( a, a ) IN (
+SELECT alias2.b, alias2.a
+FROM t1 AS alias1, t1 AS alias2
+WHERE alias2.b = alias1.a
+AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
+);
+a	b
+j	j
+v	v
+c	c
+m	m
+m	m
+d	d
+d	d
+d	d
+y	y
+t	t
+s	s
+r	r
+b	b
+x	x
+g	g
+p	p
+q	q
+w	w
+e	e
+# Another testcase, without the VIEW:
+CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 SELECT * FROM t1;
+EXPLAIN 
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
+1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
+2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+a	b
+j	j
+j	j
+v	v
+v	v
+c	c
+c	c
+m	m
+m	m
+m	m
+m	m
+d	d
+d	d
+d	d
+d	d
+d	d
+d	d
+y	y
+y	y
+t	t
+t	t
+s	s
+s	s
+r	r
+r	r
+b	b
+b	b
+x	x
+x	x
+g	g
+g	g
+p	p
+p	p
+q	q
+q	q
+w	w
+w	w
+e	e
+e	e
+DROP VIEW v1;
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;