[MDEV-8625] Bad result set with ignorable characters when using a prefix key - Jira

XML

Word

Printable

Type: Bug
Status: Confirmed (View Workflow)
Priority: Minor
Resolution: Unresolved
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Fix Version/s: 10.1(EOL), 10.3(EOL)
Component/s: Character Sets, Optimizer
Labels:
- upstream

This bug is known for a long time since adding UCA collations.

This script with no index:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci);

INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');

SELECT * FROM t1 WHERE a<'\0b';

and this script with KEY(a):

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, KEY(a));

INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');

SELECT * FROM t1 WHERE a<'\0b';

correctly return one row:

+------+

| a    |

+------+

| a    |

+------+

1 row in set (0.00 sec)

This script with a prefix index KEY(a(1))

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, KEY(a(1)));

INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');

SELECT * FROM t1 WHERE a<'\0b';

erroneously returns empty set.

relates to

MDEV-8626 Bad result set with expansions when using a prefix key

MDEV-20884 Different resultset when using prefix index and utf8mb4

MDEV-34611 INNER JOIN returns incorrect results

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