diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index d5fe7be..bdb6b75 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -1183,3 +1183,52 @@ t1 CREATE TABLE `t1` ( PARTITION p3 VALUES IN (3) TABLESPACE = `innodb_system` ENGINE = InnoDB) */ DROP TABLE t1; DROP TABLESPACE ts1; +# +# Bug #83470: Reverse scan on a partitioned table does ICP check incorrectly, causing slowdown +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +date_col datetime not null, +col1 int, +filler varchar(255), +key(date_col, col1) +) +partition by range columns(date_col) +( +partition p0 values less than ('2014-01-01 00:00:00'), +partition p1 values less than ('2015-01-01 00:00:00'), +partition p2 values less than ('2016-01-01 00:00:00') +); +insert into t1 select +date_add('2013-01-01', interval (A.a+10*B.a) day), +123456, +repeat('filler-data', 5) +from t0 A, t0 B, t0 C; +insert into t1 select +date_add('2014-01-01', interval (A.a+10*B.a) day), +123456, +repeat('filler-data', 5) +from t0 A, t0 B, t0 C; +set @cnt=1; +select * from t1 force index(date_col) +where +col1 + 10000000 > (@cnt:=@cnt+1) and +date_col >= date('2013-04-06') AND date_col < date('2013-04-06' + interval 1 day) +ORDER BY date_col DESC; +date_col col1 filler +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +2013-04-06 00:00:00 123456 filler-datafiller-datafiller-datafiller-datafiller-data +# This must return 10, not ~961: +select @cnt; + at cnt +11 +drop table t0, t1; diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index cc0c9c4..066cb25 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -1151,6 +1151,48 @@ SHOW CREATE TABLE t1; DROP TABLE t1; DROP TABLESPACE ts1; +--echo # +--echo # Bug #83470: Reverse scan on a partitioned table does ICP check incorrectly, causing slowdown +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + date_col datetime not null, + col1 int, + filler varchar(255), + key(date_col, col1) +) +partition by range columns(date_col) +( + partition p0 values less than ('2014-01-01 00:00:00'), + partition p1 values less than ('2015-01-01 00:00:00'), + partition p2 values less than ('2016-01-01 00:00:00') +); + +insert into t1 select + date_add('2013-01-01', interval (A.a+10*B.a) day), + 123456, + repeat('filler-data', 5) +from t0 A, t0 B, t0 C; + +insert into t1 select + date_add('2014-01-01', interval (A.a+10*B.a) day), + 123456, + repeat('filler-data', 5) +from t0 A, t0 B, t0 C; + +set @cnt=1; +select * from t1 force index(date_col) +where + col1 + 10000000 > (@cnt:=@cnt+1) and + date_col >= date('2013-04-06') AND date_col < date('2013-04-06' + interval 1 day) +ORDER BY date_col DESC; + +--echo # This must return 10, not ~961: +select @cnt; + +drop table t0, t1; --rmdir $MYSQL_TMP_DIR/temp_dir/test --rmdir $MYSQL_TMP_DIR/temp_dir diff --git a/sql/partitioning/partition_handler.cc b/sql/partitioning/partition_handler.cc index c574d7f..10bc4b2 100644 --- a/sql/partitioning/partition_handler.cc +++ b/sql/partitioning/partition_handler.cc @@ -2495,7 +2495,6 @@ int Partition_helper::ph_index_read_map(uchar *buf, enum ha_rkey_function find_flag) { DBUG_ENTER("Partition_handler::ph_index_read_map"); - m_handler->end_range= NULL; m_index_scan_type= PARTITION_INDEX_READ; m_start_key.key= key; m_start_key.keypart_map= keypart_map; @@ -2611,7 +2610,6 @@ int Partition_helper::ph_index_first(uchar *buf) { DBUG_ENTER("Partition_helper::ph_index_first"); - m_handler->end_range= NULL; m_index_scan_type= PARTITION_INDEX_FIRST; m_reverse_order= false; DBUG_RETURN(common_first_last(buf)); @@ -2693,7 +2691,6 @@ int Partition_helper::ph_index_read_last_map(uchar *buf, DBUG_ENTER("Partition_helper::ph_index_read_last_map"); m_ordered= true; // Safety measure - m_handler->end_range= NULL; m_index_scan_type= PARTITION_INDEX_READ_LAST; m_start_key.key= key; m_start_key.keypart_map= keypart_map;