[MDEV-34613] Nested loop 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 FLOAT, c1 TEXT);

CREATE TABLE t1(c0 TEXT) ;

CREATE INDEX i0 ON t1(c0(1)) ALGORITHM INPLACE;

INSERT IGNORE INTO t0(c1) VALUES("ۛK?6");

REPLACE LOW_PRIORITY INTO t1(c0) VALUES(96), ("cx"), (NULL);

SELECT * FROM t0; -- get [[NULL,K?6]]

SELECT * FROM t1; -- get [96,cx,NULL]

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

Suggested fix:
When I remove the index i0, the result is correct.

CREATE TABLE t0(c0 FLOAT, c1 TEXT);

CREATE TABLE t1(c0 TEXT) ;

INSERT IGNORE INTO t0(c1) VALUES("ۛK?6");

REPLACE LOW_PRIORITY INTO t1(c0) VALUES(96), ("cx"), (NULL);

SELECT ALL t0.c1 AS ref0 FROM t0 INNER JOIN t1 ON t0.c1 >= t1.c0; -- get [K?6,K?6]

relates to

MDEV-34006 Incorrect query result

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