MySQL Bugs: #109211: Incorrect Left Outer Join Execution

Bug #109211	Incorrect Left Outer Join Execution
Submitted:	27 Nov 2022 7:56	Modified:	24 Jan 2023 22:40
Reporter:	Xiu Tang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.30, 8.0.31	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Incorrect Nested loop left join when transforming block_nested_loop condition.

How to repeat:
CREATE TABLE `t0` (
  `c0` smallint DEFAULT NULL
);

INSERT INTO `t0` VALUES (32767),(NULL);

CREATE TABLE `t1` (
  `c0` decimal(10,0)
);

INSERT INTO `t1` VALUES (0000000000),(NULL),(NULL),(NULL);

CREATE TABLE `t2` (
  `c0` decimal(10,0) unsigned zerofill DEFAULT NULL
);

INSERT INTO `t2` VALUES (0000001964),(0292269042);

mysql> SET optimizer_switch='block_nested_loop=on';

mysql> SELECT ALL t2.c0 FROM t2 CROSS JOIN t0 RIGHT OUTER JOIN t1 ON t1.c0 = t0.c0;
+------------+
| c0         |
+------------+
| 0000001964 |
| 0292269042 |
|       NULL |
|       NULL |
|       NULL |
+------------+
5 rows in set (0.00 sec)

mysql> SET optimizer_switch='block_nested_loop=off';

mysql> SELECT  t2.c0 FROM t2 CROSS JOIN t0 RIGHT OUTER JOIN t1 ON t1.c0 = t0.c0;
+------+
| c0   |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
+------+
4 rows in set (0.00 sec)

Suggested fix:
mysql> SET optimizer_switch='block_nested_loop=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain format=tree SELECT t2.c0 FROM t2 CROSS JOIN t0 RIGHT OUTER JOIN t1 ON t1.c0 = t0.c0; 
+-----------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                       |
+-----------------------------+
| -> Left hash join (t1.c0 = t0.c0)  (cost=2.61 rows=16)
    -> Table scan on t1  (cost=0.65 rows=4)
    -> Hash
        -> Inner hash join (no condition)  (cost=0.76 rows=4)
            -> Table scan on t0  (cost=0.06 rows=2)
            -> Hash
                -> Table scan on t2  (cost=0.11 rows=2)
+-----------------------------+

mysql> SET optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain format=tree SELECT t2.c0 FROM t2 CROSS JOIN t0 RIGHT OUTER JOIN t1 ON t1.c0 = t0.c0; 
+-----------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                 |
+-----------------------------+
| -> Nested loop left join  (cost=3.25 rows=16)
    -> Table scan on t1  (cost=0.65 rows=4)
    -> Nested loop inner join  (cost=2.70 rows=4)
        -> Table scan on t2  (cost=0.30 rows=2)
        -> Filter: (t1.c0 = t0.c0)  (cost=0.28 rows=2)
            -> Table scan on t0  (cost=0.28 rows=2)
 |
+-----------------------------+

Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh

Documented fix as follows in the MySQL 8.0.33 changelog:

    A hash outer join sometimes incorrectly matched NULL with a
    decimal zero or an empty string that used a non-padding
    collation, leading to erroneous results.
      

Closed.