diff options
Diffstat (limited to 'mysql-test/r/order_by.result')
| -rw-r--r-- | mysql-test/r/order_by.result | 207 |
1 files changed, 203 insertions, 4 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index b907f50f632..4cd9aebdf49 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -287,6 +287,8 @@ PRIMARY KEY (member_id) Warnings: Warning 1101 BLOB/TEXT column 'info' can't have a default value insert into t1 (member_id) values (1),(2),(3); +Warnings: +Warning 1364 Field 'info' doesn't have a default value select member_id, nickname, voornaam FROM t1 ORDER by lastchange_datum DESC LIMIT 2; member_id nickname voornaam @@ -297,7 +299,7 @@ create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b'); explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index +1 SIMPLE t1 range a a 22 NULL 2 Using where; Using index select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; a b c 1 NULL b @@ -1161,7 +1163,7 @@ INSERT INTO t1 SELECT a +32, b +32 FROM t1; INSERT INTO t1 SELECT a +64, b +64 FROM t1; EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL ab 4 NULL 10 Using index for group-by +1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; a 1 @@ -2569,7 +2571,7 @@ SELECT * FROM t1 r JOIN t1 s ON r.a = s.a WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 ORDER BY 1 LIMIT 10; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE r index PRIMARY PRIMARY 4 NULL 10 100.00 Using where; Using index +1 SIMPLE r range PRIMARY PRIMARY 4 NULL 12 100.00 Using where; Using index 1 SIMPLE s eq_ref PRIMARY PRIMARY 4 test.r.a 1 100.00 Using index Warnings: Note 1003 select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where ((`test`.`s`.`a` = `test`.`r`.`a`) and ((`test`.`r`.`a` in (2,9)) or ((`test`.`r`.`a` < 100) and (`test`.`r`.`a` <> 0)))) order by 1 limit 10 @@ -2600,7 +2602,7 @@ CREATE TABLE t1 (a INT,KEY (a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index; Using filesort +1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; a 1 10 1 @@ -3008,3 +3010,200 @@ explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 li id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer drop table t1,t2; +# +# MDEV-465: Optimizer : wrong index choice, leading to strong performances issues +# +CREATE TABLE t1 ( +id1 int(10) unsigned NOT NULL auto_increment, +id2 tinyint(3) unsigned NOT NULL default '0', +id3 tinyint(3) unsigned NOT NULL default '0', +id4 int(10) unsigned NOT NULL default '0', +date timestamp NOT NULL default CURRENT_TIMESTAMP, +PRIMARY KEY (id1), +KEY id_234_date (id2,id3,id4,date), +KEY id_23_date (id2,id3,date) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +# t1 has "bad" index declaration order.. +CREATE TABLE t2 ( +id1 int(10) unsigned NOT NULL auto_increment, +id2 tinyint(3) unsigned NOT NULL default '0', +id3 tinyint(3) unsigned NOT NULL default '0', +id4 int(10) unsigned NOT NULL default '0', +date timestamp NOT NULL default CURRENT_TIMESTAMP, +PRIMARY KEY (id1), +KEY id_23_date (id2,id3,date), +KEY id_234_date (id2,id3,id4,date) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +# t2 has a "good" index declaration order +INSERT INTO t1 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); +INSERT INTO t2 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); +# The following two must both use id_23_date and no "using filesort": +EXPLAIN SELECT id1 FROM t1 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range id_234_date,id_23_date id_23_date 2 NULL 3 Using where +# See above query +EXPLAIN SELECT id1 FROM t2 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref id_23_date,id_234_date id_23_date 2 const,const 3 Using where +drop table t1,t2; +# +# MDEV-8989: ORDER BY optimizer ignores equality propagation +# +set @tmp_8989=@@optimizer_switch; +set optimizer_switch='orderby_uses_equalities=on'; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( +pk int primary key, +a int, b int, +filler char(200), +key(a) +); +insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200; +create table t3 ( +pk int primary key, +a int, b int, +filler char(200), +key(a) +); +insert into t3 select a, 1000-a, 1000-a, repeat('abc-',50) from t1; +insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1; +# The optimizer produces an order of 't2,t3' for this join +# +# Case #1 (from the bug report): +# Q1 can take advantage of t2.a to resolve ORDER BY limit w/o sorting +explain +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t2.a limit 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index a a 5 NULL 5 Using where +1 SIMPLE t3 ref a a 5 test.t2.a 1 +# +# This is Q2 which used to have "Using temporary; using filesort" but +# has the same query plan as Q1: +# +explain +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t3.a limit 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index a a 5 NULL 5 Using where +1 SIMPLE t3 ref a a 5 test.t2.a 1 +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t2.a limit 5; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t3.a limit 5; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +# +# Case #2: here, only "Using temporary" is removed. "Using filesort" remains. +# +explain +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t2.a limit 25; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort +1 SIMPLE t3 ref a a 5 test.t2.a 1 +explain +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t3.a limit 25; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort +1 SIMPLE t3 ref a a 5 test.t2.a 1 +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t2.a limit 25; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +194 806 806 194 806 806 +193 807 807 193 807 807 +192 808 808 192 808 808 +191 809 809 191 809 809 +190 810 810 190 810 810 +189 811 811 189 811 811 +188 812 812 188 812 812 +187 813 813 187 813 813 +186 814 814 186 814 814 +185 815 815 185 815 815 +184 816 816 184 816 816 +183 817 817 183 817 817 +182 818 818 182 818 818 +181 819 819 181 819 819 +180 820 820 180 820 820 +179 821 821 179 821 821 +178 822 822 178 822 822 +177 823 823 177 823 823 +176 824 824 176 824 824 +175 825 825 175 825 825 +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t3.a limit 25; +pk a b pk a b +199 801 801 199 801 801 +198 802 802 198 802 802 +197 803 803 197 803 803 +196 804 804 196 804 804 +195 805 805 195 805 805 +194 806 806 194 806 806 +193 807 807 193 807 807 +192 808 808 192 808 808 +191 809 809 191 809 809 +190 810 810 190 810 810 +189 811 811 189 811 811 +188 812 812 188 812 812 +187 813 813 187 813 813 +186 814 814 186 814 814 +185 815 815 185 815 815 +184 816 816 184 816 816 +183 817 817 183 817 817 +182 818 818 182 818 818 +181 819 819 181 819 819 +180 820 820 180 820 820 +179 821 821 179 821 821 +178 822 822 178 822 822 +177 823 823 177 823 823 +176 824 824 176 824 824 +175 825 825 175 825 825 +# +# Case #3: single table access (the code that decides whether we need +# "Using temporary" is not invoked) +# +explain select * from t3 where b=a order by a limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index NULL a 5 NULL 10 Using where +# This must not use filesort. The query plan should be like the query above: +explain select * from t3 where b=a order by b limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index NULL a 5 NULL 10 Using where +drop table t0,t1,t2,t3; +set @@optimizer_switch=@tmp_8989; +set optimizer_switch='orderby_uses_equalities=on'; +# +# MDEV-10880: Assertions `keypart_map' or +# `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and +# GROUP BY constant +# +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT DISTINCT pk FROM t1 GROUP BY 'foo'; +pk +1 +SELECT DISTINCT pk FROM t1; +pk +1 +2 +3 +DROP TABLE t1; |