diff options
Diffstat (limited to 'mysql-test/main/order_by.result')
| -rw-r--r-- | mysql-test/main/order_by.result | 292 |
1 files changed, 187 insertions, 105 deletions
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 1311f42dac2..96f5d9a49c3 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -1192,7 +1192,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 111 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index k2 k3 5 NULL 22318 Using where +1 SIMPLE t2 range k2 k2 5 NULL 7341 Using index condition; Using filesort +EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 6000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL k2 NULL NULL NULL 40960 Using where; Using filesort EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where @@ -1221,6 +1224,10 @@ id c3 176 14 186 14 196 14 +ALTER TABLE t2 DROP INDEX k3, ADD INDEX k3 (c3,c2); +EXPLAIN SELECT c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index k2 k3 10 NULL 22318 Using where; Using index DROP TABLE t1,t2; CREATE TABLE t1 ( a INT, @@ -1548,6 +1555,56 @@ UNIQUE KEY a_c (a,c), KEY (a)); INSERT INTO t1 VALUES (1, 10), (2, NULL); # Must use ref-or-null on the a_c index +ANALYZE FORMAT=JSON +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "cost": "REPLACED", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "read_sorted_file": { + "r_rows": 1, + "filesort": { + "sort_key": "t1.c", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 1, + "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,addon_fields", + "table": { + "table_name": "t1", + "access_type": "ref_or_null", + "possible_keys": ["a_c", "a"], + "key": "a_c", + "key_length": "10", + "used_key_parts": ["a", "c"], + "ref": ["const", "const"], + "loops": 1, + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "cost": "REPLACED", + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 50, + "r_filtered": 100, + "attached_condition": "t1.c = 10 or t1.c is null", + "using_index": true + } + } + } + } + ] + } +} EXPLAIN SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; id select_type table type possible_keys key key_len ref rows Extra @@ -2976,17 +3033,17 @@ EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index -1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index +1 SIMPLE t2 ref i_a i_a 5 test.t1.a 1 Using index EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index -1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 6 Using index +1 SIMPLE t2 ref i_a i_a 5 test.t1.a 1 Using index EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index -1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index +1 SIMPLE t2 ref i_a i_a 5 test.t1.a 1 Using index DROP TABLE t1,t2; # # MDEV-4974 memory leak in 5.5.32-MariaDB-1~wheezy-log @@ -3065,7 +3122,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY t3a ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary -1 PRIMARY t3b ref f3_key f3_key 6 test.t3a.f3 1 100.00 Using where; End temporary +1 PRIMARY t3b ref f3_key f3_key 6 test.t3a.f3 1 41.67 Using where; End temporary Warnings: Note 1003 select concat('foo',`test`.`t2`.`f2`) AS `field` from `test`.`t2` semi join ((`test`.`t3` `t3a` join `test`.`t3` `t3b`)) where `test`.`t3a`.`f3` < 'foo' or `test`.`t3b`.`f3` <> 'foo' order by concat('foo',`test`.`t2`.`f2`) DROP TABLE t1,t2,t3; @@ -3119,7 +3176,7 @@ id select_type table type possible_keys key key_len ref rows Extra # 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 range id_23_date,id_234_date id_23_date 2 NULL 8 Using where +1 SIMPLE t2 ref id_23_date,id_234_date id_23_date 2 const,const 8 Using where drop table t1,t2; # # MDEV-8989: ORDER BY optimizer ignores equality propagation @@ -3188,13 +3245,13 @@ 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 t2 index a a 5 NULL 25 Using where 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 t2 index a a 5 NULL 25 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 25; @@ -3401,6 +3458,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -3412,16 +3470,17 @@ ANALYZE "r_loops": 1, "r_total_time_ms": "REPLACED", "r_limit": 5, - "r_used_priority_queue": false, - "r_output_rows": 100, - "r_buffer_size": "REPLACED", - "r_sort_mode": "sort_key,packed_addon_fields", + "r_used_priority_queue": true, + "r_output_rows": 6, + "r_sort_mode": "sort_key,rowid", "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 100, "r_rows": 100, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -3453,7 +3512,7 @@ CREATE TABLE t2 SELECT * FROM t1; EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t1 index PRIMARY b 5 NULL 1 Using where +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 Using where SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; c 1 @@ -3501,11 +3560,10 @@ WHERE books.library_id = 8663 AND books.scheduled_for_removal=0 ) ORDER BY wings.id; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using filesort -1 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00 -2 MATERIALIZED books ref library_idx library_idx 4 const 2 100.00 Using where +1 PRIMARY wings ALL PRIMARY NULL NULL NULL 2 100.00 Using filesort +1 PRIMARY books ref library_idx library_idx 4 const 2 50.00 Using where; FirstMatch(wings) Warnings: -Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id` +Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`books`.`wings_id` = `test`.`wings`.`id` order by `test`.`wings`.`id` set optimizer_switch= @save_optimizer_switch; DROP TABLE books, wings; # @@ -3637,8 +3695,8 @@ WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL ORDER BY t2.key2 ASC -LIMIT 1) -from t1; +LIMIT 1) as "con" + from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a 10 Using index condition; Using where; Using filesort @@ -3649,25 +3707,19 @@ WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL ORDER BY t2.key2 ASC -LIMIT 1) -from t1; -(SELECT concat(id, '-', key1, '-', col1) -FROM t2 -WHERE -t2.key1 = t1.a and t2.key1 IS NOT NULL -ORDER BY -t2.key2 ASC -LIMIT 1) -900-0-123456 -901-1-123456 -902-2-123456 -903-3-123456 -904-4-123456 -905-5-123456 -906-6-123456 -907-7-123456 -908-8-123456 -909-9-123456 +LIMIT 1) as "con" + from t1; +con +100-0-123456 +101-1-123456 +102-2-123456 +103-3-123456 +104-4-123456 +105-5-123456 +106-6-123456 +107-7-123456 +108-8-123456 +109-9-123456 drop table t1,t2; # End of 10.3 tests # @@ -3758,6 +3810,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -3775,9 +3828,11 @@ ANALYZE "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 100, "r_rows": 100, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -3924,6 +3979,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -3941,9 +3997,11 @@ ANALYZE "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 5, "r_rows": 5, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -3973,6 +4031,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -3990,9 +4049,11 @@ ANALYZE "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 6, "r_rows": 6, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -4028,6 +4089,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -4045,9 +4107,11 @@ ANALYZE "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 6, "r_rows": 6, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -4097,6 +4161,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -4114,9 +4179,11 @@ ANALYZE "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 10, "r_rows": 10, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -4279,6 +4346,7 @@ ANALYZE }, "query_block": { "select_id": 1, + "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ @@ -4286,9 +4354,11 @@ ANALYZE "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "r_loops": 1, "rows": 50, "r_rows": 50, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -4303,6 +4373,7 @@ ANALYZE "r_hit_ratio": 0, "query_block": { "select_id": 2, + "cost": "REPLACED", "r_loops": 50, "r_total_time_ms": "REPLACED", "filesort": { @@ -4319,9 +4390,11 @@ ANALYZE "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "r_loops": 50, "rows": 50, "r_rows": 50, + "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, @@ -4400,7 +4473,8 @@ CREATE TABLE t1 (a INT, b int, primary key(a)); CREATE TABLE t2 (a INT, b INT); INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6), (2354,7),(321421,3),(535,2),(4535,3); -INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3); +INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3),(1000,1000),(2000,2000),(3000,3000); +INSERT INTO t2 select seq,seq from seq_10_to_100; # Join order should have the SJM scan table as the first table for both # the queries with GROUP BY and ORDER BY clause. EXPLAIN SELECT t1.a @@ -4408,9 +4482,9 @@ FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) ORDER BY t1.a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 10 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 97 Using where EXPLAIN FORMAT=JSON SELECT t1.a FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) @@ -4419,50 +4493,53 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { - "read_sorted_file": { - "filesort": { - "sort_key": "t1.a desc", - "table": { - "table_name": "<subquery2>", - "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 3, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "nested_loop": [ - { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 3, - "filtered": 100, - "attached_condition": "t2.b = 3 and t2.a is not null" - } - } - ] - } - } - } - } + "table": { + "table_name": "t1", + "access_type": "index", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["a"], + "loops": 1, + "rows": 10, + "cost": "COST_REPLACED", + "filtered": 100, + "using_index": true } }, { "table": { - "table_name": "t1", + "table_name": "<subquery2>", "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", + "possible_keys": ["distinct_key"], + "key": "distinct_key", "key_length": "4", "used_key_parts": ["a"], - "ref": ["test.t2.a"], + "ref": ["func"], "rows": 1, "filtered": 100, - "using_index": true + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "loops": 1, + "rows": 97, + "cost": "COST_REPLACED", + "filtered": 100, + "attached_condition": "t2.b = 3" + } + } + ] + } + } } } ] @@ -4481,9 +4558,9 @@ FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 10 Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 97 Using where EXPLAIN FORMAT=JSON SELECT t1.a, group_concat(t1.b) FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) @@ -4492,49 +4569,54 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t1.a desc", "table": { - "table_name": "<subquery2>", + "table_name": "t1", "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 3, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "nested_loop": [ - { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 3, - "filtered": 100, - "attached_condition": "t2.b = 3 and t2.a is not null" - } - } - ] - } - } + "possible_keys": ["PRIMARY"], + "loops": 1, + "rows": 10, + "cost": "COST_REPLACED", + "filtered": 100 } } } }, { "table": { - "table_name": "t1", + "table_name": "<subquery2>", "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", + "possible_keys": ["distinct_key"], + "key": "distinct_key", "key_length": "4", "used_key_parts": ["a"], - "ref": ["test.t2.a"], + "ref": ["func"], "rows": 1, - "filtered": 100 + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "loops": 1, + "rows": 97, + "cost": "COST_REPLACED", + "filtered": 100, + "attached_condition": "t2.b = 3" + } + } + ] + } + } } } ] |