Description:
MySQL 9.6.0 returns different results for two logically equivalent UNION ALL queries when an aggregate over a TIME column is extracted into a view.
The direct query computes MIN(c5) from a filtered table and unions that result with a date expression. The transformed query first materializes the same MIN(c5) expression in a view and then performs the same UNION ALL. These two queries should be semantically equivalent, but they return different first-row values.
In my local reproduction, the original query returned 0000-00-00 00:00:00 as the first row, while the view-based form returned 2026-04-21 12:34:56. The date 2026-04-21 matched the session’s CURDATE() at execution time, suggesting that the view-based path incorrectly combines the current date with the TIME value.
How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;
CREATE TABLE t3 (
pk SMALLINT NOT NULL,
c5 TIME,
category CHAR(2) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t3 VALUES (1, '12:34:56', 'ok');
SELECT MIN(cuc28.c5) AS col_1
FROM t3 AS cuc28
WHERE category = 'ok'
UNION ALL
SELECT DATE_ADD(DATE('2025-03-14'), INTERVAL 0 SECOND) AS col_1;
CREATE VIEW V_cuc28_alt AS
SELECT MIN(cuc28.c5) AS col_1
FROM t3 AS cuc28
WHERE category = 'ok';
SELECT V_cuc28_alt.col_1 AS col_1
FROM V_cuc28_alt
UNION ALL
SELECT DATE_ADD(DATE('2025-03-14'), INTERVAL 0 SECOND) AS col_1;
Expected result
Both queries should return the same result set. At minimum, the first row should not change after view extraction.
Observed original result on my system:
+---------------------+
| col_1 |
+---------------------+
| 0000-00-00 00:00:00 |
| 2025-03-14 00:00:00 |
+---------------------+
Actual result:
+---------------------+
| col_1 |
+---------------------+
| 2026-04-21 12:34:56 |
| 2025-03-14 00:00:00 |
+---------------------+
Description: MySQL 9.6.0 returns different results for two logically equivalent UNION ALL queries when an aggregate over a TIME column is extracted into a view. The direct query computes MIN(c5) from a filtered table and unions that result with a date expression. The transformed query first materializes the same MIN(c5) expression in a view and then performs the same UNION ALL. These two queries should be semantically equivalent, but they return different first-row values. In my local reproduction, the original query returned 0000-00-00 00:00:00 as the first row, while the view-based form returned 2026-04-21 12:34:56. The date 2026-04-21 matched the session’s CURDATE() at execution time, suggesting that the view-based path incorrectly combines the current date with the TIME value. How to repeat: DROP DATABASE IF EXISTS t1; CREATE DATABASE t1; USE t1; CREATE TABLE t3 ( pk SMALLINT NOT NULL, c5 TIME, category CHAR(2) NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t3 VALUES (1, '12:34:56', 'ok'); SELECT MIN(cuc28.c5) AS col_1 FROM t3 AS cuc28 WHERE category = 'ok' UNION ALL SELECT DATE_ADD(DATE('2025-03-14'), INTERVAL 0 SECOND) AS col_1; CREATE VIEW V_cuc28_alt AS SELECT MIN(cuc28.c5) AS col_1 FROM t3 AS cuc28 WHERE category = 'ok'; SELECT V_cuc28_alt.col_1 AS col_1 FROM V_cuc28_alt UNION ALL SELECT DATE_ADD(DATE('2025-03-14'), INTERVAL 0 SECOND) AS col_1; Expected result Both queries should return the same result set. At minimum, the first row should not change after view extraction. Observed original result on my system: +---------------------+ | col_1 | +---------------------+ | 0000-00-00 00:00:00 | | 2025-03-14 00:00:00 | +---------------------+ Actual result: +---------------------+ | col_1 | +---------------------+ | 2026-04-21 12:34:56 | | 2025-03-14 00:00:00 | +---------------------+