[MDEV-30072] Wrong ORDER BY for a partitioned prefix key + NOPAD - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.11, 11.0
Fix Version/s: 11.1.1, 10.11.3, 11.0.2, 10.4.29, 10.5.20, 10.6.13, 10.7.8, 10.8.8, 10.9.6, 10.10.4
Component/s: Character Sets, Partitioning
Labels:
- regression-10.4

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.

is caused by

MDEV-25904 New collation functions to compare InnoDB style trimmed NO PAD strings

relates to

MDEV-27670 Assertion `(cs->state & 0x20000) == 0' failed in my_strnncollsp_nchars_generic_8bit

MDEV-30095 Unexpected duplicate entry error for UNIQUE USING HASH + NOPAD

MDEV-30034 UNIQUE USING HASH accepts duplicate entries for tricky collations

MDEV-30048 Prefix keys for CHAR work differently for MyISAM vs InnoDB

MDEV-30050 Inconsistent results of DISTINCT with NOPAD

(1 relates to)

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