Wrong result with QUOTE(NULL) in VIEW/CTE WHERE predicate

I found a wrong-result issue involving QUOTE(), LEFT JOIN, VIEW/CTE expansion, and an outer comparison predicate.

The same query returns different results depending on whether QUOTE(b.c0) is evaluated through a VIEW, a CTE, or a TEMPORARY TABLE.

The VIEW and CTE versions return an empty set, while the equivalent TEMPORARY TABLE version returns one row with value NULL.

This looks incorrect because the LEFT JOIN uses ON FALSE, so b.c0 is a NULL-complemented column. QUOTE(NULL) should return the string 'NULL'. Therefore, the outer predicate vc = 'NULL' should match the row.

Environment:

MariaDB version:
12.2.2-MariaDB-ubu2404

How to repeat:

DROP VIEW IF EXISTS v0;

DROP TEMPORARY TABLE IF EXISTS tmp;

DROP TABLE IF EXISTS t0;

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:

Empty set

The CTE query returns:

Empty set

The TEMPORARY TABLE query returns:

+------+

| vc   |

+------+

| NULL |

+------+

Expected result:

The VIEW and CTE queries should return the same result as the TEMPORARY TABLE query:

+------+

| vc   |

+------+

| NULL |

+------+

Reason:

The base table contains one row:

1

The query uses:

LEFT JOIN t0 AS b ON FALSE

Therefore, the row from table a is preserved, and columns from table b are NULL-complemented. In particular:

b.c0 = NULL

The SELECT expression is:

QUOTE(b.c0) AS vc

QUOTE(NULL) should return the string value:

NULL

Therefore, the outer predicate:

WHERE vc = 'NULL'

should match this row.

The TEMPORARY TABLE version confirms this expected result. However, the VIEW and CTE versions return an empty set.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT vc FROM v0 WHERE vc = 'NULL'

2. CTE:
WITH cte AS (...) SELECT vc FROM cte WHERE vc = 'NULL'

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE tmp AS ...;
SELECT vc FROM tmp WHERE vc = 'NULL'

However, the VIEW and CTE versions return an empty set, while the TEMPORARY TABLE version returns the expected row.

This suggests a wrong-result bug involving QUOTE(), LEFT JOIN null-complemented rows, VIEW/CTE expansion, predicate pushdown, or NULL/string literal handling.