Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21284

query with ”partition + order by + pre index“ return the wrong result

    XMLWordPrintable

Details

    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 */
      
      

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            willhan willhan
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.