Bug #115653 LEFT OUTER JOIN returns incorrect results
Submitted: 20 Jul 2024 4:28 Modified: 22 Jul 2024 13:26
Reporter: Chenglin Liang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:9.0.0, 8.4.1, 8.0.38 OS:Ubuntu (Ubuntu 20.04.6 LTS)
Assigned to: CPU Architecture:x86 (5.15.0-113-generic)
Tags: innodb, LEFT OUTER JOIN, myisam

[20 Jul 2024 4:28] Chenglin Liang 
Description:
When I use LEFT OUTER JOIN to join two tables, I get the wrong result.

How to repeat:
CREATE TABLE t0(c0 CHAR(100)  PRIMARY KEY NOT NULL) engine=InnoDB;
CREATE TABLE t1(c0 REAL  UNIQUE NOT NULL, c1 CHAR(100)  NOT NULL, c2 VARCHAR(100)  UNIQUE) engine=MyISAM;
INSERT INTO t0 VALUES ('');
INSERT INTO t1 VALUES (-531215489, 'ргн', '\n~BwFj');
INSERT INTO t1 VALUES (963212662, '', 'qt');

SELECT * FROM t0; -- get ['']
SELECT * FROM t1; -- get [[-531215489,' ',~BwFj],[963212662,'',qt]]

SELECT t0.c0 FROM t0 LEFT JOIN t1 ON t0.c0 = t1.c1; -- get ['',''], expect ['']

Suggested fix:
explain format=tree SELECT t0.c0 FROM t0 LEFT JOIN t1 ON t0.c0 = t1.c1; 
EXPLAIN
 -> Left hash join (<hash>(t1.c1)=<hash>(t0.c0)), extra conditions: (t1.c1 = t0.c0)  (cost=0.901 rows=2)
    -> Covering index scan on t0 using PRIMARY  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.45 rows=2)
[22 Jul 2024 7:42] MySQL Verification Team 
Hello Liang Chenglin,

Thank you for the report and test case.

regards,
Umesh
[22 Jul 2024 13:26] Bernt Marius Johnsen 
The character inserted into t0.c0 in the first row is U+08ED ARABIC TONE ONE DOT BELOW (UTF-8 encoded E0A3AD). This is an ignorable, so

mysql> select _utf8mb4 x'E0A3AD' = '';
+-------------------------+
| _utf8mb4 x'E0A3AD' = '' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0,00 sec)

Which means that this is not a bug.