| Bug #119081 | UPDATE with CASE/IFNULL expression produces unexpected error: Incorrect DECIMAL value: '0' for column '' at row -1 | ||
|---|---|---|---|
| Submitted: | 27 Sep 8:35 | Modified: | 29 Sep 17:33 |
| Reporter: | ruifeng wang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.43 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Sep 17:33]
MySQL Verification Team
I verified the behavior but I do not believe this is a bug but let's wait for the optimizer team to confirm.
[2 Oct 7:12]
Roy Lyseng
Posted by developer: Looks like a minor bug in error reporting. An empty string is cast into a decimal value, which is not valid. Since this is an UPDATE statement and session is on strict mode, an error is issued. However, the error text could be more informative.
Description: When executing an UPDATE statement with a nested CASE expression and IFNULL in the WHERE clause, MySQL 8.0.43 returns the error: [HY000][1366] Incorrect DECIMAL value: '0' for column '' at row -1 The error message is suspicious because: The target table and columns exist and are valid. The error message shows an empty column name (''). The row number is reported as -1, which is not expected. Logically, the query should either update zero rows or succeed silently. How to repeat: CREATE TABLE IF NOT EXISTS t0(c0 MEDIUMINT(234) ZEROFILL COLUMN_FORMAT DEFAULT COMMENT 'asdf' STORAGE DISK NULL UNIQUE, c1 FLOAT COLUMN_FORMAT DEFAULT STORAGE MEMORY) ; REPLACE INTO t0(c0) VALUES(NULL); UPDATE t0 SET c0=((! (("u") IS TRUE))) IS NOT FALSE WHERE (((CASE t0.c0 WHEN t0.c0 THEN NULL WHEN t0.c0 THEN 0.6941511423926717 WHEN t0.c0 THEN t0.c0 ELSE 0.6918906923357651 END)) >= (IFNULL("", NULL))) IN (CAST(CAST('846417880' AS SIGNED) AS SIGNED));