Description:
There is a query result inconsistency when using a VIEW versus a CTE (Common Table Expression). Both the VIEW and the CTE contain the exact same logic (an EXISTS subquery), and both are queried using a LEFT JOIN on themselves.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
DROP TABLE IF EXISTS t0;
DROP VIEW IF EXISTS v0;
CREATE TABLE t0 ( c1 SET('a'), c3 GEOMETRY);
INSERT INTO t0 VALUES ('a', NULL);
INSERT INTO t0 VALUES('a', ST_GeomFromText('POINT(0 0)'));
INSERT INTO t0 VALUES('', NULL);
CREATE VIEW v0 AS
SELECT 1 AS vc_0 FROM t0 AS o
WHERE EXISTS (SELECT 1 FROM t0 AS i WHERE BINARY o.c1 = BINARY i.c1
AND i.c3 IS NOT NULL);
SELECT COUNT(*) AS cnt FROM v0 AS a LEFT JOIN v0 AS b ON a.vc_0 <=> b.vc_0; -- 6
WITH cte AS (
SELECT 1 AS vc_0 FROM t0 AS o
WHERE EXISTS (SELECT 1 FROM t0 AS i WHERE BINARY o.c1 = BINARY i.c1
AND i.c3 IS NOT NULL))
SELECT COUNT(*) AS cnt FROM cte AS a LEFT JOIN cte AS b ON a.vc_0 <=> b.vc_0; -- 4
VIEW RESULT:
+-----+
| cnt |
+-----+
| 6 |
+-----+
CTE RESULT:
+-----+
| cnt |
+-----+
| 4 |
+-----+
Description: There is a query result inconsistency when using a VIEW versus a CTE (Common Table Expression). Both the VIEW and the CTE contain the exact same logic (an EXISTS subquery), and both are queried using a LEFT JOIN on themselves. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; DROP TABLE IF EXISTS t0; DROP VIEW IF EXISTS v0; CREATE TABLE t0 ( c1 SET('a'), c3 GEOMETRY); INSERT INTO t0 VALUES ('a', NULL); INSERT INTO t0 VALUES('a', ST_GeomFromText('POINT(0 0)')); INSERT INTO t0 VALUES('', NULL); CREATE VIEW v0 AS SELECT 1 AS vc_0 FROM t0 AS o WHERE EXISTS (SELECT 1 FROM t0 AS i WHERE BINARY o.c1 = BINARY i.c1 AND i.c3 IS NOT NULL); SELECT COUNT(*) AS cnt FROM v0 AS a LEFT JOIN v0 AS b ON a.vc_0 <=> b.vc_0; -- 6 WITH cte AS ( SELECT 1 AS vc_0 FROM t0 AS o WHERE EXISTS (SELECT 1 FROM t0 AS i WHERE BINARY o.c1 = BINARY i.c1 AND i.c3 IS NOT NULL)) SELECT COUNT(*) AS cnt FROM cte AS a LEFT JOIN cte AS b ON a.vc_0 <=> b.vc_0; -- 4 VIEW RESULT: +-----+ | cnt | +-----+ | 6 | +-----+ CTE RESULT: +-----+ | cnt | +-----+ | 4 | +-----+