Description:
A query using a VIEW returns a row, while the equivalent query using a CTE returns an empty result set. The CTE result appears to be correct.
The issue seems related to VIEW handling of JSON_TYPE() result collation, LOWER(), GROUP BY, COUNT(DISTINCT), and an outer UNION.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (
b JSON,
c FLOAT
);
INSERT INTO a VALUES ('"WIromLCWVEYJ10t"', 0);
CREATE VIEW v0 AS
SELECT
c AS d,
JSON_TYPE(b) AS e,
COUNT(DISTINCT 1) AS f
FROM a
GROUP BY d, e;
SELECT e
FROM v0
WHERE d
UNION
SELECT e
FROM v0
WHERE f AND LOWER(e) = e OR 0;
WITH cte AS (
SELECT
c AS d,
JSON_TYPE(b) AS e,
COUNT(DISTINCT 1) AS f
FROM a
GROUP BY d, e
)
SELECT e
FROM cte
WHERE d
UNION
SELECT e
FROM cte
WHERE f AND LOWER(e) = e OR 0;
The VIEW query returns one row:
+--------+
| e |
+--------+
| STRING |
+--------+
The equivalent CTE query returns an empty result set:
Empty set
Description: A query using a VIEW returns a row, while the equivalent query using a CTE returns an empty result set. The CTE result appears to be correct. The issue seems related to VIEW handling of JSON_TYPE() result collation, LOWER(), GROUP BY, COUNT(DISTINCT), and an outer UNION. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE a ( b JSON, c FLOAT ); INSERT INTO a VALUES ('"WIromLCWVEYJ10t"', 0); CREATE VIEW v0 AS SELECT c AS d, JSON_TYPE(b) AS e, COUNT(DISTINCT 1) AS f FROM a GROUP BY d, e; SELECT e FROM v0 WHERE d UNION SELECT e FROM v0 WHERE f AND LOWER(e) = e OR 0; WITH cte AS ( SELECT c AS d, JSON_TYPE(b) AS e, COUNT(DISTINCT 1) AS f FROM a GROUP BY d, e ) SELECT e FROM cte WHERE d UNION SELECT e FROM cte WHERE f AND LOWER(e) = e OR 0; The VIEW query returns one row: +--------+ | e | +--------+ | STRING | +--------+ The equivalent CTE query returns an empty result set: Empty set