summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorSergey Petrunya <psergey@askmonty.org>2010-10-14 01:48:03 +0400
committerSergey Petrunya <psergey@askmonty.org>2010-10-14 01:48:03 +0400
commit508e75c259a88638999822cccdc66c271769a167 (patch)
treeecbbd32491a220d9c370df77141c914ff07edb7c /mysql-test
parent49ae85afd564e34ea3fb8df308cbafe347d76303 (diff)
downloadmariadb-git-508e75c259a88638999822cccdc66c271769a167.tar.gz
Merge MariaDB 5.2 -> MariaDB 5.3
- post-merge fixes
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/join_nested.result4
-rw-r--r--mysql-test/r/join_outer.result93
-rw-r--r--mysql-test/r/join_outer_jcl6.result141
-rw-r--r--mysql-test/r/select.result4
-rw-r--r--mysql-test/r/select_jcl6.result19
-rw-r--r--mysql-test/r/select_pkeycache.result4
-rw-r--r--mysql-test/suite/innodb/r/innodb.result6
-rw-r--r--mysql-test/suite/innodb_plugin/r/innodb.result6
-rw-r--r--mysql-test/suite/innodb_plugin/r/innodb_mysql.result5
-rw-r--r--mysql-test/suite/pbxt/r/join_nested.result2
-rw-r--r--mysql-test/suite/pbxt/r/select.result1
-rw-r--r--mysql-test/suite/vcol/t/vcol_misc.test19
-rw-r--r--mysql-test/t/join_outer.test44
13 files changed, 192 insertions, 156 deletions
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index c87a726a89f..9d6fdd11c4b 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -851,7 +851,7 @@ ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
-1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00
+1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`)))) where 1
@@ -1479,7 +1479,7 @@ on (t1.a = t2.a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 10
1 SIMPLE t2 ref a a 5 test.t1.a 1
-1 SIMPLE t3 ref a a 5 test.t1.a 1
+1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where
drop table t1, t2, t3;
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 490ef12535d..6d867f96034 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1328,13 +1328,13 @@ RIGHT OUTER JOIN t1 tt1 ON 1
STRAIGHT_JOIN t1 tt9 ON 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tt1 ALL NULL NULL NULL NULL 2
-1 SIMPLE tt2 ALL NULL NULL NULL NULL 2
-1 SIMPLE tt3 ALL NULL NULL NULL NULL 2
-1 SIMPLE tt4 ALL NULL NULL NULL NULL 2
-1 SIMPLE tt5 ALL NULL NULL NULL NULL 2
-1 SIMPLE tt6 ALL NULL NULL NULL NULL 2
-1 SIMPLE tt7 ALL NULL NULL NULL NULL 2
-1 SIMPLE tt8 ALL NULL NULL NULL NULL 2
+1 SIMPLE tt2 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE tt3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE tt4 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE tt5 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE tt6 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE tt7 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE tt8 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer
SET optimizer_search_depth = DEFAULT;
DROP TABLE t1;
@@ -1355,7 +1355,7 @@ RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where
1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer
-1 SIMPLE TA1 ALL NULL NULL NULL NULL 2
+1 SIMPLE TA1 ALL NULL NULL NULL NULL 2 Using where
DROP TABLE t1, t2;
#
# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990
@@ -1372,11 +1372,11 @@ RIGHT JOIN t1 AS jt6 ON jt6.f1
ON 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index
+1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
+1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index
+1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
+1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
Warnings:
Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
@@ -1389,11 +1389,11 @@ RIGHT JOIN t1 AS jt6 ON jt6.f1
ON 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index
+1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index
-1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index
+1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
+1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
+1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
Warnings:
Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
DROP TABLE t1;
@@ -1438,7 +1438,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`pk` AS `pk`,(select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),0,`test`.`t4`.`pk`)) from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk`
+Note 1003 select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),0,`test`.`t4`.`pk`)) from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk`
select t2.pk,
(select t3.pk+if(isnull(t4.pk),0,t4.pk)
from t3 left join t4 on t4.pk=t3.pk
@@ -1449,63 +1449,4 @@ group by t2.pk;
pk t
2001 3001
drop table t1,t2,t3,t4;
-#
-# Bug#57024: Poor performance when conjunctive condition over the outer
-# table is used in the on condition of an outer join
-#
-create table t1 (a int);
-insert into t1 values (NULL), (NULL), (NULL), (NULL);
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 values (4), (2), (1), (3);
-create table t2 like t1;
-insert into t2 select if(t1.a is null, 10, t1.a) from t1;
-create table t3 (a int, b int, index idx(a));
-insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101);
-analyze table t1,t2,t3;
-Table Op Msg_type Msg_text
-test.t1 analyze status OK
-test.t2 analyze status OK
-test.t3 analyze status OK
-flush status;
-select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null;
-sum(t3.b)
-1006
-show status like "handler_read%";
-Variable_name Value
-Handler_read_first 0
-Handler_read_key 4
-Handler_read_next 5
-Handler_read_prev 0
-Handler_read_rnd 0
-Handler_read_rnd_next 1048581
-flush status;
-select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
-sum(t3.b)
-1006
-show status like "handler_read%";
-Variable_name Value
-Handler_read_first 0
-Handler_read_key 4
-Handler_read_next 5
-Handler_read_prev 0
-Handler_read_rnd 0
-Handler_read_rnd_next 1048581
-drop table t1,t2,t3;
End of 5.1 tests
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 854fc725845..2f8a6ae93e0 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1312,6 +1312,147 @@ WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
f1 f2 f3 f1 f2
1 NULL 3 NULL NULL
DROP TABLE t1, t2;
+#
+# Bug#52357: Assertion failed: join->best_read in greedy_search
+# optimizer_search_depth=0
+#
+CREATE TABLE t1( a INT );
+INSERT INTO t1 VALUES (1),(2);
+SET optimizer_search_depth = 0;
+# Should not core dump on query preparation
+EXPLAIN
+SELECT 1
+FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1
+LEFT OUTER JOIN t1 tt5 ON 1
+LEFT OUTER JOIN t1 tt6 ON 1
+LEFT OUTER JOIN t1 tt7 ON 1
+LEFT OUTER JOIN t1 tt8 ON 1
+RIGHT OUTER JOIN t1 tt2 ON 1
+RIGHT OUTER JOIN t1 tt1 ON 1
+STRAIGHT_JOIN t1 tt9 ON 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE tt1 ALL NULL NULL NULL NULL 2
+1 SIMPLE tt2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE tt3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE tt4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE tt5 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE tt6 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE tt7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE tt8 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer
+SET optimizer_search_depth = DEFAULT;
+DROP TABLE t1;
+#
+# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result
+#
+CREATE TABLE t1 (f1 INT NOT NULL);
+INSERT INTO t1 VALUES (9),(0);
+CREATE TABLE t2 (f1 INT NOT NULL);
+INSERT INTO t2 VALUES
+(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
+SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
+RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
+COUNT(*)
+476
+EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
+RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer
+1 SIMPLE TA1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
+DROP TABLE t1, t2;
+#
+# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990
+#
+CREATE TABLE t1(f1 INT, PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (1),(2);
+EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
+LEFT JOIN t1 AS jt2
+RIGHT JOIN t1 AS jt3
+JOIN t1 AS jt4 ON 1
+LEFT JOIN t1 AS jt5 ON 1
+ON 1
+RIGHT JOIN t1 AS jt6 ON jt6.f1
+ON 1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index
+1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index; Using join buffer
+1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+Warnings:
+Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
+EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
+RIGHT JOIN t1 AS jt2
+RIGHT JOIN t1 AS jt3
+JOIN t1 AS jt4 ON 1
+LEFT JOIN t1 AS jt5 ON 1
+ON 1
+RIGHT JOIN t1 AS jt6 ON jt6.f1
+ON 1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index
+1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index; Using join buffer
+1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+Warnings:
+Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
+DROP TABLE t1;
+#
+# Bug#49600: outer join of two single-row tables with joining attributes
+# evaluated to nulls
+create table t1 (a int, b int);
+create table t2 (a int, b int);
+insert into t1 values (1, NULL);
+insert into t2 values (2, NULL);
+select * from t1 left join t2 on t1.b=t2.b;
+a b a b
+1 NULL NULL NULL
+select * from t1 left join t2 on t1.b=t2.b where 1=1;
+a b a b
+1 NULL NULL NULL
+drop table t1,t2;
+#
+# Bug#53161: outer join in the derived table is erroneously converted
+# into an inner join for a query with a group by clause
+#
+create table t1 (pk int not null primary key, a int not null);
+create table t2 like t1;
+create table t3 like t1;
+create table t4 (pk int not null primary key);
+insert into t1 values (1000, 1), (1001, 1);
+insert into t2 values (2000, 2), (2001, 2);
+insert into t3 values (3000, 3), (3001, 2);
+insert into t4 values (4000), (4001);
+explain extended
+select t2.pk,
+(select t3.pk+if(isnull(t4.pk),0,t4.pk)
+from t3 left join t4 on t4.pk=t3.pk
+where t3.pk=t2.pk+1000 limit 1 ) as t
+from t1,t2
+where t2.pk=t1.pk+1000 and t1.pk>1000
+group by t2.pk;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using temporary; Using filesort
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index
+Warnings:
+Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),0,`test`.`t4`.`pk`)) from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk`
+select t2.pk,
+(select t3.pk+if(isnull(t4.pk),0,t4.pk)
+from t3 left join t4 on t4.pk=t3.pk
+where t3.pk=t2.pk+1000 limit 1 ) as t
+from t1,t2
+where t2.pk=t1.pk+1000 and t1.pk>1000
+group by t2.pk;
+pk t
+2001 3001
+drop table t1,t2,t3,t4;
End of 5.1 tests
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 252006cfcf3..7722fbe5dcc 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4396,7 +4396,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4406,7 +4406,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index ff09e79a511..fb09a62eb73 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -4400,7 +4400,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4410,7 +4410,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
@@ -4785,6 +4785,21 @@ a b c
SELECT * FROM t1 WHERE 102 < c;
a b c
DROP TABLE t1;
+#
+# Bug #54459: Assertion failed: param.sort_length,
+# file .\filesort.cc, line 149 (part II)
+#
+CREATE TABLE t1(a ENUM('') NOT NULL);
+INSERT INTO t1 VALUES (), (), ();
+EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci;
+1
+1
+1
+1
+DROP TABLE t1;
End of 5.1 tests
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 252006cfcf3..7722fbe5dcc 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -4396,7 +4396,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4406,7 +4406,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
index 345da7a0163..b8d02ecbc78 100644
--- a/mysql-test/suite/innodb/r/innodb.result
+++ b/mysql-test/suite/innodb/r/innodb.result
@@ -1219,7 +1219,7 @@ count(*)
623
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 5 NULL # Using where
+1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR
update t1 set c=a;
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1914,7 +1914,7 @@ qq
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v,v_2 # 13 const # Using where
+1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
@@ -2090,7 +2090,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v v 303 const # Using where
+1 SIMPLE t1 ref v v 303 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
diff --git a/mysql-test/suite/innodb_plugin/r/innodb.result b/mysql-test/suite/innodb_plugin/r/innodb.result
index c4b718bd975..4eaf6ff25a7 100644
--- a/mysql-test/suite/innodb_plugin/r/innodb.result
+++ b/mysql-test/suite/innodb_plugin/r/innodb.result
@@ -1222,7 +1222,7 @@ count(*)
623
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 5 NULL # Using where
+1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR
update t1 set c=a;
explain select * from t1 where c between 1 and 2500;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1916,7 +1916,7 @@ qq
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v,v_2 # 13 const # Using where
+1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
@@ -2092,7 +2092,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref v v 303 const # Using where
+1 SIMPLE t1 ref v v 303 const # Using index condition
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
diff --git a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
index 53e979c350f..a749187b1c2 100644
--- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
@@ -356,7 +356,7 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
-1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
+1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
@@ -1426,12 +1426,15 @@ DROP TABLE t1;
#
create table t1 (a bit(1) not null,b int) engine=myisam;
create table t2 (c int) engine=innodb;
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off';
explain
select b from t1 where a not in (select b from t1,t2 group by a) group by a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
+set optimizer_switch=@save_optimizer_switch;
DROP TABLE t1,t2;
End of 5.0 tests
CREATE TABLE `t2` (
diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result
index 2dda77db8c6..2be4247c97f 100644
--- a/mysql-test/suite/pbxt/r/join_nested.result
+++ b/mysql-test/suite/pbxt/r/join_nested.result
@@ -1487,7 +1487,7 @@ on (t1.a = t2.a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 10
1 SIMPLE t2 ref a a 5 test.t1.a 1
-1 SIMPLE t3 ref a a 5 test.t1.a 1
+1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where
drop table t1, t2, t3;
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result
index 4e50661f9ba..c7601c00ee0 100644
--- a/mysql-test/suite/pbxt/r/select.result
+++ b/mysql-test/suite/pbxt/r/select.result
@@ -1398,7 +1398,6 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test
index 1bd7c0c50a1..bd868f46111 100644
--- a/mysql-test/suite/vcol/t/vcol_misc.test
+++ b/mysql-test/suite/vcol/t/vcol_misc.test
@@ -160,22 +160,3 @@ select * from t1,t2 where t1.b=t2.c and d <= 100;
set join_cache_level=default;
drop table t1, t2;
-create table t1 (a int, b int);
-insert into t1 values (3, 30), (4, 20), (1, 20);
-create table t2 (c int, d int, v int as (d+1), index idx(c));
-insert into t2(c,d) values
-(20, 100), (20, 300), (30, 100), (30, 200), (40, 500),
-(70, 100), (40, 300), (60, 100), (40, 100), (70, 100);
-set join_cache_level=6;
-explain
-select * from t1,t2 where t1.b=t2.c and d <= 100;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3
-1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where; Using join buffer
-select * from t1,t2 where t1.b=t2.c and d <= 100;
-a b c d v
-4 20 20 100 101
-1 20 20 100 101
-3 30 30 100 101
-set join_cache_level=default;
-drop table t1, t2;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index aed1450e607..b14d9a40300 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1030,48 +1030,4 @@ select t2.pk,
drop table t1,t2,t3,t4;
---echo #
---echo # Bug#57024: Poor performance when conjunctive condition over the outer
---echo # table is used in the on condition of an outer join
---echo #
-
-create table t1 (a int);
-insert into t1 values (NULL), (NULL), (NULL), (NULL);
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 select * from t1;
-insert into t1 values (4), (2), (1), (3);
-
-create table t2 like t1;
-insert into t2 select if(t1.a is null, 10, t1.a) from t1;
-
-create table t3 (a int, b int, index idx(a));
-insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101);
-
-analyze table t1,t2,t3;
-
-flush status;
-select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null;
-show status like "handler_read%";
-flush status;
-select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
-show status like "handler_read%";
-
-drop table t1,t2,t3;
-
--echo End of 5.1 tests