summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/icp_tests.inc10
-rw-r--r--mysql-test/r/innodb_icp.result10
-rw-r--r--mysql-test/r/join_cache.result51
-rw-r--r--mysql-test/r/myisam_icp.result10
-rw-r--r--mysql-test/r/range.result24
-rw-r--r--mysql-test/r/subselect_sj2.result21
-rw-r--r--mysql-test/r/subselect_sj2_jcl6.result21
-rw-r--r--mysql-test/r/subselect_sj2_mat.result21
-rw-r--r--mysql-test/suite/maria/icp.result10
-rw-r--r--mysql-test/t/join_cache.test34
-rw-r--r--mysql-test/t/range.test21
-rw-r--r--mysql-test/t/subselect_sj2.test19
12 files changed, 252 insertions, 0 deletions
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index 7c9feea55c2..6807d6eb1bd 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -639,6 +639,16 @@ SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+# MDEV-8189 field<>const and const<>field are not symmetric
+# Do the same EXPLAIN and SELECT
+# with "t1.d1 <> t2.pk" instead of "t2.pk <> t1.d1"
+
+EXPLAIN
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+ WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+ WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+
DROP TABLE t1, t2;
--echo #
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index fb467494525..cff78e76664 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -600,6 +600,16 @@ SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
WHERE t2.pk <> t1.d1 AND t2.pk = 4;
d1 pk i1
1 4 1
+EXPLAIN
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL k1 9 NULL 3 Using index
+1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+d1 pk i1
+1 4 1
DROP TABLE t1, t2;
#
# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
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;
#
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 49cba60320d..2792d75abb2 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -604,6 +604,16 @@ SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
WHERE t2.pk <> t1.d1 AND t2.pk = 4;
d1 pk i1
1 4 1
+EXPLAIN
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL k1 9 NULL 3 Using index
+1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+d1 pk i1
+1 4 1
DROP TABLE t1, t2;
#
# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 78a224b1439..1b984666462 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2241,3 +2241,27 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 0
drop table t1,t2;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8189 field<>const and const<>field are not symmetric
+#
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+INSERT INTO t1 (a) VALUES (10),(10),(10),(10),(10),(10),(10),(10),(10),(10),(70);
+EXPLAIN SELECT * FROM t1 WHERE a<>10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using index condition
+EXPLAIN SELECT * FROM t1 WHERE 10<>a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using index condition
+SELECT * FROM t1 WHERE a<>10;
+a b
+70 NULL
+SELECT * FROM t1 WHERE 10<>a;
+a b
+70 NULL
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 9a5da710a4c..f55fbf07db8 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1092,6 +1092,27 @@ AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
);
COUNT(*)
3724
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where
+1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index
+1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3)
+1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join)
+1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join)
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+COUNT(*)
+3724
set optimizer_prune_level=@tmp_951283;
DROP TABLE t1,t2;
#
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index c10b550d11a..2b6af74e47e 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -1107,6 +1107,27 @@ AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
);
COUNT(*)
3724
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where
+1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index
+1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3)
+1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join)
+1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (incremental, BNL join)
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+COUNT(*)
+3724
set optimizer_prune_level=@tmp_951283;
DROP TABLE t1,t2;
#
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 4e75aee24a2..cfc7aa082f1 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1094,6 +1094,27 @@ AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
);
COUNT(*)
3724
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where
+1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index
+1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3)
+1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join)
+1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join)
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+COUNT(*)
+3724
set optimizer_prune_level=@tmp_951283;
DROP TABLE t1,t2;
#
diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result
index 85a21ada47d..7a8c0dd44dd 100644
--- a/mysql-test/suite/maria/icp.result
+++ b/mysql-test/suite/maria/icp.result
@@ -606,6 +606,16 @@ SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
WHERE t2.pk <> t1.d1 AND t2.pk = 4;
d1 pk i1
1 4 1
+EXPLAIN
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL k1 9 NULL 3 Using index
+1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
+SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t1.d1 <> t2.pk AND t2.pk = 4;
+d1 pk i1
+1 4 1
DROP TABLE t1, t2;
#
# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 7d873c555f6..019d8edde2f 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -2505,6 +2505,18 @@ SELECT t2.v FROM t1, t2, t3
WHERE t3.v <> t2.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;
+# MDEV-8189 field<>const and const<>field are not symmetric
+# Do the same EXPLAIN and SELECT
+# for "t2.v <> t3.v" instead of "t3.v <> t2.v"
+
+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;
+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;
+
SET SESSION join_cache_level=6;
EXPLAIN
SELECT t2.v FROM t1, t2, t3
@@ -2514,6 +2526,17 @@ SELECT t2.v FROM t1, t2, t3
WHERE t3.v <> t2.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;
+# MDEV-8189 field<>const and const<>field are not symmetric
+# Do the same EXPLAIN and SELECT
+# for "t2.v <> t3.v" instead of "t3.v <> t2.v"
+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;
+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;
+
SET SESSION join_cache_level=4;
EXPLAIN
SELECT t2.v FROM t1, t2, t3
@@ -2523,6 +2546,17 @@ SELECT t2.v FROM t1, t2, t3
WHERE t3.v <> t2.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;
+# MDEV-8189 field<>const and const<>field are not symmetric
+# Do the same EXPLAIN and SELECT
+# for "t2.v <> t3.v" instead of "t3.v <> t2.v"
+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;
+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;
+
DROP TABLE t1,t2,t3;
SET SESSION join_cache_level=DEFAULT;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 6249d2b5e4f..d65e0bb2f73 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1777,3 +1777,24 @@ explain extended select * from t2 where (b > 25 and b < 15) or c < 44;
explain extended select * from t2 where (b > 25 and b < 15) or (a>55 and a<44);
drop table t1,t2;
+
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8189 field<>const and const<>field are not symmetric
+--echo #
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+INSERT INTO t1 (a) VALUES (10),(10),(10),(10),(10),(10),(10),(10),(10),(10),(70);
+EXPLAIN SELECT * FROM t1 WHERE a<>10;
+EXPLAIN SELECT * FROM t1 WHERE 10<>a;
+SELECT * FROM t1 WHERE a<>10;
+SELECT * FROM t1 WHERE 10<>a;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 0bf9c6d9d10..7ee52a0fa8a 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -1222,6 +1222,25 @@ WHERE alias3.d IN (
AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
);
+# MDEV-8189 field<>const and const<>field are not symmetric
+# Do the same EXPLAIN SELECT and SELECT
+# with "alias3.c != alias5.c" instead of "alias5.c != alias3.c"
+
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+ SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+ WHERE alias5.b = alias4.b
+ AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+ SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+ WHERE alias5.b = alias4.b
+ AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
+);
+
set optimizer_prune_level=@tmp_951283;
DROP TABLE t1,t2;
View Lorry Controller Status