Description:
Description:
A QUOTE() expression over a NULL value produced by a LEFT JOIN returns the string 'NULL', but a WHERE predicate comparing the expression with the string literal 'NULL' incorrectly filters out the row when the expression is used through a VIEW or CTE.
The same expression materialized into a TEMPORARY TABLE behaves correctly.
The issue appears to involve VIEW/CTE handling, QUOTE(NULL), LEFT JOIN null-complemented rows, and predicate evaluation or optimization.
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 VALUES (1);
CREATE VIEW v0 AS
SELECT QUOTE(b.c0) AS vc
FROM t0 AS a
LEFT JOIN t0 AS b
ON FALSE;
SELECT vc
FROM v0
WHERE vc = 'NULL';
WITH cte AS (
SELECT QUOTE(b.c0) AS vc
FROM t0 AS a
LEFT JOIN t0 AS b
ON FALSE
)
SELECT vc
FROM cte
WHERE vc = 'NULL';
CREATE TEMPORARY TABLE tmp AS
SELECT QUOTE(b.c0) AS vc
FROM t0 AS a
LEFT JOIN t0 AS b
ON FALSE;
SELECT vc
FROM tmp
WHERE vc = 'NULL';
Actual result:
The VIEW query returns an empty result set:
SELECT vc FROM v0 WHERE vc = 'NULL';
Empty set
The CTE query also returns an empty result set:
WITH cte AS (
SELECT QUOTE(b.c0) AS vc
FROM t0 AS a
LEFT JOIN t0 AS b
ON FALSE
)
SELECT vc FROM cte WHERE vc = 'NULL';
Empty set
The TEMPORARY TABLE query returns one row:
SELECT vc FROM tmp WHERE vc = 'NULL';
+------+
| vc |
+------+
| NULL |
+------+
Description: Description: A QUOTE() expression over a NULL value produced by a LEFT JOIN returns the string 'NULL', but a WHERE predicate comparing the expression with the string literal 'NULL' incorrectly filters out the row when the expression is used through a VIEW or CTE. The same expression materialized into a TEMPORARY TABLE behaves correctly. The issue appears to involve VIEW/CTE handling, QUOTE(NULL), LEFT JOIN null-complemented rows, and predicate evaluation or optimization. How to repeat: DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE t0 (c0 INT); INSERT INTO t0 VALUES (1); CREATE VIEW v0 AS SELECT QUOTE(b.c0) AS vc FROM t0 AS a LEFT JOIN t0 AS b ON FALSE; SELECT vc FROM v0 WHERE vc = 'NULL'; WITH cte AS ( SELECT QUOTE(b.c0) AS vc FROM t0 AS a LEFT JOIN t0 AS b ON FALSE ) SELECT vc FROM cte WHERE vc = 'NULL'; CREATE TEMPORARY TABLE tmp AS SELECT QUOTE(b.c0) AS vc FROM t0 AS a LEFT JOIN t0 AS b ON FALSE; SELECT vc FROM tmp WHERE vc = 'NULL'; Actual result: The VIEW query returns an empty result set: SELECT vc FROM v0 WHERE vc = 'NULL'; Empty set The CTE query also returns an empty result set: WITH cte AS ( SELECT QUOTE(b.c0) AS vc FROM t0 AS a LEFT JOIN t0 AS b ON FALSE ) SELECT vc FROM cte WHERE vc = 'NULL'; Empty set The TEMPORARY TABLE query returns one row: SELECT vc FROM tmp WHERE vc = 'NULL'; +------+ | vc | +------+ | NULL | +------+