Bug #120836 Wrong result with VIEW, UNION, MAKETIME(), MEDIUMTEXT and NOT IN subquery containing NULL

• Submitted: 2 Jul 15:08
• Modified: 2 Jul 19:35
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6.0
• OS: Ubuntu
• CPU Architecture: Any

[2 Jul 15:08] Xiaoyuan Xie

Description:
A query using a VIEW returns a row although the equivalent CTE query returns an empty result set. The CTE result appears to be correct because the NOT IN subquery contains NULL, so the predicate should evaluate to UNKNOWN and no rows should be returned.

This looks like a wrong-result bug involving VIEW materialization/merge or optimizer handling of NOT IN with NULL.

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

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

CREATE TABLE a (
  b MEDIUMTEXT
);

INSERT INTO a VALUES ();

CREATE VIEW v0 AS
SELECT MAKETIME(0, 0, 0) AS c
UNION
SELECT MAKETIME(0, 0, b) FROM a;

SELECT 1
FROM v0
WHERE c NOT IN (
  SELECT c FROM v0 WHERE c IS NULL
);

WITH cte AS (
  SELECT MAKETIME(0, 0, 0) AS c
  UNION
  SELECT MAKETIME(0, 0, b) FROM a
)
SELECT 1
FROM cte
WHERE c NOT IN (
  SELECT c FROM cte WHERE c IS NULL
);

Actual result:

VIEW query:
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set 

CTE query:
Empty set

[2 Jul 15:15] Xiaoyuan Xie

It may be an optimization problem

[2 Jul 19:35] Roy Lyseng

Thank you for the bug report.
Verified as described.