diff --git a/mysql-test/suite/parts/r/partition_debug.result b/mysql-test/suite/parts/r/partition_debug.result index 6b1ef9e..27a49e3 100644 --- a/mysql-test/suite/parts/r/partition_debug.result +++ b/mysql-test/suite/parts/r/partition_debug.result @@ -1946,3 +1946,36 @@ a b 4 Original from partition p0 DROP TABLE t2; SET SESSION debug="-d,exchange_partition_fail_9"; +# +# Aone #33292338 Suboptimal plan due to null partition catch +# +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL AUTO_INCREMENT, +memo varchar(1024) NOT NULL DEFAULT '', +sid char(32) NOT NULL DEFAULT '', +create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, +PRIMARY KEY (id,create_time), +KEY idx_create_time (create_time), +KEY idx_sid_create_time (sid, create_time) +) +PARTITION BY RANGE (to_days(`create_time`)) +(PARTITION p202011 VALUES LESS THAN (738125) ENGINE = InnoDB, +PARTITION p202012 VALUES LESS THAN (738156) ENGINE = InnoDB, +PARTITION p202101 VALUES LESS THAN (738187) ENGINE = InnoDB, +PARTITION p202102 VALUES LESS THAN (738215) ENGINE = InnoDB, +PARTITION p202103 VALUES LESS THAN (738246) ENGINE = InnoDB, +PARTITION p202104 VALUES LESS THAN (738276) ENGINE = InnoDB, +PARTITION p202105 VALUES LESS THAN (738307) ENGINE = InnoDB); +# Prepare data +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +explain partitions select * from t1 where sid = '123456789012345678' and create_time >= '2021-03-01 00:00:07.861'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p202011,p202103,p202104,p202105 ref idx_create_time,idx_sid_create_time idx_sid_create_time 32 const 13 Using where +set debug="+d,preserve_null_partition_catch"; +explain partitions select * from t1 where sid = '123456789012345678' and create_time >= '2021-03-01 00:00:07.861'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p202011,p202103,p202104,p202105 range idx_create_time,idx_sid_create_time idx_create_time 5 NULL 1 Using where +set debug="-d,preserve_null_partition_catch"; +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/partition_debug.test b/mysql-test/suite/parts/t/partition_debug.test index c4583bf..666092c 100644 --- a/mysql-test/suite/parts/t/partition_debug.test +++ b/mysql-test/suite/parts/t/partition_debug.test @@ -49,3 +49,68 @@ let $crash_statement= ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; --source suite/parts/inc/partition_crash_exchange.inc let $fail_statement= $crash_statement; --source suite/parts/inc/partition_fail_exchange.inc + +--echo # +--echo # Aone #33292338 Suboptimal plan due to null partition catch +--echo # +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + memo varchar(1024) NOT NULL DEFAULT '', + sid char(32) NOT NULL DEFAULT '', + create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (id,create_time), + KEY idx_create_time (create_time), + KEY idx_sid_create_time (sid, create_time) +) +PARTITION BY RANGE (to_days(`create_time`)) +(PARTITION p202011 VALUES LESS THAN (738125) ENGINE = InnoDB, + PARTITION p202012 VALUES LESS THAN (738156) ENGINE = InnoDB, + PARTITION p202101 VALUES LESS THAN (738187) ENGINE = InnoDB, + PARTITION p202102 VALUES LESS THAN (738215) ENGINE = InnoDB, + PARTITION p202103 VALUES LESS THAN (738246) ENGINE = InnoDB, + PARTITION p202104 VALUES LESS THAN (738276) ENGINE = InnoDB, + PARTITION p202105 VALUES LESS THAN (738307) ENGINE = InnoDB); + +--echo # Prepare data +--disable_query_log +INSERT INTO t1 VALUES +(1, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(2, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(3, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(4, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(5, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(6, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(7, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(8, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(9, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(10, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(11, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(12, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(13, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(14, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(15, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(16, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(17, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(18, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(19, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(20, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(21, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(22, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(23, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(24, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(25, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(26, "abcabcabc0000000000", "123456789012345678", "2020-11-01 00:00:00"), +(100, "abcabcabc4444444444", "123456789012345678", "2021-03-07 22:46:20"), +(101, "abcabcabc3333333333", "123456789012345678", "2021-03-07 23:14:59"), +(102, "abcabcabc2222222222", "123456789012345678", "2021-03-08 00:23:10"), +(103, "abcabcabc1111111111", "123456789012345678", "2021-03-08 10:08:01"); +--enable_query_log + +ANALYZE TABLE t1; + +explain partitions select * from t1 where sid = '123456789012345678' and create_time >= '2021-03-01 00:00:07.861'; +set debug="+d,preserve_null_partition_catch"; +explain partitions select * from t1 where sid = '123456789012345678' and create_time >= '2021-03-01 00:00:07.861'; +set debug="-d,preserve_null_partition_catch"; + +DROP TABLE t1; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index a2e0c4f..0051d98 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -7628,6 +7628,22 @@ ha_rows ha_partition::records_in_range(uint inx, key_range *min_key, if (rows == HA_POS_ERROR) DBUG_RETURN(HA_POS_ERROR); + + /* + Skip estimation on the potential non-matching NULL partition, + at the cost of wasting one index dive. + + See get_part_iter_for_interval_via_mapping() for NULL partition catch, + ret_null_part, and get_next_partition_id_range() for action on the catch. + */ + bool workaround_null_partition_catch = true; + DBUG_EXECUTE_IF("preserve_null_partition_catch", + workaround_null_partition_catch = false;); + if (workaround_null_partition_catch && part_id == 0 && rows == 1 && + bitmap_bits_set(&m_part_info->read_partitions) > 1) { + continue; + } + estimated_rows+= rows; checked_rows+= m_file[part_id]->stats.records; /*