Bug #112421 zero and null value, left or right join result wrong

• Submitted: 22 Sep 2023 1:46
• Modified: 24 Sep 2023 2:34
• Reporter: bear chen
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: CentOS (7)
• CPU Architecture: Any

[22 Sep 2023 1:46] bear chen

Description:
exists zero and null value, left join or right join result wrong

How to repeat:
drop table if exists test1;
create table test1 (id decimal(12,2));
insert into test1 values (0.00),(NULL);

drop table if exists test2;
create table test2 (id decimal(12,2));
insert into test2 values (0.00),(NULL);

select * from test1 c left join test2 d on c.id=d.id;
select * from test1 c right join test2 d on c.id=d.id;

8.0.32 detail:
root@localhost:db01 8.0.32 09:39:51> select * from test1;
+------+
| id   |
+------+
| 0.00 |
| NULL |
+------+
2 rows in set (0.00 sec)

root@localhost:db01 8.0.32 09:40:04> select * from test2;
+------+
| id   |
+------+
| 0.00 |
| NULL |
+------+
2 rows in set (0.00 sec)

root@localhost:db01 8.0.32 09:40:05> select * from test1 c left join test2 d on c.id=d.id;
+------+------+
| id   | id   |
+------+------+
| 0.00 | NULL |
| 0.00 | 0.00 |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

5.7.38 detail:
root@localhost:db01 5.7.38-log 09:41:23> select * from test1;
+------+
| id   |
+------+
| 0.00 |
| NULL |
+------+
2 rows in set (0.00 sec)

root@localhost:db01 5.7.38-log 09:41:27> select * from test2;
+------+
| id   |
+------+
| 0.00 |
| NULL |
+------+
2 rows in set (0.01 sec)

root@localhost:db01 5.7.38-log 09:41:44> select * from test1 c left join test2 d on c.id=d.id;
+------+------+
| id   | id   |
+------+------+
| 0.00 | 0.00 |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

so, 8.0.32 version, can zero and NULL be joined?

Suggested fix:

zero and NULL value cannot be joined

[22 Sep 2023 10:04] MySQL Verification Team

Hi Mr. chen,

Thank you for your bug report.

We ran your test case with latest 8.0 and 8.1.

Both return correct results. We ran 4 (four) selects. SELECT from each of the table, SELECT with a left join and SELECT with a right join. All results are correct and as they should be:

+------+
| id   |
+------+
| 0.00 |
| NULL |
+------+
+------+
| id   |
+------+
| 0.00 |
| NULL |
+------+
+------+------+
| id   | id   |
+------+------+
| 0.00 | 0.00 |
| NULL | NULL |
+------+------+
+------+------+
| id   | id   |
+------+------+
| 0.00 | 0.00 |
| NULL | NULL |
+------+------+

Can't repeat.

[24 Sep 2023 2:34] bear chen

Is it related to the proofreading set?

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:(none) 8.0.32 10:33:46> show variables like "%collation%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+
4 rows in set (0.01 sec)

[25 Sep 2023 11:02] MySQL Verification Team

Hi,

No, it isn't we used the same settings.

If you are not using package downloaded from our site, dev.mysql.com, then it could be the cause.