Description:
Description:
An equivalent VIEW and CTE return different results.
The VIEW and CTE are defined by the same query:
SELECT YEAR(0) AS d
UNION
SELECT YEAR(b) FROM a
Logically, both should produce the same result set. However, when the result is referenced again inside a scalar subquery, the VIEW query returns one row, while the equivalent CTE query returns an empty set.
This appears to be an incorrect result bug, possibly related to UNION result type inference, YEAR(0), materialization, or condition pushdown for VIEW/CTE repeated references.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
1 row in set (0.00 sec)
DROP DATABASE IF EXISTS `dd`;
CREATE DATABASE `dd`;
USE `dd`;
CREATE TABLE a (
b DATE
);
INSERT a VALUES ('7060-05-09');
CREATE VIEW `v0` AS
SELECT YEAR(0) d UNION SELECT YEAR(b) FROM a
;
SELECT d FROM v0 WHERE (SELECT 1 FROM v0 WHERE d )
;
WITH CTE AS (
SELECT YEAR(0) d UNION SELECT YEAR(b) FROM a
)
SELECT d FROM CTE WHERE (SELECT 1 FROM CTE WHERE d )
;
VIEW RESULT:
+------+
| d |
+------+
| 0000 |
+------+
CTE RESULT:
Empty set
Description: Description: An equivalent VIEW and CTE return different results. The VIEW and CTE are defined by the same query: SELECT YEAR(0) AS d UNION SELECT YEAR(b) FROM a Logically, both should produce the same result set. However, when the result is referenced again inside a scalar subquery, the VIEW query returns one row, while the equivalent CTE query returns an empty set. This appears to be an incorrect result bug, possibly related to UNION result type inference, YEAR(0), materialization, or condition pushdown for VIEW/CTE repeated references. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ 1 row in set (0.00 sec) DROP DATABASE IF EXISTS `dd`; CREATE DATABASE `dd`; USE `dd`; CREATE TABLE a ( b DATE ); INSERT a VALUES ('7060-05-09'); CREATE VIEW `v0` AS SELECT YEAR(0) d UNION SELECT YEAR(b) FROM a ; SELECT d FROM v0 WHERE (SELECT 1 FROM v0 WHERE d ) ; WITH CTE AS ( SELECT YEAR(0) d UNION SELECT YEAR(b) FROM a ) SELECT d FROM CTE WHERE (SELECT 1 FROM CTE WHERE d ) ; VIEW RESULT: +------+ | d | +------+ | 0000 | +------+ CTE RESULT: Empty set