Bug #120854 Wrong result with COALESCE(YEAR, 0), arithmetic and window function in VIEW/CTE

• Submitted: 3 Jul 5:49
• Modified: 6 Jul 15:23
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6.0,9.7.0
• OS: Ubuntu
• CPU Architecture: Any

[3 Jul 5:49] Xiaoyuan Xie

Description:
A COALESCE() expression over a YEAR column returns a wrong arithmetic result when used through a VIEW or CTE.

The table contains one row:

c2 = 2075
The expression:

COALESCE(c2, 0)
should evaluate to 2075, because c2 is not NULL. Therefore:

COALESCE(c2, 0) / 2 = 1037.5000
A TEMPORARY TABLE created from the same expression returns the expected result:

1037.5000
However, the VIEW and CTE forms return:

999.9999
This suggests that the COALESCE(YEAR, 0) expression is evaluated with an incorrect numeric value or incorrectly derived numeric type in VIEW/CTE contexts.

The issue appears to involve YEAR, COALESCE(), arithmetic evaluation, VIEW/CTE expression handling, and possibly window function evaluation.

How to repeat:
DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
  c2 YEAR
);

INSERT INTO t0 VALUES (2075);

CREATE VIEW v0 AS
SELECT COALESCE(c2, 0) AS vc
FROM t0;

SELECT vc / 2 AS r, COUNT(*) OVER () AS cnt
FROM v0;

WITH cte AS (
  SELECT COALESCE(c2, 0) AS vc
  FROM t0
)
SELECT vc / 2 AS r, COUNT(*) OVER () AS cnt
FROM cte;

CREATE TEMPORARY TABLE tmp AS
SELECT COALESCE(c2, 0) AS vc
FROM t0;

SELECT vc / 2 AS r, COUNT(*) OVER () AS cnt
FROM tmp;

VIEW:
+----------+-----+
| r        | cnt |
+----------+-----+
| 999.9999 |   1 |
+----------+-----+

CTE:
+----------+-----+
| r        | cnt |
+----------+-----+
| 999.9999 |   1 |
+----------+-----+

TEMPORARY TABLE:
+-----------+-----+
| r         | cnt |
+-----------+-----+
| 1037.5000 |   1 |
+-----------+-----+

[3 Jul 10:15] Chaithra Marsur Gopala Reddy

Hi Xiaoyuan Xie,

Thank you for the test case. Verified as described.

[6 Jul 15:23] Xiaoyuan Xie

Hi Chaithra,

Thank you very much for reviewing the test case and verifying the report.

I really appreciate your prompt response and confirmation. It is encouraging to see the issue acknowledged, and it motivates us to continue testing and providing useful feedback to help improve the correctness and reliability of MySQL.

We will also be more careful to check for existing or related reports before submitting new issues, so that we can avoid duplicate bug reports as much as possible and make our reports more helpful to the project.

Thanks again for your time and support.