Bug #119169 Inconsistent Result with DAYNAME() and DECIMAL

• Submitted: 16 Oct 2025 9:08
• Modified: 15 Jan 8:59
• Reporter: zz z
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0 8.4.6
• OS: Any
• CPU Architecture: Any

[16 Oct 2025 9:08] zz z

Description:
A query yields inconsistent results between the optimized plan and the unoptimized count when a WHERE clause multiplies the string output of the DAYNAME() function by a DECIMAL column. The implicit cast of the DAYNAME() string to 0 should always result in a FALSE condition, but the unoptimized path incorrectly evaluates it as TRUE.

How to repeat:
CREATE TABLE t4668 (c1 DECIMAL(20,0));
ALTER TABLE t4668 RENAME t4669;
INSERT INTO t4669 (c1) VALUES (302);

SELECT c1 FROM t4669 WHERE (('Tuesday' * c1));
-- 0
SELECT SUM(count) FROM (SELECT ((('Tuesday' * c1))) IS TRUE AS count FROM t4669) AS ta_norec;
-- 1

[15 Jan 8:59] Øystein Grøvlen

Thank you for the bug report, but I am not able to reproduce this with the given test case:

mysql [localhost:8406] {msandbox} (test) > CREATE TABLE t4668 (c1 DECIMAL(20,0));
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8406] {msandbox} (test) > ALTER TABLE t4668 RENAME t4669;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8406] {msandbox} (test) > INSERT INTO t4669 (c1) VALUES (302);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8406] {msandbox} (test) >
mysql [localhost:8406] {msandbox} (test) > SELECT c1 FROM t4669 WHERE (('Tuesday' * c1));
Empty set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'Tuesday'
mysql [localhost:8406] {msandbox} (test) > SELECT SUM(count) FROM (SELECT ((('Tuesday' * c1))) IS TRUE AS count FROM t4669) AS ta_norec;
+------------+
| SUM(count) |
+------------+
|          0 |
+------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'Tuesday'

Are you sure you have provided the right test case? It does not match the description.  (There is no DAYNAME() function in the test case.)