Bug #120843 VIEW and CTE return wrong results with YEAR(), UNION and uncorrelated EXISTS subquery

• Submitted: 2 Jul 16:43
• Modified: 2 Jul 20:33
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6.0
• OS: Ubuntu
• CPU Architecture: Any

[2 Jul 16:43] Xiaoyuan Xie

Description:
A query using a VIEW and an equivalent query using a CTE return different results. Both results appear to be wrong.

The VIEW query returns only 0000, while the CTE query returns an empty result set. Based on the semantics of YEAR(), UNION, and EXISTS, the expected result should contain both 3651 and 0000.

This looks like a wrong-result bug involving YEAR(), UNION, VIEW/CTE handling, and an uncorrelated EXISTS subquery.

How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0     |
+-----------+

DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;

CREATE TABLE a (b DATE);

INSERT INTO a VALUES ('3651-04-29');

CREATE VIEW v0 AS
SELECT YEAR(b) AS g
FROM a
UNION
SELECT YEAR(0);

SELECT g
FROM v0
WHERE EXISTS (
  SELECT 0
  FROM v0
  WHERE g
);

WITH cte AS (
  SELECT YEAR(b) AS g
  FROM a
  UNION
  SELECT YEAR(0)
)
SELECT g
FROM cte
WHERE EXISTS (
  SELECT 0
  FROM cte
  WHERE g
);

Actual result:

The VIEW query returns:

+------+
| g    |
+------+
| 0000 |
+------+
The equivalent CTE query returns:

Empty set
Expected result:

Both queries should return:

+------+
| g    |
+------+
| 3651 |
| 0000 |
+------+

[2 Jul 20:33] Roy Lyseng

Thank you for the bug report.
Results seems consistent in release 9.7, though.
Still, I verify this bug since 3651 is outside the value range for data type YEAR.