[MDEV-34417] Wrong result set with utf8mb4_danish_ci and BNLH join - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5, 11.6(EOL)
Fix Version/s: 10.5.26, 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3, 11.5.2, 11.6.0
Component/s: Character Sets, Optimizer
Labels:
None

CREATE OR REPLACE TABLE t1 (a VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci);

INSERT INTO t1 VALUES ('aaaa'),('åå');

SELECT * FROM t1 WHERE a='aaaa';

+------+

| a    |

+------+

| aaaa |

| åå   |

+------+

Looks good so far. It returns two records, because in Danish collation 'å' is equal to 'aa'.

Now I join the table to itself:

SET join_cache_level=1;

SELECT * FROM t1 NATURAL JOIN t1 t2;

+------+

| a    |

+------+

| aaaa |

| åå   |

| aaaa |

| åå   |

+------+

Looks good so far.

Now I enable the BNLH join:

SET join_cache_level=3;

SELECT * FROM t1 NATURAL JOIN t1 t2;

+------+

| a    |

+------+

| aaaa |

| åå   |

+------+

Half of the records disappeared. Looks wrong.

causes

MDEV-34580 Assertion `(key_part->key_part_flag & 4) == 0' failed key_hashnr

is duplicated by

MDEV-34352 Execution time for Block Nested Loop Hash (BNLH) join with charset utf8mb4 is 7-20 times slower than with latin1

relates to

MDEV-34232 Check performance of BNL-H vs MySQL's hash join

MDEV-34352 Execution time for Block Nested Loop Hash (BNLH) join with charset utf8mb4 is 7-20 times slower than with latin1

MDEV-34427 BNL-H has not optimal implementation for varchar type

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