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