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

Wrong ORDER BY for a partitioned prefix key + NOPAD

Details

    Description

      I create a table:

      CREATE OR REPLACE TABLE t1
      		 
      (
      		 
        id INT,
      		 
        data VARCHAR(20),
      		 
        KEY data_id (data,id)
      		 
      ) COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM
      		 
      PARTITION BY RANGE COLUMNS (id)
      		 
      (
      		 
        PARTITION p10 VALUES LESS THAN (20),
      		 
        PARTITION p20 VALUES LESS THAN MAXVALUE
      		 
      );
      		 
      INSERT INTO t1 VALUES (30, 'ss '), (10, 'ß ');

      Now I run ORDER BY queries:

      SELECT id FROM t1 WHERE data='ss ' ORDER BY id;


      +------+
      		 
      | id   |
      		 
      +------+
      		 
      |   10 |
      		 
      |   30 |
      		 
      +------+


      SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;


      +------+
      		 
      | id   |
      		 
      +------+
      		 
      |   30 |
      		 
      |   10 |
      		 
      +------+

      Looks good so far.

      Now I alter the table changing the full key to a prefix key:

      ALTER TABLE t1 DROP KEY data_id, ADD KEY data_id2(data(10),id);

      And return ORDER BY queries again:

      SELECT id FROM t1 WHERE data='ss ' ORDER BY id;


      +------+
      		 
      | id   |
      		 
      +------+
      		 
      |   30 |
      		 
      |   10 |
      		 
      +------+


      SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;


      +------+
      		 
      | id   |
      		 
      +------+
      		 
      |   10 |
      		 
      |   30 |
      		 
      +------+

      Notice the order changed to opposite.

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25904 New collation functions to compare InnoDB style trimmed NO PAD strings

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27670 Assertion `(cs->state & 0x20000) == 0' failed in my_strnncollsp_nchars_generic_8bit

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30095 Unexpected duplicate entry error for UNIQUE USING HASH + NOPAD

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30034 UNIQUE USING HASH accepts duplicate entries for tricky collations

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30048 Prefix keys for CHAR work differently for MyISAM vs InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30050 Inconsistent results of DISTINCT with NOPAD

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Labels regression-10.4
            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Summary Wrong ORDER BY for a partitioned prefix key + NOPAD Wrong ORDER BY with a NOPAD collation
            bar Alexander Barkov made changes -
            Summary Wrong ORDER BY with a NOPAD collation Wrong ORDER BY for a partitioned prefix key + NOPAD
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Comment [ With InnoDB it returns a different result (only one row):
            {code:sql}
            CREATE OR REPLACE TABLE t1
            (
              id INT,
              data VARCHAR(20),
              KEY data_id (data,id)
            ) COLLATE utf8mb3_general_nopad_ci ENGINE=InnoDB
            PARTITION BY RANGE COLUMNS (id)
            (
              PARTITION p10 VALUES LESS THAN (20),
              PARTITION p20 VALUES LESS THAN MAXVALUE
            );
            INSERT INTO t1 VALUES (30, 'ss '), (10, 'ß ');
            SELECT id FROM t1 WHERE data='ss ' ORDER BY id;
            SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;
            {code}
            {noformat}
            MariaDB [test]> SELECT id FROM t1 WHERE data='ss ' ORDER BY id;
            +------+
            | id |
            +------+
            | 30 |
            +------+
            1 row in set (0.002 sec)

            MariaDB [test]> SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;
            +------+
            | id |
            +------+
            | 30 |
            +------+
            1 row in set (0.002 sec)
            {noformat}
            ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.4.29 [ 28510 ]
            Fix Version/s 10.5.20 [ 28512 ]
            Fix Version/s 10.6.13 [ 28514 ]
            Fix Version/s 10.8.8 [ 28518 ]
            Fix Version/s 10.9.6 [ 28520 ]
            Fix Version/s 10.10.4 [ 28522 ]
            Fix Version/s 10.11.3 [ 28524 ]
            Fix Version/s 11.1.1 [ 28704 ]
            Fix Version/s 11.0.2 [ 28706 ]
            Fix Version/s 10.7.8 [ 28515 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.