Description:
The attached test case shows that a VIEW and an equivalent CTE return different results, even though they appear to be semantically equivalent.
The test inserts a DATETIME value of '2345-03-17 14:34:49'. This value is valid for DATETIME, since MySQL documents the supported DATETIME range as 1000-01-01 00:00:00 to 9999-12-31 23:59:59. MySQL also documents the YEAR data type range as 1901 to 2155, and 0000. Thus, 2345 is valid as the year part of a DATETIME, but it is outside the valid range of the YEAR data type.
In the testcase, both the VIEW and the CTE compute YEAR(c0) inside a UNION, and then the resulting relation is referenced twice in a self-join. The VIEW query returns one row containing 0000, 0000, while the equivalent CTE query returns an empty set. These results are inconsistent with each other.
The VIEW version returns:
+------+------+
| vc_0 | vc_0 |
+------+------+
| 0000 | 0000 |
+------+------+
while the equivalent CTE version returns:
Empty set
A possible explanation is that MySQL is handling the result type or materialization of YEAR(c0) inconsistently between VIEW and CTE. MySQL documents that derived tables, view references, and CTEs may be optimized using merge or materialization, and that if a CTE is materialized and referenced multiple times, it is materialized once and reused. MySQL also documents that conditions are not pushed down to a materialized CTE if it is referenced multiple times.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
DROP TABLE IF EXISTS t0;
DROP VIEW IF EXISTS v0;
CREATE TABLE `t0` (
`c0` DATETIME
);
INSERT INTO `t0` (`c0`) VALUES ('2345-03-17 14:34:49');
CREATE VIEW `v0` AS
SELECT YEAR(`c0`) AS `vc_0` FROM `t0` UNION SELECT YEAR(`c0`) AS `vc_0` FROM `t0`;
SELECT * FROM `v0` AS `a` JOIN `v0` AS `b` ON `a`.`vc_0` = `b`.`vc_0` WHERE `a`.`vc_0` > 0;
WITH CTE AS (
SELECT YEAR(`c0`) AS `vc_0` FROM `t0` UNION SELECT YEAR(`c0`) AS `vc_0` FROM `t0` )
SELECT * FROM `CTE` AS `a` JOIN `CTE` AS `b` ON `a`.`vc_0` = `b`.`vc_0` WHERE `a`.`vc_0` > 0;
VIEW RESULT:
+------+------+
| vc_0 | vc_0 |
+------+------+
| 0000 | 0000 |
+------+------+
CTE RESULT:
Empty set
Description: The attached test case shows that a VIEW and an equivalent CTE return different results, even though they appear to be semantically equivalent. The test inserts a DATETIME value of '2345-03-17 14:34:49'. This value is valid for DATETIME, since MySQL documents the supported DATETIME range as 1000-01-01 00:00:00 to 9999-12-31 23:59:59. MySQL also documents the YEAR data type range as 1901 to 2155, and 0000. Thus, 2345 is valid as the year part of a DATETIME, but it is outside the valid range of the YEAR data type. In the testcase, both the VIEW and the CTE compute YEAR(c0) inside a UNION, and then the resulting relation is referenced twice in a self-join. The VIEW query returns one row containing 0000, 0000, while the equivalent CTE query returns an empty set. These results are inconsistent with each other. The VIEW version returns: +------+------+ | vc_0 | vc_0 | +------+------+ | 0000 | 0000 | +------+------+ while the equivalent CTE version returns: Empty set A possible explanation is that MySQL is handling the result type or materialization of YEAR(c0) inconsistently between VIEW and CTE. MySQL documents that derived tables, view references, and CTEs may be optimized using merge or materialization, and that if a CTE is materialized and referenced multiple times, it is materialized once and reused. MySQL also documents that conditions are not pushed down to a materialized CTE if it is referenced multiple times. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; DROP TABLE IF EXISTS t0; DROP VIEW IF EXISTS v0; CREATE TABLE `t0` ( `c0` DATETIME ); INSERT INTO `t0` (`c0`) VALUES ('2345-03-17 14:34:49'); CREATE VIEW `v0` AS SELECT YEAR(`c0`) AS `vc_0` FROM `t0` UNION SELECT YEAR(`c0`) AS `vc_0` FROM `t0`; SELECT * FROM `v0` AS `a` JOIN `v0` AS `b` ON `a`.`vc_0` = `b`.`vc_0` WHERE `a`.`vc_0` > 0; WITH CTE AS ( SELECT YEAR(`c0`) AS `vc_0` FROM `t0` UNION SELECT YEAR(`c0`) AS `vc_0` FROM `t0` ) SELECT * FROM `CTE` AS `a` JOIN `CTE` AS `b` ON `a`.`vc_0` = `b`.`vc_0` WHERE `a`.`vc_0` > 0; VIEW RESULT: +------+------+ | vc_0 | vc_0 | +------+------+ | 0000 | 0000 | +------+------+ CTE RESULT: Empty set