Description:
Materializing an aggregated `TIME` value as a view changes a zero datetime value into a runtime timestamp when the value is later combined with a `DATE` branch through `UNION ALL`. The transformed query should preserve the original zero datetime row, but instead returns a row with a different datetime value.
How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;
CREATE TABLE t3 (
c5 TIME
);
INSERT INTO t3 VALUES ('00:00:00');
SELECT MIN(cuc28.c5) AS col_1
FROM t3 AS cuc28
UNION ALL
SELECT DATE('2025-01-01') AS col_1;
CREATE VIEW V_cuc28_fe3c0f30 AS
SELECT MIN(cuc28.c5) AS col_1
FROM t3 AS cuc28;
SELECT V_cuc28_fe3c0f30.col_1 AS col_1
FROM V_cuc28_fe3c0f30
UNION ALL
SELECT DATE('2025-01-01') AS col_1;
Original result:
+---------------------+
| col_1 |
+---------------------+
| 0000-00-00 00:00:00 |
| 2025-01-01 00:00:00 |
+---------------------+
After create view, result:
+---------------------+
| col_1 |
+---------------------+
| 2026-04-13 00:00:00 |
| 2025-01-01 00:00:00 |
+---------------------+
Description: Materializing an aggregated `TIME` value as a view changes a zero datetime value into a runtime timestamp when the value is later combined with a `DATE` branch through `UNION ALL`. The transformed query should preserve the original zero datetime row, but instead returns a row with a different datetime value. How to repeat: DROP DATABASE IF EXISTS t1; CREATE DATABASE t1; USE t1; CREATE TABLE t3 ( c5 TIME ); INSERT INTO t3 VALUES ('00:00:00'); SELECT MIN(cuc28.c5) AS col_1 FROM t3 AS cuc28 UNION ALL SELECT DATE('2025-01-01') AS col_1; CREATE VIEW V_cuc28_fe3c0f30 AS SELECT MIN(cuc28.c5) AS col_1 FROM t3 AS cuc28; SELECT V_cuc28_fe3c0f30.col_1 AS col_1 FROM V_cuc28_fe3c0f30 UNION ALL SELECT DATE('2025-01-01') AS col_1; Original result: +---------------------+ | col_1 | +---------------------+ | 0000-00-00 00:00:00 | | 2025-01-01 00:00:00 | +---------------------+ After create view, result: +---------------------+ | col_1 | +---------------------+ | 2026-04-13 00:00:00 | | 2025-01-01 00:00:00 | +---------------------+