| Bug #104021 | outer join got wrong result while blob type contains NULL and empty value | ||
|---|---|---|---|
| Submitted: | 15 Jun 2021 8:45 | Modified: | 18 Aug 2021 10:34 |
| Reporter: | track ay | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S3 (Non-critical) |
| Version: | 8.0.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Jun 2021 13:20]
MySQL Verification Team
Hi Mr. ay, Thank you for your bug report. However, this is not a bug. What you have asked MySQL is to compare one column to itself. Hence, a result of that query can only be a Cartesian product (and not a join), and that is what MySQL has delivered exactly. Not a bug.
[16 Jun 2021 3:06]
track ay
CREATE TABLE `IDT_20141` ( `COL1` varchar(20), `COL2` bigint DEFAULT NULL, `COL3` year DEFAULT NULL, KEY `U_M_COL` (`COL1`(10),`COL2`,`COL3`) /*!80000 INVISIBLE */);
insert into `IDT_20141`(`COL1`, `COL2`) values (NULL, 6859552977531535821), ("", -8636772661817289608), ('abc', -5727772875978232085);
select t1.col1, t1.col2, t2.col1, t2.col2 from `IDT_20141` t1 right join `IDT_20141` t2 on t1.`COL1` = t2.`COL1`;
+--------+----------------------+--------+----------------------+
| col1 | col2 | col1 | col2 |
+--------+----------------------+--------+----------------------+
| <null> | <null> | <null> | 6859552977531535821 |
| | -8636772661817289608 | | -8636772661817289608 |
| abc | -5727772875978232085 | abc | -5727772875978232085 |
+--------+----------------------+--------+----------------------+
I don't think it's not a bug, thus how to explain the above behavior.
[18 Aug 2021 10:34]
track ay
I think it's a bug because after changing the tinyblob type to varchar, I got a completely different result. So there must be a problem somewhere
[18 Aug 2021 12:08]
MySQL Verification Team
Hi, Please do note that our latest release is 8.0.26 and that query cache is no longer maintained.
Description: select t1.col1, t1.col2, t2.col1, t2.col2 from `IDT_20140` t1 right join `IDT_20140` t2 on t1.`COL1` = t2.`COL1`; +--------------------------------------------+----------------------+--------------------------------------------+----------------------+ | col1 | col2 | col1 | col2 | +--------------------------------------------+----------------------+--------------------------------------------+----------------------+ | <null> | <null> | <null> | 6859552977531535821 | | | -8636772661817289608 | | -8636772661817289608 | | <null> | 6859552977531535821 | | -8636772661817289608 | | 0x067cbf35a1d655cfe2b30bae340dc95a3ff8434a | -5727772875978232085 | 0x067cbf35a1d655cfe2b30bae340dc95a3ff8434a | -5727772875978232085 | +--------------------------------------------+----------------------+--------------------------------------------+----------------------+ How to repeat: CREATE TABLE `IDT_20140` ( `COL1` tinyblob, `COL2` bigint DEFAULT NULL, `COL3` year DEFAULT NULL, KEY `U_M_COL` (`COL1`(10),`COL2`,`COL3`) /*!80000 INVISIBLE */); insert into `IDT_20140`(`COL1`, `COL2`) values (NULL, 6859552977531535821), ("", -8636772661817289608), (0x067cbf35a1d655cfe2b30bae340dc95a3ff8434a, -5727772875978232085); select t1.col1, t1.col2, t2.col1, t2.col2 from `IDT_20140` t1 right join `IDT_20140` t2 on t1.`COL1` = t2.`COL1`; Suggested fix: NULL should not be equal to empty strings.