diff options
author | Sergey Petrunya <psergey@askmonty.org> | 2010-10-14 01:48:03 +0400 |
---|---|---|
committer | Sergey Petrunya <psergey@askmonty.org> | 2010-10-14 01:48:03 +0400 |
commit | 508e75c259a88638999822cccdc66c271769a167 (patch) | |
tree | ecbbd32491a220d9c370df77141c914ff07edb7c /mysql-test | |
parent | 49ae85afd564e34ea3fb8df308cbafe347d76303 (diff) | |
download | mariadb-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.result | 4 | ||||
-rw-r--r-- | mysql-test/r/join_outer.result | 93 | ||||
-rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 141 | ||||
-rw-r--r-- | mysql-test/r/select.result | 4 | ||||
-rw-r--r-- | mysql-test/r/select_jcl6.result | 19 | ||||
-rw-r--r-- | mysql-test/r/select_pkeycache.result | 4 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb.result | 6 | ||||
-rw-r--r-- | mysql-test/suite/innodb_plugin/r/innodb.result | 6 | ||||
-rw-r--r-- | mysql-test/suite/innodb_plugin/r/innodb_mysql.result | 5 | ||||
-rw-r--r-- | mysql-test/suite/pbxt/r/join_nested.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/pbxt/r/select.result | 1 | ||||
-rw-r--r-- | mysql-test/suite/vcol/t/vcol_misc.test | 19 | ||||
-rw-r--r-- | mysql-test/t/join_outer.test | 44 |
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 |