[Commits] 8b58542: Bug #83470: Reverse scan on a partitioned table does ICP check incorrectly, causing slowdown

Sergei Petrunia psergey at askmonty.org
Fri Oct 21 21:20:16 EEST 2016


revision-id: 8b58542281b40e46bcad065a90a302153869bedc
parent(s): c28e258157f39f25e044bb72e8bae1ff00989a3d
committer: Sergei Petrunia
branch nick: mysql-5.7
timestamp: 2016-10-21 21:20:16 +0300
message:

Bug #83470:	Reverse scan on a partitioned table does ICP check incorrectly, causing slowdown

Partition_helper functions should not clear m_handler->end_range

---
 mysql-test/r/partition_innodb.result  |   49 +++++++++++++++++++++++++++++++++
 mysql-test/t/partition_innodb.test    |   42 ++++++++++++++++++++++++++++
 sql/partitioning/partition_handler.cc |    3 --
 3 files changed, 91 insertions(+), 3 deletions(-)

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;


More information about the commits mailing list