Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.3.20, 10.4.10, 10.5.0
Description
Description:
The order-by clause gives wrong results when used on a partitioned table with a composite index that contains a prefix index as the leftmost column.
test case:
CREATE TABLE `willtest10` (
|
`thedate` int(11) NOT NULL, |
`dtEventTime` varchar(32) NOT NULL, |
`dtEventTimeStamp` bigint(20) NOT NULL, |
`metrics` text,
|
`data_cnt` bigint(20) DEFAULT NULL, |
KEY `ind_metrics` (`metrics`(30),`dtEventTimeStamp`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='shard_key "dtEventTimeStamp"' |
/*!50100 PARTITION BY RANGE (thedate)
|
(PARTITION p20190904 VALUES LESS THAN (20190904) ENGINE = InnoDB,
|
PARTITION p20190925 VALUES LESS THAN (20190925) ENGINE = InnoDB) */ ; |
INSERT INTO `willtest10` VALUES (20190903,'2019-09-03 23:58:00',1567526280000,'com.parties.metrics.QueueMetrics.1100.playersEnteredQueue',1),(20190922,'2019-09-22 02:57:00',1569092220000,'com.parties.metrics.QueueMetrics.1100.playersEnteredQueue',1),(20190923,'2019-09-23 02:44:00',1569177840000,'com.parties.metrics.QueueMetrics.1090.playersEnteredQueue',1),(20190923,'2019-09-23 02:44:00',1569177840000,'com.parties.metrics.QueueMetrics.840.playersEnteredQueue',1); |
|
mysql> select * from willtest10 order by dtEventTimeStamp desc;
|
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
| thedate | dtEventTime | dtEventTimeStamp | metrics | data_cnt |
|
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
| 20190923 | 2019-09-23 02:44:00 | 1569177840000 | com.parties.metrics.QueueMetrics.1090.playersEnteredQueue | 1 | |
| 20190923 | 2019-09-23 02:44:00 | 1569177840000 | com.parties.metrics.QueueMetrics.840.playersEnteredQueue | 1 | |
| 20190922 | 2019-09-22 02:57:00 | 1569092220000 | com.parties.metrics.QueueMetrics.1100.playersEnteredQueue | 1 | |
| 20190903 | 2019-09-03 23:58:00 | 1567526280000 | com.parties.metrics.QueueMetrics.1100.playersEnteredQueue | 1 | |
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
4 rows in set (0.00 sec) |
|
mysql> select * FROM willtest10 force index(ind_metrics) WHERE metrics = 'com.parties.metrics.QueueMetrics.1100.playersEnteredQueue' ORDER BY dtEven |
tTimeStamp desc limit 1 ; |
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
| thedate | dtEventTime | dtEventTimeStamp | metrics | data_cnt |
|
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
| 20190903 | 2019-09-03 23:58:00 | 1567526280000 | com.parties.metrics.QueueMetrics.1100.playersEnteredQueue | 1 | |
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
1 row in set (0.00 sec) |
|
mysql> select * FROM willtest10 force index(ind_metrics) WHERE metrics = 'com.parties.metrics.QueueMetrics.1100.playersEnteredQueue' ORDER BY dtEven |
tTimeStamp desc ;
|
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
| thedate | dtEventTime | dtEventTimeStamp | metrics | data_cnt |
|
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
| 20190903 | 2019-09-03 23:58:00 | 1567526280000 | com.parties.metrics.QueueMetrics.1100.playersEnteredQueue | 1 | |
| 20190922 | 2019-09-22 02:57:00 | 1569092220000 | com.parties.metrics.QueueMetrics.1100.playersEnteredQueue | 1 | |
+----------+---------------------+------------------+-----------------------------------------------------------+----------+
|
2 rows in set (0.00 sec) |
And we fix this problem like this patch:
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
|
index 60c367e20c0..e4cdc08138d 100644 |
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -21942,6 +21942,28 @@ void compute_part_of_sort_key_for_equals(JOIN *join, TABLE *table, |
}
|
|
|
+bool is_perfix_index(TABLE* table, int key, uint key_parts ) |
+{
|
+ if (!table || !table->key_info) |
+ {
|
+ return false; |
+ }
|
+ KEY_PART_INFO* key_part = table->key_info[key].key_part;
|
+ KEY* key_info = table->key_info + key;
|
+
|
+ for (uint i = 0; i < key_parts; i++, key_part++) |
+ {
|
+ if (key_part->field && |
+ (key_part->length !=
|
+ table->field[key_part->fieldnr - 1]->key_length() && |
+ !(key_info->flags & (HA_FULLTEXT | HA_SPATIAL))))
|
+ {
|
+ return true; |
+ }
|
+ }
|
+ return false; |
+}
|
+
|
/** |
Test if we can skip the ORDER BY by using an index.
|
|
@@ -22065,6 +22087,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
|
}
|
}
|
|
+ if ( is_perfix_index(table, ref_key, ref_key_parts) &&
|
+ table && table->part_info && order)
|
+ {
|
+ DBUG_RETURN(0);
|
+ }
|
+
|
if (ref_key >= 0 && ref_key != MAX_KEY)
|
{
|
/* Current access method uses index ref_key with ref_key_parts parts */
|
|