MDEV-8189 field<>const and const<>field are not symmetric

1 files changed, 51 insertions, 0 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index c1dbef08c04..68ea89949b2 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -4321,6 +4321,23 @@ h
 n
 v
 p
+EXPLAIN
+SELECT t2.v FROM t1, t2, t3
+WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
+GROUP BY t2.v ORDER BY t1.pk,t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
+1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where
+1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	5	Using where
+SELECT t2.v FROM t1, t2, t3
+WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
+GROUP BY t2.v ORDER BY t1.pk,t2.v;
+v
+b
+h
+n
+v
+p
 SET SESSION join_cache_level=6;
 EXPLAIN
 SELECT t2.v FROM t1, t2, t3 
@@ -4339,6 +4356,23 @@ h
 n
 v
 p
+EXPLAIN
+SELECT t2.v FROM t1, t2, t3
+WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
+GROUP BY t2.v ORDER BY t1.pk,t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
+1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	5	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
+SELECT t2.v FROM t1, t2, t3
+WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
+GROUP BY t2.v ORDER BY t1.pk,t2.v;
+v
+b
+h
+n
+v
+p
 SET SESSION join_cache_level=4;
 EXPLAIN
 SELECT t2.v FROM t1, t2, t3 
@@ -4357,6 +4391,23 @@ h
 n
 v
 p
+EXPLAIN
+SELECT t2.v FROM t1, t2, t3
+WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
+GROUP BY t2.v ORDER BY t1.pk,t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
+1	SIMPLE	t3	hash_ALL	PRIMARY,idx2	#hash#PRIMARY	4	test.t2.i	20	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash_ALL	idx2	#hash#idx2	3	test.t3.v	44	Using where; Using join buffer (incremental, BNLH join)
+SELECT t2.v FROM t1, t2, t3
+WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
+GROUP BY t2.v ORDER BY t1.pk,t2.v;
+v
+b
+h
+n
+v
+p
 DROP TABLE t1,t2,t3;
 SET SESSION join_cache_level=DEFAULT;
 #