Description:
A YEAR() expression over a DATE column behaves inconsistently when used in a VIEW or CTE with UNION.
The expression:
YEAR(c2)
where c2 = '7041-03-08', should return the year value:
7041
However, in the VIEW and CTE forms, the query:
SELECT vc
FROM v0
WHERE vc > 1970
returns one row, but the displayed value is:
0000
This is inconsistent. If the value is 7041, then it satisfies vc > 1970, but it should be displayed as 7041. If the value is 0000, then it should not satisfy vc > 1970.
After the same expression is materialized into a TEMPORARY TABLE using CREATE TEMPORARY TABLE ... SELECT, the query returns an empty result set. This suggests that the value may have been materialized as a YEAR-like value 0000, while the VIEW/CTE form compares it as the original numeric year 7041 but displays it as 0000.
This appears to be a wrong-result issue involving YEAR(), UNION, VIEW/CTE result type derivation, and comparison/display semantics.
How to repeat:
DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c2 DATE
);
INSERT INTO t0 VALUES ('7041-03-08');
CREATE VIEW v0 AS
SELECT YEAR(c2) AS vc FROM t0
UNION
SELECT YEAR(c2) AS vc FROM t0 WHERE FALSE;
SELECT vc
FROM v0
WHERE vc > 1970;
WITH cte AS (
SELECT YEAR(c2) AS vc FROM t0
UNION
SELECT YEAR(c2) AS vc FROM t0 WHERE FALSE
)
SELECT vc
FROM cte
WHERE vc > 1970;
CREATE TEMPORARY TABLE tmp AS
SELECT YEAR(c2) AS vc FROM t0
UNION
SELECT YEAR(c2) AS vc FROM t0 WHERE FALSE;
SELECT vc
FROM tmp
WHERE vc > 1970;
VIEW:
+------+
| vc |
+------+
| 0000 |
+------+
CTE:
+------+
| vc |
+------+
| 0000 |
+------+
TEMPORARY TABLE:
Empty set
Description: A YEAR() expression over a DATE column behaves inconsistently when used in a VIEW or CTE with UNION. The expression: YEAR(c2) where c2 = '7041-03-08', should return the year value: 7041 However, in the VIEW and CTE forms, the query: SELECT vc FROM v0 WHERE vc > 1970 returns one row, but the displayed value is: 0000 This is inconsistent. If the value is 7041, then it satisfies vc > 1970, but it should be displayed as 7041. If the value is 0000, then it should not satisfy vc > 1970. After the same expression is materialized into a TEMPORARY TABLE using CREATE TEMPORARY TABLE ... SELECT, the query returns an empty result set. This suggests that the value may have been materialized as a YEAR-like value 0000, while the VIEW/CTE form compares it as the original numeric year 7041 but displays it as 0000. This appears to be a wrong-result issue involving YEAR(), UNION, VIEW/CTE result type derivation, and comparison/display semantics. How to repeat: DROP VIEW IF EXISTS v0; DROP TEMPORARY TABLE IF EXISTS tmp; DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c2 DATE ); INSERT INTO t0 VALUES ('7041-03-08'); CREATE VIEW v0 AS SELECT YEAR(c2) AS vc FROM t0 UNION SELECT YEAR(c2) AS vc FROM t0 WHERE FALSE; SELECT vc FROM v0 WHERE vc > 1970; WITH cte AS ( SELECT YEAR(c2) AS vc FROM t0 UNION SELECT YEAR(c2) AS vc FROM t0 WHERE FALSE ) SELECT vc FROM cte WHERE vc > 1970; CREATE TEMPORARY TABLE tmp AS SELECT YEAR(c2) AS vc FROM t0 UNION SELECT YEAR(c2) AS vc FROM t0 WHERE FALSE; SELECT vc FROM tmp WHERE vc > 1970; VIEW: +------+ | vc | +------+ | 0000 | +------+ CTE: +------+ | vc | +------+ | 0000 | +------+ TEMPORARY TABLE: Empty set