Bug #106517 Default collation causes inconsistent results
Submitted: 19 Feb 8:45 Modified: 4 Mar 15:47
Reporter: John Jove Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0 OS:Ubuntu (20.04 LTS)
Assigned to: CPU Architecture:x86

[19 Feb 8:45] John Jove
Description:
For the same string, whether with or without collation cause inconsistent results.

How to repeat:
The default Charset in MySQL is utf8mb4, which default collation is utf8mb4_0900_ai_ci. I try the following two cases:

case 1:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 DECIMAL);
INSERT INTO t0 VALUES(NULL);
UPDATE t0 SET c0 = 1 WHERE (t0.c0 IS NULL)>>('');

case 2:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 DECIMAL);
INSERT INTO t0 VALUES(NULL);
UPDATE t0 SET c0 = 1 WHERE (t0.c0 IS NULL)>>('' COLLATE 'utf8mb4_0900_ai_ci');

I think the UPDATE statements in case 1 and case 2 should behave the same, as both succeed or fail. 
In fact, case 1 failed and case 2 succeeded.
[21 Feb 14:03] MySQL Verification Team
Hi Mr. Jove,

Thank you for your bug report.

We have managed to repeat the behaviour that you reported.

We agree that there should be no reason in comparing decimal column with NULL, regardless of NULL's collation.

We only disagree in the severity of the bug reported.

Verified as reported.
[4 Mar 15:47] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.29 release, and here's the proposed changelog entry from the documentation team:

Equivalent queries could return different results because the execution
paths differed slightly. For example, Item::val_int_from_string() and
Item_string::val_int() should use the same algorithm for string to integer
conversion.

Thank you for the bug report.