diff options
81 files changed, 635 insertions, 559 deletions
diff --git a/include/my_base.h b/include/my_base.h index 5bc778655dc..7efa5eb9673 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -615,7 +615,6 @@ enum data_file_type { #define EQ_RANGE 32U #define NULL_RANGE 64U #define GEOM_FLAG 128U -#define SKIP_RANGE 256U typedef struct st_key_range { diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index aa7ab6e60a1..be892cf774b 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -1,3 +1,5 @@ +--source include/have_sequence.inc + --echo # --echo # Bug#36981 - "innodb crash when selecting for update" --echo # @@ -721,10 +723,12 @@ DROP TABLE t1; CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index a24ebdd1fff..f105330976a 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1159,10 +1159,10 @@ with cte as union (select a from t1 where a < 2); a +1 4 5 7 -1 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) (select a from cte where exists( select a from t1 where cte.a=t1.a )) @@ -1170,16 +1170,16 @@ union (select a from t1 where a < 2)"; execute stmt; a +1 4 5 7 -1 execute stmt; a +1 4 5 7 -1 deallocate prepare stmt; with cte as (select a from t1 where a between 4 and 7 group by a) @@ -1216,9 +1216,9 @@ union (select a from cte where exists( select a from t1 where cte.a=t1.a )); a 1 -7 -5 4 +5 +7 prepare stmt from "with cte as (select a from t1 where a between 4 and 7) (select a from t1 where a < 2) @@ -1227,15 +1227,15 @@ union execute stmt; a 1 -7 -5 4 +5 +7 execute stmt; a 1 -7 -5 4 +5 +7 deallocate prepare stmt; with cte as (select a from t1 where a between 4 and 7) @@ -1244,9 +1244,9 @@ where exists( select a from t1 where t1.a < 2 and cte.a=t1.a )) union (select a from cte where exists( select a from t1 where cte.a=t1.a )); a -7 -5 4 +5 +7 prepare stmt from "with cte as (select a from t1 where a between 4 and 7) (select a from cte @@ -1255,14 +1255,14 @@ union (select a from cte where exists( select a from t1 where cte.a=t1.a ))"; execute stmt; a -7 -5 4 +5 +7 execute stmt; a -7 -5 4 +5 +7 deallocate prepare stmt; drop table t1; # diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index f311271d4d2..b39d6cde8a5 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -808,9 +808,12 @@ with cte as union (select a from t1 where a < 2); +--sorted_result eval $q1; eval prepare stmt from "$q1"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; @@ -821,9 +824,12 @@ with cte as union (select a from cte where exists( select a from t1 where cte.a=t1.a )); +--sorted_result eval $q2; eval prepare stmt from "$q2"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; @@ -834,9 +840,12 @@ with cte as union (select a from cte where exists( select a from t1 where cte.a=t1.a )); +--sorted_result eval $q3; eval prepare stmt from "$q3"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; @@ -848,9 +857,12 @@ with cte as union (select a from cte where exists( select a from t1 where cte.a=t1.a )); +--sorted_result eval $q4; eval prepare stmt from "$q4"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 0f6da7d9495..8264e311f93 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1064,16 +1064,19 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +INSERT INTO t1 select seq from seq_1_to_1000; +INSERT INTO t2 select seq+1000 from seq_1_to_1000; +INSERT INTO t3 select 'qqq',seq+2000 from seq_1_to_1000; set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -1 PRIMARY <derived3> hash_ALL NULL #hash#$hj 3075 func 2 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1002 +1 PRIMARY <derived3> hash_ALL NULL #hash#$hj 3075 func 1002 Using where; Using join buffer (flat, BNLH join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 v3.d 1 Using index -3 DERIVED t3 ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ALL NULL NULL NULL NULL 2 +3 DERIVED t3 ALL NULL NULL NULL NULL 1002 +2 DERIVED t1 ALL NULL NULL NULL NULL 1002 SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; a b c d DROP VIEW v1, v3; diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index f4477ce8550..8c41f80ffbd 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1,5 +1,6 @@ # Initialize --source include/default_optimizer_switch.inc +--source include/have_sequence.inc --disable_warnings drop table if exists t1,t2,t3; @@ -919,6 +920,9 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +INSERT INTO t1 select seq from seq_1_to_1000; +INSERT INTO t2 select seq+1000 from seq_1_to_1000; +INSERT INTO t3 select 'qqq',seq+2000 from seq_1_to_1000; set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index b853426fdf6..c7f6448f2d8 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -8828,9 +8828,21 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<derived2>", + "table_name": "t1", "access_type": "ALL", - "rows": 3, + "rows": 4, + "filtered": 100, + "attached_condition": "t1.id2 is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["id2"], + "ref": ["test.t1.id2"], + "rows": 2, "filtered": 100, "attached_condition": "vc.ct > 0", "materialized": { @@ -8850,18 +8862,6 @@ EXPLAIN } } } - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 4, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "163", - "join_type": "BNL", - "attached_condition": "t1.id2 = vc.id2" } } } @@ -9078,9 +9078,8 @@ WHERE d_tab.e>1 ) ; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE (t1.a,t1.b) IN @@ -9103,44 +9102,33 @@ EXPLAIN "table_name": "t1", "access_type": "ALL", "rows": 5, - "filtered": 100 + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null" }, "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "10", "used_key_parts": ["e", "max_f"], - "ref": ["func", "func"], - "rows": 1, + "ref": ["test.t1.a", "test.t1.b"], + "rows": 2, "filtered": 100, + "first_match": "t1", "materialized": { - "unique": 1, "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 5, - "filtered": 100, - "attached_condition": "d_tab.e > 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_f > 18", - "filesort": { - "sort_key": "t2.e", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 5, - "filtered": 100, - "attached_condition": "t2.e > 1" - } - } - } + "select_id": 3, + "having_condition": "max_f > 18", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.e > 1" } } } @@ -9179,9 +9167,8 @@ WHERE d_tab.max_f<25 ) ; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE (t1.a,t1.b) IN @@ -9204,43 +9191,32 @@ EXPLAIN "table_name": "t1", "access_type": "ALL", "rows": 5, - "filtered": 100 + "filtered": 100, + "attached_condition": "t1.b < 25 and t1.a is not null and t1.b is not null" }, "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "10", "used_key_parts": ["e", "max_f"], - "ref": ["func", "func"], - "rows": 1, + "ref": ["test.t1.a", "test.t1.b"], + "rows": 2, "filtered": 100, + "first_match": "t1", "materialized": { - "unique": 1, "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 5, - "filtered": 100, - "attached_condition": "d_tab.max_f < 25", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_f > 18 and max_f < 25", - "filesort": { - "sort_key": "t2.e", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 5, - "filtered": 100 - } - } - } + "select_id": 3, + "having_condition": "max_f > 18 and max_f < 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 } } } @@ -14045,16 +14021,16 @@ a b max_c a b c 1 21 345 3 21 231 select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); a b max_c a b c -2 33 7 5 33 207 4 33 123 5 33 207 -2 33 7 8 33 117 +2 33 7 5 33 207 4 33 123 8 33 117 -1 21 345 3 21 231 +2 33 7 8 33 117 3 21 500 3 21 231 +1 21 345 3 21 231 explain select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2 Using where 2 DERIVED t3 range i1 i1 5 NULL 5 Using index condition explain format=json select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); EXPLAIN @@ -14062,9 +14038,21 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<derived2>", + "table_name": "t2", "access_type": "ALL", - "rows": 5, + "rows": 9, + "filtered": 100, + "attached_condition": "t2.b is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["b"], + "ref": ["test.t2.b"], + "rows": 2, "filtered": 100, "attached_condition": "v1.a < 5", "materialized": { @@ -14083,18 +14071,6 @@ EXPLAIN } } } - }, - "block-nl-join": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 9, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "173", - "join_type": "BNL", - "attached_condition": "t2.b = v1.b" } } } @@ -16868,8 +16844,8 @@ explain extended select id, a from t1 where id in (select id from v1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 20 100.00 1 PRIMARY <derived3> ref key0 key0 4 test.t1.id 2 100.00 FirstMatch(t1) -3 DERIVED t1 ALL PRIMARY NULL NULL NULL 20 100.00 Using temporary; Using filesort -3 DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where +3 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 +3 LATERAL DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`v1`) where `v1`.`id` = `test`.`t1`.`id` select id, a from t1 @@ -16906,10 +16882,10 @@ group by t1.id) dt); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 20 100.00 1 PRIMARY <derived3> ref key0 key0 4 test.t1.id 2 100.00 FirstMatch(t1) -3 DERIVED t1 ALL PRIMARY NULL NULL NULL 20 100.00 Using temporary; Using filesort -3 DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where +3 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 +3 LATERAL DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`ro_id` = `test`.`t1`.`id` and `test`.`t2`.`flag` = 1) where 1 group by `test`.`t1`.`id`) `dt`) where `dt`.`id` = `test`.`t1`.`id` +Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`ro_id` = `test`.`t1`.`id` and `test`.`t2`.`flag` = 1) where `test`.`t1`.`id` = `test`.`t1`.`id` group by `test`.`t1`.`id`) `dt`) where `dt`.`id` = `test`.`t1`.`id` drop view v1; drop table t1,t2; # End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index e8f9df5f80d..15b67b51f45 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -9,6 +9,7 @@ KEY c1 (c1), KEY n1_c1_n2 (n1,c1,n2) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); +insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -16,7 +17,7 @@ test.t1 analyze status OK EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index c1,n1_c1_n2 n1_c1_n2 9 NULL 2 Using where; Using index +1 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 4f9d2e970f7..d4d7fde1fcd 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -1,5 +1,6 @@ --source include/have_innodb.inc --source include/default_optimizer_switch.inc +--source include/have_sequence.inc --echo # --echo # MDEV-16917: do not use splitting for derived with join cache @@ -13,6 +14,7 @@ CREATE TABLE t1 ( KEY n1_c1_n2 (n1,c1,n2) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); +insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000; ANALYZE TABLE t1; diff --git a/mysql-test/main/disabled.def b/mysql-test/main/disabled.def index 02104f6a680..d3366587ee5 100644 --- a/mysql-test/main/disabled.def +++ b/mysql-test/main/disabled.def @@ -18,3 +18,5 @@ file_contents : MDEV-6526 these files are not installed anymore max_statement_time : cannot possibly work, depends on timing partition_open_files_limit : open_files_limit check broken by MDEV-18360 partition_innodb : Waiting for fix MDEV-20169 +type_enum : Waiting for fix MDEV-6978 +type_set : Waiting for fix MDEV-6978 diff --git a/mysql-test/main/explain_json.result b/mysql-test/main/explain_json.result index a48cfcf213b..18a15795aab 100644 --- a/mysql-test/main/explain_json.result +++ b/mysql-test/main/explain_json.result @@ -1037,7 +1037,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by (scanning) +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); EXPLAIN { @@ -1070,7 +1070,7 @@ EXPLAIN "rows": 65, "filtered": 100, "attached_condition": "t1.b = 'a' and t1.c = 'i121' and t1.a2 >= 'b'", - "using_index_for_group_by": "scanning" + "using_index_for_group_by": true } } } diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index 568c2a3cdd5..3a49c075ddd 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -1578,7 +1578,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) FORCE INDEX FOR GROUP BY (i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 144 Using index +1 SIMPLE t1 range NULL i2 4 NULL 145 Using index for group-by EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 144 diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index d55e57a8432..4f32db780fd 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -3822,20 +3822,20 @@ set @@optimizer_use_condition_selectivity=4; set @@use_stat_tables=PREFERABLY; explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_outer index a a 10 NULL 30 100.00 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1_outer.a 1 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 7 100.00 Using index 2 MATERIALIZED t1 range NULL a 5 NULL 5 100.00 Using index for group-by Warnings: -Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `<subquery2>`.`max(b)` = `test`.`t1_outer`.`a` +Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)` set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables=@save_use_stat_tables; explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_outer index a a 10 NULL 30 100.00 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1_outer.a 1 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 7 100.00 Using index 2 MATERIALIZED t1 range NULL a 5 NULL 5 100.00 Using index for group-by Warnings: -Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `<subquery2>`.`max(b)` = `test`.`t1_outer`.`a` +Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)` drop table t1; # # End of 10.0 tests diff --git a/mysql-test/main/index_intersect.result b/mysql-test/main/index_intersect.result index 7a8609f39e8..3ec98216479 100644 --- a/mysql-test/main/index_intersect.result +++ b/mysql-test/main/index_intersect.result @@ -370,7 +370,7 @@ EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Country,Name Population,Country 4,3 NULL # Using sort_intersect(Population,Country); Using where +1 SIMPLE City index_merge Population,Country,Name Population,Country,Name 4,3,35 NULL # Using sort_intersect(Population,Country,Name); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; @@ -695,7 +695,7 @@ ID Name Country Population 3808 Austin USA 656562 3809 Baltimore USA 651154 3810 Memphis USA 650100 -SET SESSION sort_buffer_size = 2048; +SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; diff --git a/mysql-test/main/index_intersect.test b/mysql-test/main/index_intersect.test index b8ab5a688c5..26937fd5eef 100644 --- a/mysql-test/main/index_intersect.test +++ b/mysql-test/main/index_intersect.test @@ -275,10 +275,22 @@ SELECT * FROM City USE INDEX () SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; - - -SET SESSION sort_buffer_size = 2048; - + +# Originally this was just sort_buffer_size=2048. Then, it started +# failing on 32bit due to different cost number in +# Unique::get_use_cost() because of sizeof(sizeof(TREE_ELEMENT)+key_size) + +# On 64 bit: Unique object element_size=32, which gives 2048/32= 64 elements +# in the tree. +# On 32 bit: Unique object element_size=24. +# If we want 64 elements in the tree, we need 64*24=1536 as sort_buffer_size. + +# The purpose of setting sort_buffer_size is to show that some of the following +# explains should use 'index_merge' while others should use range +# If the following code causes future problems, the other option would be +# to create a separate result-.diff file for 32 bit. + +SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); # The following EXPLAIN command demonstrate that the execution plans # may be different if sort_buffer_size is set to a small value diff --git a/mysql-test/main/index_intersect_innodb.result b/mysql-test/main/index_intersect_innodb.result index 9f279eaf8af..c070b41e54d 100644 --- a/mysql-test/main/index_intersect_innodb.result +++ b/mysql-test/main/index_intersect_innodb.result @@ -701,7 +701,7 @@ ID Name Country Population 3808 Austin USA 656562 3809 Baltimore USA 651154 3810 Memphis USA 650100 -SET SESSION sort_buffer_size = 2048; +SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 3ff72871342..c1597e583a4 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -750,7 +750,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 3515 Using intersect(st_a,st_b); Using where; Using index explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a 4 const 15568 Using where +1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 15094 Using where explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971 diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index f29f81c0ee4..ff92b4506ba 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -13,7 +13,7 @@ set optimizer_switch='extended_keys=off'; explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity|i_l_shipdate 4|4 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 const 5 Using where flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; count(*) @@ -21,9 +21,9 @@ count(*) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 5 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -33,7 +33,7 @@ set optimizer_switch='extended_keys=on'; explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|8 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; count(*) @@ -41,9 +41,9 @@ count(*) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 2 +Handler_read_next 1 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -274,7 +274,7 @@ explain select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity|i_l_shipdate 4|4 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 const 5 Using where flush status; select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; @@ -283,9 +283,9 @@ max(l_linenumber) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 5 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index 97d2f209be7..16d60ddba43 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -682,15 +682,17 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 101 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -701,7 +703,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 101 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index 1a59c7b16e9..e5fa88512b9 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1473,8 +1473,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE DU system dog_id NULL NULL NULL 1 1 SIMPLE D system PRIMARY NULL NULL NULL 1 1 SIMPLE DSAR system NULL NULL NULL NULL 1 -1 SIMPLE DT range t_id t_id 2 NULL 2 Using where -1 SIMPLE DSA ref PRIMARY PRIMARY 8 const,test.DT.t_id,func 1 Using index +1 SIMPLE DSA ref PRIMARY PRIMARY 4 const 3 Using where; Using index +1 SIMPLE DT ALL t_id NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR WHERE DU.dog_id=D.dog_id AND D.dog_id=DT.dog_id AND D.birthday=DT.birthday AND DT.t_id=DSA.t_id AND DT.birthday=DSA.birthday AND DSA.dog_id=DSAR.dog_id; diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index f990efdd06f..5bfcf7f20e1 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -444,9 +444,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where -1 SIMPLE t13 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t13 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE m2 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index @@ -466,9 +466,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where -1 SIMPLE t13 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t13 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE m2 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index diff --git a/mysql-test/main/key_cache.result b/mysql-test/main/key_cache.result index b271d0a1289..c8bdc979387 100644 --- a/mysql-test/main/key_cache.result +++ b/mysql-test/main/key_cache.result @@ -739,13 +739,13 @@ p 1019 explain select i from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using rowid filter +1 SIMPLE t2 index_merge k1,k2 k1,k2 5,11 NULL 50 Using intersect(k1,k2); Using where; Using index select i from t2 where a='yyyy' and i=3; i 3 explain select a from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using rowid filter +1 SIMPLE t2 index_merge k1,k2 k1,k2 5,11 NULL 50 Using intersect(k1,k2); Using where; Using index select a from t2 where a='yyyy' and i=3 ; a yyyy @@ -753,7 +753,7 @@ select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 NULL 262143 2048 # # 0 3245 43 1594 30 +keycache1 7 NULL 262143 2048 # # 0 3229 43 1594 30 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=2*1024; insert into t2 values (7000, 3, 'yyyy'); diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index a4fbdd794a7..2ff966fdfd3 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -3091,12 +3091,11 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 @@ -3105,7 +3104,7 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 @@ -3126,19 +3125,17 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value @@ -3147,13 +3144,12 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 @@ -3162,14 +3158,14 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 @@ -3182,20 +3178,18 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead EXPLAIN UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived4> ref key0 key0 5 test.t1.a 2 FirstMatch(t1) 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 -3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 4 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived4> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 -3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00 4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 # Status of EXPLAIN EXTENDED query @@ -3205,13 +3199,12 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived4> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) -3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00 4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 @@ -3220,14 +3213,14 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index e49158fdbe9..0ae18e6e817 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -675,16 +675,18 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c @@ -694,8 +696,8 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result index d9e51c3fba0..6b9d59c636a 100644 --- a/mysql-test/main/null_key.result +++ b/mysql-test/main/null_key.result @@ -160,7 +160,7 @@ a b 7 NULL explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 5 const 5 Using where; Using index select * from t1 where (a = 7 or a is null) and (b=7 or b is null); a b 7 NULL @@ -194,7 +194,7 @@ a a b explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -204,7 +204,7 @@ a a b explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; a a b 7 7 7 @@ -214,7 +214,7 @@ a a b explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 5 Using where; Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); a a b 7 7 NULL diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index ff4bf09944b..65fc7d6cc98 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1022,7 +1022,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 200, + "cost": 200.06, "chosen": true }, { @@ -1035,13 +1035,13 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 200, + "cost": 200.06, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 100, - "cost_for_plan": 242.32, + "cost_for_plan": 242.38, "estimated_join_cardinality": 100 } ] @@ -1080,7 +1080,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 200, + "cost": 200.06, "chosen": true }, { @@ -1093,13 +1093,13 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 200, + "cost": 200.06, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 100, - "cost_for_plan": 242.32, + "cost_for_plan": 242.38, "pruned_by_cost": true } ] @@ -1199,7 +1199,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { ], "best_covering_index_scan": { "index": "a", - "cost": 14185, + "cost": 13377, "chosen": false, "cause": "cost" }, @@ -1210,7 +1210,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "index": "a", "covering": true, "rows": 5, - "cost": 6.75 + "cost": 6.25 } ] }, @@ -1222,7 +1222,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.75, + "cost": 6.25, "key_parts_used_for_access": ["a"], "ranges": [], "chosen": true @@ -1236,12 +1236,12 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.75, + "cost": 6.25, "key_parts_used_for_access": ["a"], "ranges": [] }, "rows_for_plan": 5, - "cost_for_plan": 6.75, + "cost_for_plan": 6.25, "chosen": true } } @@ -1258,20 +1258,20 @@ EXPLAIN SELECT DISTINCT a FROM t1 { { "access_type": "index_merge", "resulting_rows": 5, - "cost": 6.75, + "cost": 6.25, "chosen": true } ], "chosen_access_method": { "type": "index_merge", "records": 5, - "cost": 6.75, + "cost": 6.25, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 5, - "cost_for_plan": 7.75, + "cost_for_plan": 7.25, "estimated_join_cardinality": 5 } ] @@ -1384,7 +1384,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "best_covering_index_scan": { "index": "a", - "cost": 2.4369, + "cost": 2.4092, "chosen": true }, "setup_range_conditions": [], @@ -1578,7 +1578,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.2468, + "cost": 4.2117, "chosen": true }, "setup_range_conditions": [], @@ -1761,7 +1761,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.2468, + "cost": 4.2117, "chosen": true }, "setup_range_conditions": [], @@ -2008,7 +2008,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 218.74, + "cost": 216.29, "chosen": true }, { @@ -2018,7 +2018,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 21, - "cost": 25.415, + "cost": 25.362, "chosen": true } ], @@ -2039,7 +2039,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1,2) <= (a,b) <= (1,2)"] }, "rows_for_plan": 21, - "cost_for_plan": 25.415, + "cost_for_plan": 25.362, "chosen": true } } @@ -2077,7 +2077,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_c", "used_range_estimates": true, "rows": 180, - "cost": 92, + "cost": 180.27, "chosen": true }, { @@ -2085,7 +2085,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_b", "used_range_estimates": true, "rows": 21, - "cost": 25.395, + "cost": 21.142, "chosen": true }, { @@ -2097,13 +2097,13 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "chosen_access_method": { "type": "ref", "records": 21, - "cost": 25.395, + "cost": 21.142, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 21, - "cost_for_plan": 29.595, + "cost_for_plan": 25.342, "estimated_join_cardinality": 21 } ] @@ -2127,7 +2127,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "fanout": 1, - "read_time": 25.396, + "read_time": 21.143, "table": "t1", "rows_estimation": 21, "possible_keys": [ @@ -2143,8 +2143,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_c", "can_resolve_order": true, "updated_limit": 47, - "range_scan_time": 4.324, - "index_scan_time": 4.324, + "range_scan_time": 4.331, + "index_scan_time": 4.331, "records": 180, "chosen": true }, @@ -2190,7 +2190,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 218.74, + "cost": 216.29, "chosen": true } ], @@ -2211,7 +2211,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1) <= (a) <= (1)"] }, "rows_for_plan": 180, - "cost_for_plan": 218.74, + "cost_for_plan": 216.29, "chosen": true } } @@ -3163,7 +3163,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "best_covering_index_scan": { "index": "pk_a_b", - "cost": 3.0429, + "cost": 3.0107, "chosen": true }, "setup_range_conditions": [], @@ -3176,7 +3176,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3473, + "cost": 1.3456, "chosen": true }, { @@ -3186,7 +3186,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3483, + "cost": 1.3458, "chosen": false, "cause": "cost" }, @@ -3197,7 +3197,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3493, + "cost": 0.3461, "chosen": true } ], @@ -3205,10 +3205,10 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "intersecting_indexes": [ { "index": "pk", - "index_scan_cost": 1.0023, - "cumulated_index_scan_cost": 1.0023, + "index_scan_cost": 1.0006, + "cumulated_index_scan_cost": 1.0006, "disk_sweep_cost": 0.9008, - "cumulative_total_cost": 1.9031, + "cumulative_total_cost": 1.9014, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, @@ -3246,7 +3246,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"] }, "rows_for_plan": 1, - "cost_for_plan": 0.3493, + "cost_for_plan": 0.3461, "chosen": true } } @@ -3284,7 +3284,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk", "used_range_estimates": true, "rows": 1, - "cost": 1.3273, + "cost": 1.1256, "chosen": true }, { @@ -3292,7 +3292,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a", "used_range_estimates": true, "rows": 1, - "cost": 1.3283, + "cost": 1.1258, "chosen": false, "cause": "cost" }, @@ -3301,7 +3301,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a_b", "used_range_estimates": true, "rows": 1, - "cost": 0.3293, + "cost": 0.1261, "chosen": true }, { @@ -3313,13 +3313,13 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 0.3293, + "cost": 0.1261, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 1, - "cost_for_plan": 0.5293, + "cost_for_plan": 0.3261, "estimated_join_cardinality": 1 } ] @@ -3657,7 +3657,7 @@ explain delete from t0 where t0.a<3 { "using_mrr": false, "index_only": false, "rows": 3, - "cost": 3.752, + "cost": 3.7468, "chosen": true } ], @@ -3675,7 +3675,7 @@ explain delete from t0 where t0.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 3.752, + "cost_for_plan": 3.7468, "chosen": true } } @@ -3787,7 +3787,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 3.0234, + "cost": 3.0059, "chosen": true }, "setup_range_conditions": [], @@ -3800,7 +3800,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], @@ -3821,7 +3821,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 0.752, + "cost_for_plan": 0.7468, "chosen": true } } @@ -3852,7 +3852,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 3.0234, + "cost": 3.0059, "chosen": true }, "setup_range_conditions": [], @@ -3865,7 +3865,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], @@ -3886,7 +3886,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 0.752, + "cost_for_plan": 0.7468, "chosen": true } } @@ -3913,20 +3913,20 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "range", "resulting_rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 3, - "cost": 0.752, + "cost": 0.7468, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.352, + "cost_for_plan": 1.3468, "rest_of_plan": [ { "plan_prefix": ["t0"], @@ -3939,7 +3939,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 1, - "cost": 3.007, + "cost": 3.0018, "chosen": true }, { @@ -3951,13 +3951,13 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 3.007, + "cost": 3.0018, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 3, - "cost_for_plan": 4.959, + "cost_for_plan": 4.9485, "estimated_join_cardinality": 3 } ] @@ -3970,20 +3970,20 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "range", "resulting_rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 3, - "cost": 0.752, + "cost": 0.7468, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.352, + "cost_for_plan": 1.3468, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -3996,7 +3996,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 2, - "cost": 3.014, + "cost": 3.0035, "chosen": true }, { @@ -4008,13 +4008,13 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "chosen_access_method": { "type": "ref", "records": 2, - "cost": 3.014, + "cost": 3.0035, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 6, - "cost_for_plan": 5.5661, + "cost_for_plan": 5.5503, "pruned_by_cost": true } ] @@ -7501,7 +7501,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3483, + "cost": 0.3458, "chosen": true } ], @@ -7535,7 +7535,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 107, - "cost": 22.9, + "cost": 21.634, "chosen": true } ], @@ -7572,7 +7572,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1221.1, + "cost": 1203.9, "chosen": true } ], @@ -7617,7 +7617,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 4, - "cost": 4.9598, + "cost": 4.9487, "chosen": true } ], @@ -7656,7 +7656,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3497, + "cost": 1.3462, "chosen": true } ], @@ -7690,7 +7690,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3497, + "cost": 1.3462, "chosen": true } ], @@ -7732,7 +7732,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3487, + "cost": 1.3459, "chosen": true } ], @@ -7767,7 +7767,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3485, + "cost": 1.3459, "chosen": true } ], @@ -7802,7 +7802,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3487, + "cost": 1.3459, "chosen": true } ], @@ -7840,7 +7840,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3485, + "cost": 1.3459, "chosen": true } ], @@ -7881,7 +7881,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.5419, + "cost": 1.3943, "chosen": true } ], @@ -7920,7 +7920,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2, - "cost": 2.5524, + "cost": 2.5469, "chosen": true } ], @@ -7976,7 +7976,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1221.1, + "cost": 1203.9, "chosen": true } ], @@ -8185,7 +8185,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 20, + "cost": 20.006, "chosen": true }, @@ -8200,13 +8200,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "type": "ref", "records": 1, - "cost": 20, + "cost": 20.006, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 10, - "cost_for_plan": 26.017, + "cost_for_plan": 26.023, "selectivity": 0.8047, "estimated_join_cardinality": 8.0469 } @@ -8274,7 +8274,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3965, + "cost": 1.3579, "chosen": true } ], diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index e76365a8af6..ca8098391d5 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -111,12 +111,12 @@ explain select * from t1 where a=1 or b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3473, + "cost": 0.3456, "chosen": true } ], "index_to_merge": "a", - "cumulated_cost": 0.3473 + "cumulated_cost": 0.3456 }, { "range_scan_alternatives": [ @@ -127,15 +127,15 @@ explain select * from t1 where a=1 or b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3473, + "cost": 0.3456, "chosen": true } ], "index_to_merge": "b", - "cumulated_cost": 0.6947 + "cumulated_cost": 0.6912 } ], - "cost_of_reading_ranges": 0.6947, + "cost_of_reading_ranges": 0.6912, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": [ @@ -158,7 +158,7 @@ explain select * from t1 where a=1 or b=1 { } } ], - "index_roworder_union_cost": 2.4884, + "index_roworder_union_cost": 2.4849, "members": 2, "chosen": true } @@ -187,7 +187,7 @@ explain select * from t1 where a=1 or b=1 { ] }, "rows_for_plan": 2, - "cost_for_plan": 2.4884, + "cost_for_plan": 2.4849, "chosen": true } } @@ -209,20 +209,20 @@ explain select * from t1 where a=1 or b=1 { { "access_type": "index_merge", "resulting_rows": 2, - "cost": 2.4884, + "cost": 2.4849, "chosen": true } ], "chosen_access_method": { "type": "index_merge", "records": 2, - "cost": 2.4884, + "cost": 2.4849, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 2, - "cost_for_plan": 2.8884, + "cost_for_plan": 2.8849, "estimated_join_cardinality": 2 } ] @@ -323,7 +323,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2730, + "cost": 2700.1, "chosen": true }, @@ -337,7 +337,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2730, + "cost": 2700.1, "chosen": false, "cause": "cost" }, @@ -352,7 +352,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2730, + "cost": 2700.1, "chosen": false, "cause": "cost" } @@ -364,10 +364,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key1", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 40.252, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 10.314, "disk_sweep_cost": 1923.1, - "cumulative_total_cost": 1963.4, + "cumulative_total_cost": 1933.5, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -376,10 +376,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key2", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 80.504, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 20.628, "disk_sweep_cost": 84.518, - "cumulative_total_cost": 165.02, + "cumulative_total_cost": 105.15, "usable": true, "matching_rows_now": 77.636, "intersect_covering_with_this_index": false, @@ -388,10 +388,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key3", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 120.76, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 30.942, "disk_sweep_cost": 0, - "cumulative_total_cost": 120.76, + "cumulative_total_cost": 30.942, "usable": true, "matching_rows_now": 2.6872, "intersect_covering_with_this_index": true, @@ -404,7 +404,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 2, - "cost": 120.76, + "cost": 30.942, "covering": true, "chosen": true }, @@ -422,7 +422,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 2, - "cost": 120.76, + "cost": 30.942, "covering": true, "clustered_pk_scan": false, "intersect_of": @@ -460,7 +460,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) ] }, "rows_for_plan": 2, - "cost_for_plan": 120.76, + "cost_for_plan": 30.942, "chosen": true } ] @@ -501,7 +501,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": true }, @@ -515,13 +515,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": false, "cause": "cost" } ], "index_to_merge": "key1", - "cumulated_cost": 487 + "cumulated_cost": 457.06 }, { @@ -538,7 +538,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": true }, @@ -552,16 +552,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": false, "cause": "cost" } ], "index_to_merge": "key3", - "cumulated_cost": 973.99 + "cumulated_cost": 914.12 } ], - "cost_of_reading_ranges": 973.99, + "cost_of_reading_ranges": 914.12, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": @@ -582,10 +582,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key1", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 40.252, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 10.314, "disk_sweep_cost": 1923.1, - "cumulative_total_cost": 1963.4, + "cumulative_total_cost": 1933.5, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -594,10 +594,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key2", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 80.504, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 20.628, "disk_sweep_cost": 84.518, - "cumulative_total_cost": 165.02, + "cumulative_total_cost": 105.15, "usable": true, "matching_rows_now": 77.636, "intersect_covering_with_this_index": false, @@ -610,7 +610,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "chosen": true } @@ -631,10 +631,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key3", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 40.252, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 10.314, "disk_sweep_cost": 1923.1, - "cumulative_total_cost": 1963.4, + "cumulative_total_cost": 1933.5, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -643,10 +643,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key4", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 80.504, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 20.628, "disk_sweep_cost": 84.518, - "cumulative_total_cost": 165.02, + "cumulative_total_cost": 105.15, "usable": true, "matching_rows_now": 77.636, "intersect_covering_with_this_index": false, @@ -659,13 +659,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "chosen": true } } ], - "index_roworder_union_cost": 314.73, + "index_roworder_union_cost": 194.98, "members": 2, "chosen": true } @@ -686,7 +686,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "clustered_pk_scan": false, "intersect_of": @@ -717,7 +717,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "clustered_pk_scan": false, "intersect_of": @@ -747,7 +747,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) ] }, "rows_for_plan": 154, - "cost_for_plan": 314.73, + "cost_for_plan": 194.98, "chosen": true } ] diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index fcbef8b5ff4..fd5780cc64c 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -199,7 +199,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "index": "key1", "used_range_estimates": true, "rows": 1, - "cost": 1.3251, + "cost": 1.1251, "chosen": true }, { @@ -211,13 +211,13 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 1.3251, + "cost": 1.1251, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 1, - "cost_for_plan": 1.5251, + "cost_for_plan": 1.3251, "estimated_join_cardinality": 1 } ] diff --git a/mysql-test/main/opt_trace_ucs2.result b/mysql-test/main/opt_trace_ucs2.result index 1a88396f9ab..0d920341bcd 100644 --- a/mysql-test/main/opt_trace_ucs2.result +++ b/mysql-test/main/opt_trace_ucs2.result @@ -38,7 +38,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2, - "cost": 2.5559, + "cost": 2.5477, "chosen": true } ], diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index a68e70e8a25..9752aa71bfb 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -568,18 +568,18 @@ explain extended select * from t1 where (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`_col_1`,`tvc_0`.`_col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`_col_1` and `test`.`t1`.`b` = `<subquery2>`.`_col_2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`))))) explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`2`))))) select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); a b @@ -590,10 +590,10 @@ explain extended select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`_col_1`,`tvc_0`.`_col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`_col_1` and `test`.`t1`.`b` = `<subquery2>`.`_col_2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`))))) select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); a b c @@ -606,10 +606,10 @@ explain extended select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`_col_1`,`tvc_0`.`_col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`_col_1` and `test`.`t2`.`c` = `<subquery2>`.`_col_2`)))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t2`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t2`.`c`) = `tvc_0`.`_col_2`))))) drop table t1, t2, t3; set @@in_predicate_conversion_threshold= default; # diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 76481a5042a..49aa1dd32a0 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -693,7 +693,7 @@ KEY StringField (FieldKey,StringVal(32)) INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using index condition; Using where +1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; FieldKey LongVal StringVal 1 0 2 @@ -3118,7 +3118,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 ref id_23_date,id_234_date id_23_date 2 const,const 8 Using where +1 SIMPLE t2 range id_23_date,id_234_date id_23_date 2 NULL 8 Using where drop table t1,t2; # # MDEV-8989: ORDER BY optimizer ignores equality propagation diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index dbd4b59eedf..e56fd46b55f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -281,7 +281,7 @@ INSERT INTO t1 VALUES (33,5),(33,5),(33,5),(33,5),(34,5),(35,5); EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range|filter a,b a|b 5|5 NULL 2 (41%) Using index condition; Using where; Using rowid filter +1 SIMPLE t1 ref|filter a,b b|a 5|5 const 15 (5%) Using where; Using rowid filter SELECT * FROM t1 WHERE a IN(1,2) AND b=5; a b DROP TABLE t1; @@ -327,8 +327,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index eee4ad579c2..ee92d612889 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -330,8 +330,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 769d89ddaba..6b2c9a2f0bc 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -691,11 +691,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation CountryPopulation 3 const 270 Using index condition +1 SIMPLE City ref Country,CountryPopulation Country 3 const 267 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation CountryPopulation 3 const 7 Using index condition +1 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') @@ -752,7 +752,7 @@ CREATE INDEX CountryName ON City(Country,Name); EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName CountryPopulation 3 const 270 Using index condition +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 267 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index cd286c96081..f69425fee8b 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -825,7 +825,7 @@ WHERE ((Population > 101000 AND Population < 110000) OR ID BETWEEN 3500 AND 3800) AND Country='FIN' AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryPopulation 3 const 7 Using index condition; Using where +1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 7 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR ID BETWEEN 3790 AND 3800) AND Country='USA' diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index a803f832935..5ad252bb42d 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -886,7 +886,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using rowid filter +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -915,7 +915,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -939,7 +939,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 71.00 100.00 100.00 Using index condition -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) 0.52 (7%) 8.48 100.00 Using where; Using rowid filter +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) 0.52 (7%) 8.48 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -975,7 +975,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1050,7 +1050,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -1079,7 +1079,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1095,7 +1095,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 71.00 100.00 100.00 Using index condition -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.70 8.48 7.77 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.70 8.48 7.77 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -1131,7 +1131,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1454,7 +1454,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1486,7 +1486,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1504,7 +1504,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.20 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1543,7 +1543,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1577,7 +1577,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1609,7 +1609,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1627,7 +1627,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.20 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1666,7 +1666,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1703,7 +1703,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1740,7 +1740,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1758,7 +1758,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.95 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1802,7 +1802,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1836,7 +1836,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1873,7 +1873,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1891,7 +1891,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.95 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1935,7 +1935,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 106b95d36b7..ac4fe9f1534 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2115,8 +2115,8 @@ union ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range|filter f1,f2 f1|f1 13|13 NULL 1 (2%) Using index condition; Using where; Using rowid filter -2 UNION t1 range|filter f1,f2 f1|f1 13|13 NULL 1 (2%) Using index condition; Using where; Using rowid filter +1 PRIMARY t1 ref|filter f1,f2 f1|f1 13|13 const 1 (2%) Using index condition; Using where; Using rowid filter +2 UNION t1 ref|filter f1,f2 f1|f1 13|13 const 1 (2%) Using index condition; Using where; Using rowid filter NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain format=json ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) @@ -2135,11 +2135,12 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["f1", "f2"], "key": "f1", "key_length": "13", "used_key_parts": ["f1"], + "ref": ["const"], "rowid_filter": { "range": { "key": "f1", @@ -2161,11 +2162,12 @@ EXPLAIN "operation": "UNION", "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["f1", "f2"], "key": "f1", "key_length": "13", "used_key_parts": ["f1"], + "ref": ["const"], "rowid_filter": { "range": { "key": "f1", @@ -2202,7 +2204,7 @@ id y x explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index -1 SIMPLE t2 index_merge x,y y,x 5,5 NULL 1 100.00 Using intersect(y,x); Using where; Using index +1 SIMPLE t2 ref x,y y 5 const 2 100.00 Using where Warnings: Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1 drop table t1, t2; @@ -2240,17 +2242,17 @@ count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range|filter b,a a|b 5|5 NULL 33 (6%) 5.90 Using index condition; Using where; Using rowid filter +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 3.30 Using where; Using rowid filter Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; a b 11 2 11 2 -11 2 -22 2 83 2 +11 2 83 2 +22 2 drop table t1; set optimizer_switch=@save_optimizer_switch; SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT; @@ -2508,7 +2510,7 @@ WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) ORDER BY timestamp DESC; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainTimestamp|ixEventWhoisDomainDomain 4|767 NULL 1 (29%) 28.57 Using where; Using rowid filter +1 SIMPLE t1 ref|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp 767|4 const 2 (14%) 14.29 Using index condition; Using where; Using filesort; Using rowid filter Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_street2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>(current_timestamp() + interval -1 month) order by `test`.`t1`.`timestamp` desc SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index dad981db201..f84d2eae05a 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -73,12 +73,12 @@ order by s_acctbal desc, n_name, s_name, p_partkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 0.31 Using where; Using temporary; Using filesort -1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where 1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 1 PRIMARY region eq_ref PRIMARY PRIMARY 4 dbt3_s001.nation.n_regionkey 1 20.00 Using where 2 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where -2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 +2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 2 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 2 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where Warnings: @@ -119,7 +119,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 2.08 Using where; Using join buffer (flat, BNL join) 1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where 2 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where -2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 +2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 2 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 2 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where Warnings: @@ -489,7 +489,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 100.00 Using where -2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where 4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 @@ -1661,7 +1661,7 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` # gives selectivity data explain extended select * from t1 where a in (17,51,5) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range|filter b,a a|b 5|5 NULL 29 (6%) 5.80 Using index condition; Using where; Using rowid filter +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 58 (3%) 2.90 Using where; Using rowid filter Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) drop table t1; diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result index 1f44f7bc5ed..69dc506527b 100644 --- a/mysql-test/main/show_explain.result +++ b/mysql-test/main/show_explain.result @@ -28,7 +28,7 @@ select count(*) from t1 where a < 100000; connection default; show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 999 Using where; Using index Warnings: Note 1003 select count(*) from t1 where a < 100000 connection con1; diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index a73ebed03e0..ba2e3ef268f 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -74,7 +74,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey @@ -181,7 +181,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey @@ -214,7 +214,7 @@ order by o_year; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort 1 SIMPLE part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 5 dbt3_s001.part.p_partkey 30 Using index condition +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 5 dbt3_s001.part.p_partkey 30 Using where 1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where 1 SIMPLE orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (27%) Using where; Using rowid filter 1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 @@ -337,7 +337,7 @@ and o_orderkey=l_orderkey and p_partkey=l_partkey; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE part range PRIMARY,i_p_retailprice i_p_retailprice 9 NULL 1 Using index condition 1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where select o_orderkey, p_partkey from part, lineitem, orders where p_retailprice > 1100 and o_orderdate='1997-01-01' diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 2975c8ba686..0b50def19c2 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -246,7 +246,7 @@ order by o_year; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort 1 SIMPLE part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 5 dbt3_s001.part.p_partkey 30 Using index condition +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 5 dbt3_s001.part.p_partkey 30 Using where 1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where 1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 1 SIMPLE orders eq_ref PRIMARY,i_o_orderdate,i_o_custkey PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 09e30fa9ce8..1ee4718ef10 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -3176,7 +3176,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7040,8 +7040,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index 5c4544a1b05..299faadeff7 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -96,10 +96,10 @@ explain extended select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 8 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where `test`.`t1`.`oref` = `test`.`t2`.`oref` having trigcond(`test`.`t1`.`a` is null))))) AS `Z` from `test`.`t2` +Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`oref` = `test`.`t2`.`oref` and trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t2` flush status; select oref, a from t2 where a in (select a from t1 where oref=t2.oref); oref a diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 4260676cc37..aebeb4e0cc1 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -99,10 +99,10 @@ explain extended select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 8 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where `test`.`t1`.`oref` = `test`.`t2`.`oref` having trigcond(`test`.`t1`.`a` is null))))) AS `Z` from `test`.`t2` +Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`oref` = `test`.`t2`.`oref` and trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t2` flush status; select oref, a from t2 where a in (select a from t1 where oref=t2.oref); oref a diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index d9048b2c040..5f38d2a5d35 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1889,8 +1889,8 @@ WHERE alias4.c = alias3.b ); 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 -3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 -3 MATERIALIZED alias4 index c c 11 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 Using where +3 MATERIALIZED alias4 ref c c 11 test.alias3.b 2 Using where; Using index DROP TABLE t1,t2; # # BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 35e52abee45..1436f98d9f8 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -3179,7 +3179,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7040,8 +7040,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index dbc52834063..8100c1da7e5 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -3181,7 +3181,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 39306df943a..689363c6d22 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -3177,7 +3177,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7031,8 +7031,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 595d1de9e1a..f6f468ba1ab 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -3182,7 +3182,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7046,8 +7046,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 288b28a2b6e..f9bbf2e00c4 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7031,8 +7031,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 7e5327712d5..b4ad66458dc 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -2768,8 +2768,8 @@ SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Start temporary -1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 Using where; End temporary +1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 +1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Using where; FirstMatch(t1_2) 3 DERIVED t1 ALL NULL NULL NULL NULL 11 SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); @@ -2777,10 +2777,10 @@ a b a b 3 1 9 1 5 8 4 0 3 9 9 1 -2 4 6 8 2 4 4 0 -2 6 6 8 +2 4 6 8 2 6 4 0 +2 6 6 8 5 4 4 0 7 7 7 7 5 4 4 0 diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 2477f3d73ca..2301a03f973 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -2779,8 +2779,8 @@ SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Start temporary -1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 Using where; End temporary +1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 +1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Using where; FirstMatch(t1_2) 3 DERIVED t1 ALL NULL NULL NULL NULL 11 SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); @@ -2788,10 +2788,10 @@ a b a b 3 1 9 1 5 8 4 0 3 9 9 1 -2 4 6 8 2 4 4 0 -2 6 6 8 +2 4 6 8 2 6 4 0 +2 6 6 8 5 4 4 0 7 7 7 7 5 4 4 0 diff --git a/mysql-test/main/type_enum.result b/mysql-test/main/type_enum.result index 02d71c6bd9f..ca909e1a2f2 100644 --- a/mysql-test/main/type_enum.result +++ b/mysql-test/main/type_enum.result @@ -1932,16 +1932,14 @@ ALTER TABLE t2 ADD PRIMARY KEY(c1); SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 -1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +1 SIMPLE t2 range PRIMARY PRIMARY 1 NULL 1 Using where; Using index DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY); INSERT INTO t1 VALUES ('a'); @@ -1959,16 +1957,14 @@ ALTER TABLE t2 ADD PRIMARY KEY(c1); SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 -1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +1 SIMPLE t2 range PRIMARY PRIMARY 1 NULL 1 Using where; Using index DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY); INSERT INTO t1 VALUES ('a'); @@ -1986,16 +1982,14 @@ ALTER TABLE t2 ADD PRIMARY KEY(c1); SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 -1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +1 SIMPLE t2 range PRIMARY PRIMARY 1 NULL 1 Using where; Using index DROP TABLE IF EXISTS t1,t2; # # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index f7f18c8cbae..88623028b54 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -1121,7 +1121,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t2 AS t1 WHERE b NOT IN (SELECT b FROM t1 FORCE INDEX(b)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY t1 index_subquery b b 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery b b 5 func 3 Using index; Full scan on NULL key DROP TABLE t1; DROP TABLE t2, t3; # diff --git a/mysql-test/suite/innodb/r/full_crc32_import.result b/mysql-test/suite/innodb/r/full_crc32_import.result index b8ebbb73706..06da5716aae 100644 --- a/mysql-test/suite/innodb/r/full_crc32_import.result +++ b/mysql-test/suite/innodb/r/full_crc32_import.result @@ -5,6 +5,7 @@ CREATE TABLE t1 b blob, c blob, KEY (b(200))) ENGINE=InnoDB ROW_FORMAT=COMPACT; +BEGIN; INSERT INTO t1 (b, c) values (repeat("ab", 200), repeat("bc", 200)); INSERT INTO t1 (b, c) values (repeat("bc", 200), repeat("cd", 200)); INSERT INTO t1 (b, c) values (repeat("cd", 200), repeat("ef", 200)); @@ -17,6 +18,7 @@ INSERT INTO t1 (b, c) values (repeat("ij", 200), repeat("kl", 200)); INSERT INTO t1 (b, c) values (repeat("jk", 200), repeat("lm", 200)); INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; +COMMIT; SELECT COUNT(*) FROM t1; COUNT(*) 40 @@ -85,6 +87,7 @@ ST_GeomFromText('LINESTRING(15 15,24 10,31 20)')); INSERT INTO t1(c2,c3) VALUES( ST_GeomFromText('POINT(60 50)'), ST_GeomFromText('LINESTRING(10 15,20 44,35 32)')); +BEGIN; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; @@ -96,6 +99,7 @@ INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +COMMIT; FLUSH TABLE t1 FOR EXPORT; # List before copying files db.opt diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 26aeb799d32..31db0622fe1 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2733,7 +2733,7 @@ KEY (c3) ) ENGINE=InnoDB; explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range c1,c3 c3 4 NULL 1 Using index condition; Using where +1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort DROP TABLE t1,t2; # # 36259: Optimizing with ORDER BY diff --git a/mysql-test/suite/innodb/t/full_crc32_import.test b/mysql-test/suite/innodb/t/full_crc32_import.test index 2c11a2423e6..aa4db3f9bf7 100644 --- a/mysql-test/suite/innodb/t/full_crc32_import.test +++ b/mysql-test/suite/innodb/t/full_crc32_import.test @@ -15,6 +15,7 @@ CREATE TABLE t1 c blob, KEY (b(200))) ENGINE=InnoDB ROW_FORMAT=COMPACT; +BEGIN; INSERT INTO t1 (b, c) values (repeat("ab", 200), repeat("bc", 200)); INSERT INTO t1 (b, c) values (repeat("bc", 200), repeat("cd", 200)); INSERT INTO t1 (b, c) values (repeat("cd", 200), repeat("ef", 200)); @@ -27,6 +28,7 @@ INSERT INTO t1 (b, c) values (repeat("ij", 200), repeat("kl", 200)); INSERT INTO t1 (b, c) values (repeat("jk", 200), repeat("lm", 200)); INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; +COMMIT; SELECT COUNT(*) FROM t1; FLUSH TABLE t1 FOR EXPORT; @@ -95,6 +97,7 @@ INSERT INTO t1(c2,c3) VALUES( ST_GeomFromText('POINT(60 50)'), ST_GeomFromText('LINESTRING(10 15,20 44,35 32)')); +BEGIN; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; @@ -106,6 +109,7 @@ INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +COMMIT; FLUSH TABLE t1 FOR EXPORT; --echo # List before copying files diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index f196840ad55..ad8b3b01dfe 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -677,16 +677,18 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c @@ -696,8 +698,8 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index 30003146da7..7194b9abcf6 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -138,14 +138,13 @@ where e.mgr = a.emp_id select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 100.00 Using where -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 -4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.emp.emp_id 2 100.00 FirstMatch(emp) 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where 3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where 3 RECURSIVE UNION <derived2> ref key0 key0 5 test.e.mgr 2 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' +Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `ancestors`.`emp_id` = `test`.`emp`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' with recursive ancestors as diff --git a/sql/filesort_utils.cc b/sql/filesort_utils.cc index 5bd552f76be..27f37d1d507 100644 --- a/sql/filesort_utils.cc +++ b/sql/filesort_utils.cc @@ -52,9 +52,9 @@ double get_merge_many_buffs_cost_fast(ha_rows num_rows, // Calculate CPU cost of sorting buffers. total_cost= - ( num_buffers * num_keys_per_buffer * log(1.0 + num_keys_per_buffer) + - last_n_elems * log(1.0 + last_n_elems) ) - / TIME_FOR_COMPARE_ROWID; + ((num_buffers * num_keys_per_buffer * log(1.0 + num_keys_per_buffer) + + last_n_elems * log(1.0 + last_n_elems)) / + TIME_FOR_COMPARE_ROWID); // Simulate behavior of merge_many_buff(). while (num_buffers >= MERGEBUFF2) diff --git a/sql/handler.cc b/sql/handler.cc index 0d2603ac4aa..c44e61ade87 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -2819,7 +2819,7 @@ double handler::keyread_time(uint index, uint ranges, ha_rows rows) if (ranges) { uint keys_per_block= (uint) (stats.block_size*3/4/len+1); - ulonglong blocks= !rows ? 0 : (rows-1) / keys_per_block + 1; + ulonglong blocks= (rows+ keys_per_block- 1)/keys_per_block; cost+= blocks; } return cost; @@ -2972,6 +2972,8 @@ int handler::ha_close(void) /* Detach from ANALYZE tracker */ tracker= NULL; + /* We use ref as way to check that open succeded */ + ref= 0; DBUG_ASSERT(m_lock_type == F_UNLCK); DBUG_ASSERT(inited == NONE); diff --git a/sql/handler.h b/sql/handler.h index edd8cc99f66..c3de2cb4752 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -3258,6 +3258,11 @@ public: DBUG_ASSERT(m_lock_type == F_UNLCK); DBUG_ASSERT(inited == NONE); } + /* To check if table has been properely opened */ + bool is_open() + { + return ref != 0; + } virtual handler *clone(const char *name, MEM_ROOT *mem_root); bool clone_handler_for_update(); void delete_update_handler(); @@ -3461,7 +3466,10 @@ public: reset_statistics(); } virtual double scan_time() - { return ulonglong2double(stats.data_file_length) / IO_SIZE + 2; } + { + return ((ulonglong2double(stats.data_file_length) / stats.block_size + 2) * + avg_io_cost()); + } virtual double key_scan_time(uint index) { @@ -4918,6 +4926,7 @@ public: ha_share= arg_ha_share; return false; } + void set_table(TABLE* table_arg) { table= table_arg; } int get_lock_type() const { return m_lock_type; } public: /* XXX to be removed, see ha_partition::partition_ht() */ diff --git a/sql/item_func.cc b/sql/item_func.cc index b75bae596e8..ead72007518 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -5901,7 +5901,7 @@ bool Item_func_match::init_search(THD *thd, bool no_order) { DBUG_ENTER("Item_func_match::init_search"); - if (!table->file->get_table()) // the handler isn't opened yet + if (!table->file->is_open()) DBUG_RETURN(0); /* Check if init_search() has been called before */ diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index 8d60b565d77..a2d15ff424f 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -112,7 +112,7 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, if (table->file->is_clustering_key(keyno)) len= table->s->stored_rec_length; /* Assume block is 75 % full */ - uint avg_block_records= ((uint) (table->file->stats.block_size*3/4))/len + 1; + uint avg_block_records= ((uint) (stats.block_size*3/4))/len + 1; uint limit= thd->variables.eq_range_index_dive_limit; bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, seq_init_param, @@ -218,7 +218,7 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, */ prev_range_last_block_records+= rows; DBUG_ASSERT(prev_range_last_block_records < - table->file->stats.block_size); + stats.block_size); } else { @@ -323,7 +323,7 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, uint limited_ranges= (uint) MY_MIN((ulonglong) n_ranges, io_blocks); cost->cpu_cost= read_time(keyno, limited_ranges, total_rows); } - cost->cpu_cost+= ((double) total_rows / TIME_FOR_COMPARE + + cost->cpu_cost+= (rows2double(total_rows) / TIME_FOR_COMPARE + MULTI_RANGE_READ_SETUP_COST); } DBUG_PRINT("statistics", @@ -403,7 +403,7 @@ ha_rows handler::multi_range_read_info(uint keyno, uint n_ranges, uint n_rows, { cost->cpu_cost= read_time(keyno, n_ranges, (uint)n_rows); } - cost->cpu_cost+= (double) n_rows / TIME_FOR_COMPARE; + cost->cpu_cost+= rows2double(n_rows) / TIME_FOR_COMPARE; return 0; } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e5d813dc301..7390345ed29 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2671,7 +2671,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, scan_time= read_time= DBL_MAX; else { - scan_time= ((double) records) / TIME_FOR_COMPARE; + scan_time= rows2double(records) / TIME_FOR_COMPARE; /* The 2 is there to prefer range scans to full table scans. This is mainly to make the test suite happy as many tests has @@ -2822,7 +2822,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, { int key_for_use= find_shortest_key(head, &head->covering_keys); double key_read_time= (head->file->key_scan_time(key_for_use) + - (double) records / TIME_FOR_COMPARE); + rows2double(records) / TIME_FOR_COMPARE); DBUG_PRINT("info", ("'all'+'using index' scan will be using key %d, " "read time %g", key_for_use, key_read_time)); @@ -5164,8 +5164,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, Add one ROWID comparison for each row retrieved on non-CPK scan. (it is done in QUICK_RANGE_SELECT::row_in_ranges) */ - double rid_comp_cost= static_cast<double>(non_cpk_scan_records) / - TIME_FOR_COMPARE_ROWID; + double rid_comp_cost= (rows2double(non_cpk_scan_records) / + TIME_FOR_COMPARE_ROWID); imerge_cost+= rid_comp_cost; trace_best_disjunct.add("cost_of_mapping_rowid_in_non_clustered_pk_scan", rid_comp_cost); @@ -5471,7 +5471,7 @@ typedef struct st_common_index_intersect_info { PARAM *param; /* context info for range optimizations */ uint key_size; /* size of a ROWID element stored in Unique object */ - uint compare_factor; /* 1/compare - cost to compare two ROWIDs */ + double compare_factor; /* 1/compare - cost to compare two ROWIDs */ size_t max_memory_size; /* maximum space allowed for Unique objects */ ha_rows table_cardinality; /* estimate of the number of records in table */ double cutoff_cost; /* discard index intersects with greater costs */ @@ -6166,7 +6166,7 @@ bool check_index_intersect_extension(PARTIAL_INDEX_INTERSECT_INFO *curr, { uint *buff_elems= common_info->buff_elems; uint key_size= common_info->key_size; - uint compare_factor= common_info->compare_factor; + double compare_factor= common_info->compare_factor; size_t max_memory_size= common_info->max_memory_size; records_sent_to_unique+= ext_index_scan_records; @@ -14305,20 +14305,21 @@ void cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, b-tree the number of comparisons will be larger. TODO: This cost should be provided by the storage engine. */ - const double tree_traversal_cost= + const double tree_traversal_cost= ceil(log(static_cast<double>(table_records))/ log(static_cast<double>(keys_per_block))) * - 1/double(2*TIME_FOR_COMPARE); + 1/(2*TIME_FOR_COMPARE); const double cpu_cost= num_groups * - (tree_traversal_cost + 1/double(TIME_FOR_COMPARE_IDX)); + (tree_traversal_cost + 1/TIME_FOR_COMPARE_IDX); *read_cost= io_cost + cpu_cost; *records= num_groups; DBUG_PRINT("info", - ("table rows: %lu keys/block: %u keys/group: %lu result rows: %lu blocks: %lu", - (ulong)table_records, keys_per_block, (ulong) keys_per_group, + ("table rows: %lu keys/block: %u keys/group: %lu " + "result rows: %lu blocks: %lu", + (ulong) table_records, keys_per_block, (ulong) keys_per_group, (ulong) *records, (ulong) num_blocks)); DBUG_VOID_RETURN; } diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 607d9a2d95a..38779619b82 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1420,9 +1420,10 @@ void get_delayed_table_estimates(TABLE *table, /* Calculate cost of scanning the temptable */ double data_size= COST_MULT(item->jtbm_record_count, hash_sj_engine->tmp_table->s->reclength); - /* Do like in handler::read_time */ - *scan_time= data_size/IO_SIZE + 2; -} + /* Do like in handler::scan_time() */ + *scan_time= ((data_size/table->file->stats.block_size+2) * + table->file->avg_io_cost()); +} /** @@ -3144,7 +3145,7 @@ bool Sj_materialization_picker::check_qep(JOIN *join, prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read); prefix_cost= COST_ADD(prefix_cost, curpos.read_time); prefix_cost= COST_ADD(prefix_cost, - prefix_rec_count / (double) TIME_FOR_COMPARE); + prefix_rec_count / TIME_FOR_COMPARE); //TODO: take into account join condition selectivity here } diff --git a/sql/sql_const.h b/sql/sql_const.h index c605f3d0bae..3f053a1606d 100644 --- a/sql/sql_const.h +++ b/sql/sql_const.h @@ -204,8 +204,8 @@ instead of reading with keys. The number says how many evaluation of the WHERE clause is comparable to reading one extra row from a table. */ -#define TIME_FOR_COMPARE 5 // 5 compares == one read -#define TIME_FOR_COMPARE_IDX 20 +#define TIME_FOR_COMPARE 5.0 // 5 WHERE compares == one read +#define TIME_FOR_COMPARE_IDX 20.0 #define IDX_BLOCK_COPY_COST ((double) 1 / TIME_FOR_COMPARE) #define IDX_LOOKUP_COST ((double) 1 / 8) diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index d5120d95420..e546f3cfeca 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -4255,7 +4255,7 @@ void select_insert::abort_result_set() table will be assigned with view table structure, but that table will not be opened really (it is dummy to check fields types & Co). */ - if (table && table->file->get_table()) + if (table && table->file->is_open()) { bool changed, transactional_table; /* diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 750883581b4..f7660054b74 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -232,7 +232,7 @@ static bool test_if_cheaper_ordering(const JOIN_TAB *tab, uint *saved_best_key_parts= NULL); static int test_if_order_by_key(JOIN *join, ORDER *order, TABLE *table, uint idx, - uint *used_key_parts= NULL); + uint *used_key_parts); static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order, ha_rows select_limit, bool no_changes, const key_map *map); @@ -7196,7 +7196,7 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, { TABLE *table= s->table; double sel= table->cond_selectivity; - double table_records= (double) s->records; + double table_records= rows2double(s->records); dbl_records= table_records * sel; return dbl_records; } @@ -7227,6 +7227,61 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, } +/* + Calculate the cost of reading a set of rows trough an index + + Logically this is identical to the code in multi_range_read_info_const() + excepts the function also takes into account io_blocks and multiple + ranges. + + One main difference between the functions is that + multi_range_read_info_const() adds a very small cost per range + (IDX_LOOKUP_COST) and also MULTI_RANGE_READ_SETUP_COST, to ensure that + 'ref' is preferred slightly over ranges. +*/ + +double cost_for_index_read(const THD *thd, const TABLE *table, uint key, + ha_rows records, ha_rows worst_seeks) +{ + DBUG_ENTER("cost_for_index_read"); + double cost; + handler *file= table->file; + + set_if_smaller(records, (ha_rows) thd->variables.max_seeks_for_key); + if (file->is_clustering_key(key)) + cost= file->read_time(key, 1, records); + else + if (table->covering_keys.is_set(key)) + cost= file->keyread_time(key, 1, records); + else + cost= ((file->keyread_time(key, 0, records) + + file->read_time(key, 1, MY_MIN(records, worst_seeks)))); + + DBUG_PRINT("statistics", ("cost: %.3f", cost)); + DBUG_RETURN(cost); +} + + +/* + Adjust cost from table->quick_costs calculated by + multi_range_read_info_const() to be comparable with cost_for_index_read() + + This functions is needed because best_access_patch doesn't add + TIME_FOR_COMPARE to it's costs until very late. + Preferably we should fix so that all costs are comparably. + (All compared costs should include TIME_FOR_COMPARE for all found + rows). +*/ + +double adjust_quick_cost(double quick_cost, ha_rows records) +{ + double cost= (quick_cost - MULTI_RANGE_READ_SETUP_COST - + rows2double(records)/TIME_FOR_COMPARE); + DBUG_ASSERT(cost > 0.0); + return cost; +} + + /** Find the best access path for an extension of a partial execution plan and add this path to the plan. @@ -7448,12 +7503,13 @@ best_access_path(JOIN *join, (!(key_flags & HA_NULL_PART_KEY) || // (2) all_key_parts == notnull_part)) // (3) { + + /* TODO: Adjust cost for covering and clustering key */ type= JT_EQ_REF; trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); if (!found_ref && table->quick_keys.is_set(key)) - tmp= ((double) table->quick_costs[key] - - MULTI_RANGE_READ_SETUP_COST); + tmp= adjust_quick_cost(table->quick_costs[key], 1); else tmp= table->file->avg_io_cost(); tmp*= prev_record_reads(join_positions, idx, found_ref); @@ -7487,12 +7543,8 @@ best_access_path(JOIN *join, { records= (double) table->quick_rows[key]; trace_access_idx.add("used_range_estimates", true); - /* - Use calculated cost, but ensure we prefer ref before - range - */ - tmp= ((double) table->quick_costs[key] - - MULTI_RANGE_READ_SETUP_COST); + tmp= adjust_quick_cost(table->quick_costs[key], + table->quick_rows[key]); goto got_cost; } else @@ -7527,12 +7579,11 @@ best_access_path(JOIN *join, */ if (table->quick_keys.is_set(key) && (const_part & - (((key_part_map)1 << table->quick_key_parts[key])-1)) == + (((key_part_map)1 << table->quick_key_parts[key])-1)) == (((key_part_map)1 << table->quick_key_parts[key])-1) && table->quick_n_ranges[key] == 1 && records > (double) table->quick_rows[key]) { - records= (double) table->quick_rows[key]; trace_access_idx.add("used_range_estimates", true); } @@ -7552,13 +7603,8 @@ best_access_path(JOIN *join, } } /* Limit the number of matched rows */ - tmp= records; - set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); - if (table->covering_keys.is_set(key)) - tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); - else - tmp= table->file->read_time(key, 1, - (ha_rows) MY_MIN(tmp,s->worst_seeks)); + tmp= cost_for_index_read(thd, table, key, (ha_rows) records, + (ha_rows) s->worst_seeks); got_cost: tmp= COST_MULT(tmp, record_count); } @@ -7621,8 +7667,11 @@ best_access_path(JOIN *join, table->quick_key_parts[key] == max_key_part && //(C2) table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part)) //(C3) { - tmp= records= (double) table->quick_rows[key]; + records= (double) table->quick_rows[key]; + tmp= adjust_quick_cost(table->quick_costs[key], + table->quick_rows[key]); trace_access_idx.add("used_range_estimates", true); + goto got_cost2; } else { @@ -7645,24 +7694,23 @@ best_access_path(JOIN *join, cheaper in some cases ? TODO: figure this out and adjust the plan choice if needed. */ - if (!found_ref && table->quick_keys.is_set(key) && // (1) - table->quick_key_parts[key] > max_key_part && // (2) - records < (double)table->quick_rows[key]) // (3) - { - trace_access_idx.add("used_range_estimates", true); - records= (double)table->quick_rows[key]; - } - else + if (table->quick_keys.is_set(key)) { - if (table->quick_keys.is_set(key) && - table->quick_key_parts[key] < max_key_part) + if (table->quick_key_parts[key] >= max_key_part) // (2) { - trace_access_idx.add("chosen", false); - cause= "range uses more keyparts"; + if (!found_ref && // (1) + records < (double) table->quick_rows[key]) // (3) + { + trace_access_idx.add("used_range_estimates", true); + records= (double) table->quick_rows[key]; + } + } + else /* (table->quick_key_parts[key] < max_key_part) */ + { + trace_access_idx.add("chosen", true); + cause= "range uses less keyparts"; } } - - tmp= records; } else { @@ -7686,27 +7734,25 @@ best_access_path(JOIN *join, rec_per_key=(double) s->records/rec+1; if (!s->records) - tmp = 0; + records= 0; else if (rec_per_key/(double) s->records >= 0.01) - tmp = rec_per_key; + records= rec_per_key; else { double a=s->records*0.01; if (keyinfo->user_defined_key_parts > 1) - tmp= (max_key_part * (rec_per_key - a) + + records= (max_key_part * (rec_per_key - a) + a*keyinfo->user_defined_key_parts - rec_per_key)/ (keyinfo->user_defined_key_parts-1); else - tmp= a; - set_if_bigger(tmp,1.0); + records= a; + set_if_bigger(records, 1.0); } - records = (ulong) tmp; } if (ref_or_null_part) { - /* We need to do two key searches to find key */ - tmp *= 2.0; + /* We need to do two key searches to find row */ records *= 2.0; } @@ -7725,22 +7771,21 @@ best_access_path(JOIN *join, if (table->quick_keys.is_set(key) && table->quick_key_parts[key] <= max_key_part && const_part & - ((key_part_map)1 << table->quick_key_parts[key]) && + ((key_part_map)1 << table->quick_key_parts[key]) && table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part & const_part) && records > (double) table->quick_rows[key]) { - tmp= records= (double) table->quick_rows[key]; + records= (double) table->quick_rows[key]; } } /* Limit the number of matched rows */ + tmp= records; set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); - if (table->covering_keys.is_set(key)) - tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); - else - tmp= table->file->read_time(key, 1, - (ha_rows) MY_MIN(tmp,s->worst_seeks)); + tmp= cost_for_index_read(thd, table, key, (ha_rows) tmp, + (ha_rows) s->worst_seeks); + got_cost2: tmp= COST_MULT(tmp, record_count); } else @@ -7773,10 +7818,10 @@ best_access_path(JOIN *join, } trace_access_idx.add("rows", records).add("cost", tmp); - if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE) + if (tmp + 0.0001 < best_time - records/TIME_FOR_COMPARE) { trace_access_idx.add("chosen", true); - best_time= COST_ADD(tmp, records/(double) TIME_FOR_COMPARE); + best_time= COST_ADD(tmp, records/TIME_FOR_COMPARE); best= tmp; best_records= records; best_key= start_key; @@ -7819,7 +7864,7 @@ best_access_path(JOIN *join, use_cond_selectivity); tmp= s->quick ? s->quick->read_time : s->scan_time(); - double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_ADD(tmp, cmp_time); /* We read the table as many times as join buffer becomes full. */ @@ -7910,7 +7955,7 @@ best_access_path(JOIN *join, access (see first else-branch below), but we don't take it into account here for range/index_merge access. Find out why this is so. */ - double cmp_time= (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->found_records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_MULT(record_count, COST_ADD(s->quick->read_time, cmp_time)); @@ -7957,7 +8002,7 @@ best_access_path(JOIN *join, - read the whole table record - skip rows which does not satisfy join condition */ - double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_MULT(record_count, COST_ADD(tmp,cmp_time)); } else @@ -7973,7 +8018,7 @@ best_access_path(JOIN *join, we read the table (see flush_cached_records for details). Here we take into account cost to read and skip these records. */ - double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_ADD(tmp, cmp_time); } } @@ -8000,10 +8045,10 @@ best_access_path(JOIN *join, trace_access_scan.add("cost", tmp); if (best == DBL_MAX || - COST_ADD(tmp, record_count/(double) TIME_FOR_COMPARE*rnd_records) < + COST_ADD(tmp, record_count/TIME_FOR_COMPARE*rnd_records) < (best_key->is_for_hash_join() ? best_time : COST_ADD(best - best_filter_cmp_gain, - record_count/(double) TIME_FOR_COMPARE*records))) + record_count/TIME_FOR_COMPARE*records))) { /* If the table has a range (s->quick is set) make_join_select() @@ -8555,7 +8600,7 @@ optimize_straight_join(JOIN *join, table_map join_tables) : 0; read_time+= COST_ADD(read_time - filter_cmp_gain, COST_ADD(position->read_time, - record_count / (double) TIME_FOR_COMPARE)); + record_count / TIME_FOR_COMPARE)); advance_sj_state(join, join_tables, idx, &record_count, &read_time, &loose_scan_pos); @@ -8748,7 +8793,7 @@ greedy_search(JOIN *join, record_count= COST_MULT(record_count, join->positions[idx].records_read); read_time= COST_ADD(read_time, COST_ADD(join->positions[idx].read_time, - record_count / (double) TIME_FOR_COMPARE)); + record_count / TIME_FOR_COMPARE)); remaining_tables&= ~(best_table->table->map); --size_remain; @@ -8858,7 +8903,7 @@ void JOIN::get_partial_cost_and_fanout(int end_tab_idx, record_count= COST_MULT(record_count, tab->records_read); read_time= COST_ADD(read_time, COST_ADD(tab->read_time, - record_count / (double) TIME_FOR_COMPARE)); + record_count / TIME_FOR_COMPARE)); if (tab->emb_sj_nest) sj_inner_fanout= COST_MULT(sj_inner_fanout, tab->records_read); } @@ -9497,7 +9542,7 @@ best_extension_by_limited_search(JOIN *join, COST_ADD(position->read_time - filter_cmp_gain, current_record_count / - (double) TIME_FOR_COMPARE)); + TIME_FOR_COMPARE)); if (unlikely(thd->trace_started())) { @@ -16902,7 +16947,7 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, reopt_remaining_tables &= ~rs->table->map; rec_count= COST_MULT(rec_count, pos.records_read); cost= COST_ADD(cost, pos.read_time); - cost= COST_ADD(cost, rec_count / (double) TIME_FOR_COMPARE); + cost= COST_ADD(cost, rec_count / TIME_FOR_COMPARE); //TODO: take into account join condition selectivity here double pushdown_cond_selectivity= 1.0; table_map real_table_bit= rs->table->map; @@ -18619,6 +18664,7 @@ bool Create_tmp_table::finalize(THD *thd, delete table->file; goto err; } + table->file->set_table(table); if (!m_using_unique_constraint) share->reclength+= m_group_null_items; // null flag is stored separately @@ -18643,6 +18689,7 @@ bool Create_tmp_table::finalize(THD *thd, share->reclength+= whole_null_pack_length; if (!share->reclength) share->reclength= 1; // Dummy select + share->stored_rec_length= share->reclength; /* Use packed rows if there is blobs or a lot of space to gain */ if (share->blob_fields || (string_total_length() >= STRING_TOTAL_LENGTH_TO_PACK_ROWS && @@ -22877,8 +22924,7 @@ static int test_if_order_by_key(JOIN *join, } ok: - if (used_key_parts != NULL) - *used_key_parts= key_parts; + *used_key_parts= key_parts; DBUG_RETURN(reverse); } @@ -22972,12 +23018,13 @@ test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts, */ for (nr= 0 ; nr < table->s->keys ; nr++) { + uint not_used; if (usable_keys->is_set(nr) && table->key_info[nr].key_length < min_length && table->key_info[nr].user_defined_key_parts >= ref_key_parts && is_subkey(table->key_info[nr].key_part, ref_key_part, ref_key_part_end) && - test_if_order_by_key(NULL, order, table, nr)) + test_if_order_by_key(NULL, order, table, nr, ¬_used)) { min_length= table->key_info[nr].key_length; best= nr; @@ -28048,14 +28095,9 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab, if (ref_rows > 0) { - double tmp= (double)ref_rows; - /* Reuse the cost formula from best_access_path: */ - set_if_smaller(tmp, (double) tab->join->thd->variables.max_seeks_for_key); - if (table->covering_keys.is_set(keynr)) - tmp= table->file->keyread_time(keynr, 1, (ha_rows) tmp); - else - tmp= table->file->read_time(keynr, 1, - (ha_rows) MY_MIN(tmp,tab->worst_seeks)); + double tmp= cost_for_index_read(tab->join->thd, table, keynr, + ref_rows, + (ha_rows) tab->worst_seeks); if (tmp < best_cost) { best_cost= tmp; diff --git a/sql/table.cc b/sql/table.cc index 811ea28b49c..6d3adc4d67c 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9185,8 +9185,8 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock) /* we call it only when table is opened and it is "leaf" table*/ DBUG_ASSERT(table); lock_type= lock; - /* table->file->get_table() can be 0 for derived tables */ - if (table->file && table->file->get_table()) + /* If not derived tables */ + if (table->file && table->file->is_open()) table->file->set_lock_type(lock); if (is_merged_derived()) { diff --git a/sql/uniques.cc b/sql/uniques.cc index 4e327a4cc09..60918ea534e 100644 --- a/sql/uniques.cc +++ b/sql/uniques.cc @@ -161,7 +161,7 @@ inline double log2_n_fact(double x) static double get_merge_buffers_cost(uint *buff_elems, uint elem_size, uint *first, uint *last, - uint compare_factor) + double compare_factor) { uint total_buf_elems= 0; for (uint *pbuf= first; pbuf <= last; pbuf++) @@ -206,7 +206,7 @@ static double get_merge_buffers_cost(uint *buff_elems, uint elem_size, static double get_merge_many_buffs_cost(uint *buffer, uint maxbuffer, uint max_n_elems, uint last_n_elems, int elem_size, - uint compare_factor) + double compare_factor) { int i; double total_cost= 0.0; @@ -306,7 +306,7 @@ static double get_merge_many_buffs_cost(uint *buffer, double Unique::get_use_cost(uint *buffer, size_t nkeys, uint key_size, size_t max_in_memory_size, - uint compare_factor, + double compare_factor, bool intersect_fl, bool *in_memory) { size_t max_elements_in_tree; diff --git a/sql/uniques.h b/sql/uniques.h index 663d5c1682a..a78e626907f 100644 --- a/sql/uniques.h +++ b/sql/uniques.h @@ -72,13 +72,14 @@ public: bool get(TABLE *table); /* Cost of searching for an element in the tree */ - inline static double get_search_cost(ulonglong tree_elems, uint compare_factor) + inline static double get_search_cost(ulonglong tree_elems, + double compare_factor) { return log((double) tree_elems) / (compare_factor * M_LN2); } static double get_use_cost(uint *buffer, size_t nkeys, uint key_size, - size_t max_in_memory_size, uint compare_factor, + size_t max_in_memory_size, double compare_factor, bool intersect_fl, bool *in_memory); inline static int get_cost_calc_buff_size(size_t nkeys, uint key_size, size_t max_in_memory_size) diff --git a/storage/heap/ha_heap.h b/storage/heap/ha_heap.h index 319775d7222..50d3c0afb6c 100644 --- a/storage/heap/ha_heap.h +++ b/storage/heap/ha_heap.h @@ -65,9 +65,9 @@ public: double scan_time() { return (double) (stats.records+stats.deleted) / 20.0+10; } double read_time(uint index, uint ranges, ha_rows rows) - { return (double) rows / 20.0+1; } + { return (double) (rows +1)/ 20.0; } double keyread_time(uint index, uint ranges, ha_rows rows) - { return (double) rows / 20.0+1; } + { return (double) (rows + ranges) / 20.0 ; } double avg_io_cost() { return 0.05; } /* 1/20 */ int open(const char *name, int mode, uint test_if_locked); diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index e6137fac482..6519a2301e1 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -2089,7 +2089,8 @@ int ha_myisam::info(uint flag) ref_length= misam_info.reflength; share->db_options_in_use= misam_info.options; - stats.block_size= myisam_block_size; /* record block size */ + /* record block size. We adjust with IO_SIZE to not make it too small */ + stats.block_size= MY_MAX(myisam_block_size, IO_SIZE); if (table_share->tmp_table == NO_TMP_TABLE) mysql_mutex_lock(&table_share->LOCK_share); diff --git a/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result b/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result index efc6ccde500..4f285134c00 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result @@ -34,6 +34,7 @@ Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 FLUSH STATUS; +SET GLOBAL rocksdb_force_flush_memtable_and_lzero_now=1; SELECT * FROM t1 WHERE b=6; id a b 6 NULL 6 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result b/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result index ce3d7d9147e..feeabd7ee79 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result @@ -67,7 +67,7 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a,b from t1 where a = 700; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref ka ka 5 const 15000 100.00 +1 SIMPLE t1 ALL ka NULL NULL NULL 20000 75.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a from t1 where a in (700, 800); diff --git a/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test b/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test index 7b1652c759b..22b5d69780d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test @@ -28,6 +28,7 @@ SELECT * FROM t1 WHERE id=8; SHOW SESSION STATUS LIKE 'Handler_read%'; FLUSH STATUS; +SET GLOBAL rocksdb_force_flush_memtable_and_lzero_now=1; SELECT * FROM t1 WHERE b=6; SHOW SESSION STATUS LIKE 'Handler_read%'; diff --git a/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result index e646bc9bf38..194a6b31cba 100644 --- a/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result @@ -87,7 +87,7 @@ a b c a connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t0 left join `auto_test_remote`.`ta_r_auto_inc` t1 on ((t1.`a` = t0.`a`) and (t0.`a` is not null)) left join `auto_test_remote`.`ta_r_3` t2 on (t2.`c` = t1.`c`) left join `auto_test_remote`.`ta_r` t3 on ((t3.`b` = t2.`b`) and (t2.`b` is not null)) where 1 order by t0.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t0 left join `auto_test_remote`.`ta_r_auto_inc` t1 on (t1.`a` = t0.`a`) left join `auto_test_remote`.`ta_r_3` t2 on (t2.`c` = t1.`c`) left join `auto_test_remote`.`ta_r` t3 on (t3.`b` = t2.`b`) where 1 order by t0.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result index f3c6e189444..e6720c1113f 100644 --- a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL NULL NULL 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null) and (t3.`b` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result index a05781cb6d6..a0b44c95cee 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result @@ -87,7 +87,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join `auto_test_remote`.`ta_r_auto_inc` t2 on ((t2.`b` = t3.`b`) and (t3.`b` is not null)) left join `auto_test_remote`.`ta_r_3` t1 on (t1.`c` = t2.`c`) left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join `auto_test_remote`.`ta_r_auto_inc` t2 on (t2.`b` = t3.`b`) left join `auto_test_remote`.`ta_r_3` t1 on (t1.`c` = t2.`c`) left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result index 48cd9c2c75f..0ee74cae7a3 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t3.`b` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') |