[MDEV-34611] INNER JOIN returns incorrect results - Jira

XML

Word

Printable

Description:
When I use INNER JOIN to join two tables, I get the wrong result.

How to repeat:

CREATE TABLE t0(c0 TINYTEXT) ;

CREATE TABLE t1 LIKE t0;

CREATE INDEX i1 ON t0(c0(1) ASC);

INSERT INTO t1(c0) VALUES(19), (6);

INSERT INTO t0(c0) VALUES("㏫"), (61);

INSERT INTO t0(c0) VALUES(29);

INSERT INTO t0(c0) VALUES(99);

SELECT * FROM t0; -- get [㏫,61,29,99]

SELECT * FROM t1; -- get [19,6]

SELECT ALL t1.c0 AS ref0 FROM t1 INNER JOIN t0 ON t1.c0 >= t0.c0; -- get[6,6], but expect [19,6,6]

Suggested fix:
The problem may lie in the optimization of INNER JOIN by storage engine InnoDB.

relates to

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

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

MDEV-20884 Different resultset when using prefix index and utf8mb4

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