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 |
+-----------+-----+
