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

Bug #120843	VIEW and CTE return wrong results with YEAR(), UNION and uncorrelated EXISTS subquery
Submitted:	2 Jul 16:43	Modified:	3 Jul 9:14
Reporter:	Xiaoyuan Xie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.6.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

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 |
+------+

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.

Not reproducible anymore with 9.7:

mysql> select version()
    -> ;
+-----------+
| version() |
+-----------+
| 9.7.0     |
+-----------+
1 row in set (0.000 sec)

mysql> DROP DATABASE IF EXISTS dd;
Query OK, 0 rows affected, 1 warning (0.001 sec)

mysql> CREATE DATABASE dd;
Query OK, 1 row affected (0.001 sec)

mysql> USE dd;
Database changed
mysql> 
mysql> CREATE TABLE a (b DATE);
Query OK, 0 rows affected (0.005 sec)

mysql> 
mysql> INSERT INTO a VALUES ('3651-04-29');
Query OK, 1 row affected (0.001 sec)

mysql> 
mysql> CREATE VIEW v0 AS
    -> SELECT YEAR(b) AS g
    -> FROM a
    -> UNION
    -> SELECT YEAR(0);
Query OK, 0 rows affected (0.001 sec)

mysql> 
mysql> SELECT g
    -> FROM v0
    -> WHERE EXISTS (
    ->   SELECT 0
    ->   FROM v0
    ->   WHERE g
    -> );
+------+
| g    |
+------+
| 0000 |
| 2000 |
+------+
2 rows in set (0.001 sec)

mysql> 
mysql> 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
    -> );
+------+
| g    |
+------+
| 0000 |
| 2000 |
+------+
2 rows in set (0.001 sec)

mysql>

I verified it because results are still wrong.

Thank you Roy and Georgi for checking this.

I understand that the VIEW and CTE results are now consistent in 9.7.0. However, as Roy pointed out, the result still appears to be wrong because `3651` is outside the value range of the `YEAR` data type.

My understanding is that `YEAR(b)` for the valid `DATE` value `'3651-04-29'` should produce the numeric year `3651`, but after the `UNION` the result seems to be coerced into a `YEAR`-like type, leading to unexpected values such as `0000` / `2000`.

Thanks again for verifying the bug and for the clarification.