diff options
Diffstat (limited to 'mysql-test/r/order_by.result')
| -rw-r--r-- | mysql-test/r/order_by.result | 82 |
1 files changed, 65 insertions, 17 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 1405893213d..cf5c9386790 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3; +call mtr.add_suppression("Out of sort memory; increase server sort buffer size"); CREATE TABLE t1 ( id int(6) DEFAULT '0' NOT NULL, idservice int(5), @@ -514,7 +515,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where drop table t1,t2,t3; CREATE TABLE t1 ( `titre` char(80) NOT NULL default '', @@ -611,7 +612,7 @@ DS-MRR: use two IGNORE INDEX queries, otherwise we get cost races, because DS-MRR: records_in_range/read_time return the same numbers for all three indexes EXPLAIN SELECT * FROM t1 IGNORE INDEX (LongField, StringField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Using MRR; Using filesort +1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using where; Using filesort EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort @@ -643,7 +644,7 @@ create table t1(a int, b int, index(b)); insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -652,7 +653,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL @@ -1112,7 +1113,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range k2 k2 5 NULL 386 Using index condition; Using where; Using MRR; Using filesort +1 SIMPLE t2 range k2 k2 5 NULL 386 Using where; Using filesort SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; id c3 6 14 @@ -1426,15 +1427,14 @@ DROP TABLE t1; # create table t1(a int, b tinytext); insert into t1 values (1,2),(3,2); -set session sort_buffer_size= 30000; +set session sort_buffer_size= 1000; Warnings: -Warning 1292 Truncated incorrect sort_buffer_size value: '30000' +Warning 1292 Truncated incorrect sort_buffer_size value: '1000' set session max_sort_length= 2180; CALL mtr.add_suppression("Out of sort memory"); select * from t1 order by b; ERROR HY001: Out of sort memory, consider increasing server sort buffer size drop table t1; -call mtr.add_suppression("Out of sort memory; increase server sort buffer size"); # # Bug #39844: Query Crash Mysql Server 5.0.67 # @@ -1491,8 +1491,8 @@ SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using temporary; Using filesort -1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition +1 SIMPLE t1 ref a,b b 4 const 4 Using where; Using temporary; Using filesort +1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using where SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; @@ -1510,6 +1510,24 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; d 52.5 +SELECT t1.*,t2.* FROM t1, t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 5; +a b a b c d +ppfcz1 DE ppfcz1 14 6 52.5 +ppfcz1 DE ppfcz1 14 7 55.5 +ppfcz1 DE ppfcz1 14 8 57.5 +ppfcz1 DE ppfcz1 14 9 59.5 +ppfcz1 DE ppfcz1 14 10 61.5 +SELECT t1.*, t2.* FROM t3 AS t1, t2 AS t2 +WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' +ORDER BY t2.c LIMIT 5; +a b a b c d +ppfcz1 DE ppfcz1 14 6 52.5 +ppfcz1 DE ppfcz1 14 7 55.5 +ppfcz1 DE ppfcz1 14 8 57.5 +ppfcz1 DE ppfcz1 14 9 59.5 +ppfcz1 DE ppfcz1 14 10 61.5 DROP TABLE t1,t2,t3; CREATE TABLE t1 ( id1 INT NULL, @@ -1609,20 +1627,20 @@ INSERT INTO t2 SELECT a+4, b FROM t2; EXPLAIN SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort -1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) # should have "using filesort" EXPLAIN SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort -1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) # should have "using filesort" EXPLAIN SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort -1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) DROP TABLE t1, t2; # # Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and @@ -1646,6 +1664,36 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where DROP TABLE t1, t2; # +# Bug #707848: WHERE condition with OR + ORDER BY + field substitution +# +CREATE TABLE t1 (a int PRIMARY KEY); +INSERT INTO t1 VALUES +(9), (7), (11), (15), (2), (4), (1), (5), (14), (54), (3), (8); +EXPLAIN EXTENDED +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 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 +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; +a a +1 1 +2 2 +3 3 +4 4 +5 5 +7 7 +8 8 +9 9 +11 11 +14 14 +DROP TABLE t1; +# # Bug #59110: Memory leak of QUICK_SELECT_I allocated memory # and # Bug #59308: Incorrect result for @@ -1686,7 +1734,7 @@ LEFT JOIN t3 ON t2.i2 = t3.i3 ORDER BY t1.i1 LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 system NULL NULL NULL NULL 0 0.00 const row not found -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 240.00 Using index +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 100.00 Using index 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`i2` = `test`.`t1`.`i1`) order by `test`.`t1`.`i1` limit 5 |