Bug #120844 Wrong result with VIEW, DAYNAME(), UNION and NOT IN subquery

• Submitted: 2 Jul 16:47
• Modified: 3 Jul 16:21
• Reporter: Xiaoyuan Xie
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6.0
• OS: Ubuntu
• CPU Architecture: Any

[2 Jul 16:47] Xiaoyuan Xie

Description:
A query using a VIEW returns an empty result set, while an equivalent query using a CTE returns two rows. The CTE result appears to be correct.

The issue seems related to VIEW handling of DAYNAME(), UNION, string-to-number conversion in a boolean predicate, and a NOT IN subquery.

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

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

CREATE TABLE t0 (
  c1 VARCHAR(1),
  c3 DATE
);

INSERT INTO t0 VALUES ('x', '7056-10-15');

CREATE VIEW v0 AS
SELECT '' AS vc_0, DAYNAME(c3) AS vc_1
FROM t0

UNION

SELECT c1 AS vc_0, DAYNAME(c3) AS vc_1
FROM t0;

SELECT vc_0 AS out_col
FROM v0
WHERE vc_0 NOT IN (
  SELECT vc_0
  FROM v0
  WHERE vc_1
);

WITH cte AS (
  SELECT '' AS vc_0, DAYNAME(c3) AS vc_1
  FROM t0

  UNION

  SELECT c1 AS vc_0, DAYNAME(c3) AS vc_1
  FROM t0
)
SELECT vc_0 AS out_col
FROM cte
WHERE vc_0 NOT IN (
  SELECT vc_0
  FROM cte
  WHERE vc_1
);

Actual result:

The VIEW query returns:

Empty set
The equivalent CTE query returns:

+---------+
| out_col |
+---------+
|         |
| x       |
+---------+

[3 Jul 0:05] Roy Lyseng

Thank you for the bug report.
This problem was fixed in 9.7 by Worklog 16895, thus I am closing the report as not reproducible.

[3 Jul 16:21] Xiaoyuan Xie

Thank you Roy for checking this.

I understand that this issue was fixed in 9.7 by Worklog 16895, so it is no longer reproducible in the current version. I will verify the test case again on 9.7.

Thanks for confirming the fix.