[MDEV-6978] Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column - Jira

XML

Word

Printable

DROP TABLE IF EXISTS t1,t2;

CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY);

INSERT INTO t1 VALUES ('a');

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (a ENUM('a','A') CHARACTER SET latin1 COLLATE latin1_bin);

INSERT INTO t2 VALUES ('a'),('A');

SELECT t1.* FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;

SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;

This script correctly returns 2 rows for the both SELECT queries.

+---+

| a |

+---+

| a |

| a |

+---+

2 rows in set (0.00 sec)

If I now add a primary key on t2 and rerun the queries again:

ALTER TABLE t2 ADD PRIMARY KEY(a);

SELECT t1.* FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;

SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;

both queries return only one rows:

+---+

| a |

+---+

| a |

+---+

1 row in set (0.00 sec)

This is wrong. Two rows should always be returned.

The same problem is repeatable if I change data type for t1.a from ENUM to VARCHAR(10) or SET('a').

relates to

MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column

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