Bug #115565 Partition tables:compare the field with null and return an incorrect result

• Submitted: 11 Jul 2024 9:37
• Modified: 11 Jul 2024 10:37
• Reporter: GONGTUI FU
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: IS NULL, left join, partition tables

[11 Jul 2024 9:37] GONGTUI FU

Description:
Hi, guys:
Two partition tables use left join. If the field in the on condition is compared with null in the where clause, the query result set is incorrect

How to repeat:
1. Download bmsql_warehouse.sql and bmsql_district.sql
2. create database test;
3. use test;
4  source bmsql_warehouse.sql;
5. source bmsql_district.sql;
6. select b.*, a.* from (select d_w_id, sum(d_ytd) s from bmsql_district where d_w_id = 500 group by d_w_id) b left join (Select w_id, w_ytd from bmsql_warehouse where w_id = 500) a  on a.w_id=b.d_w_id and a.w_ytd=b.s where a.w_id is null;

In 8.0.30 version, the sql return:
+--------+-----------+------+-------+
| d_w_id | s         | w_id | w_ytd |
+--------+-----------+------+-------+
|    500 | 300000.00 | NULL |  NULL |
+--------+-----------+------+-------+

In 8.0.25 vsersion, the sql return empty set.
In ORACLE database, the sql return empty set too.

[11 Jul 2024 10:37] MySQL Verification Team

Hi Mr. FU,

Thank you for your bug report.

This is indeed a very small bug in our Optimiser.

It is checked against 8.0.38, 8.4.1 and 9.0.0.

Here is the problem, fully exposed. The problems is in the last WHERE, more precisely in WHERE a.w_id is null;

Here are the outputs:

mysql> select b.*, a.* from (select d_w_id, sum(d_ytd) s from bmsql_district where d_w_id = 500 group by d_w_id) b left join (Select w_id, w_ytd from bmsql_warehouse where w_id = 500) a  on a.w_id=b.d_w_id and a.w_ytd=b.s;
+--------+-----------+------+-----------+
| d_w_id | s         | w_id | w_ytd     |
+--------+-----------+------+-----------+
|    500 | 300000.00 |  500 | 300000.00 |
+--------+-----------+------+-----------+
1 row in set (0.03 sec)

mysql> select b.*, a.* from (select d_w_id, sum(d_ytd) s from bmsql_district where d_w_id = 500 group by d_w_id) b left join (Select w_id, w_ytd from bmsql_warehouse where w_id = 500) a  on a.w_id=b.d_w_id and a.w_ytd=b.s where a.w_id is null;
+--------+-----------+------+-------+
| d_w_id | s         | w_id | w_ytd |
+--------+-----------+------+-------+
|    500 | 300000.00 | NULL |  NULL |
+--------+-----------+------+-------+
1 row in set (0.00 sec)

Verified for versions 8.0 and all higher supported versions.