Description:
A query using a VIEW returns a different result from an equivalent query using a CTE.
The query involves:
GROUP_CONCAT()
JSON_QUOTE()
HEX()
LOWER()
a self JOIN
VIEW vs CTE execution
The VIEW query returns 1 row, while the equivalent CTE query returns an empty set.
In addition, the VIEW query returns different values for the same VIEW expression in the joined aliases: a.g is returned as "5b", while b.g is returned as "5B".
This appears to be a wrong-result bug, possibly related to VIEW expansion, expression substitution, aggregate evaluation, case conversion, or optimizer handling of predicates involving LOWER().
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE c (
c2 CHAR(1)
);
INSERT INTO c VALUES ('[');
CREATE OR REPLACE VIEW v0 AS
SELECT
GROUP_CONCAT(JSON_QUOTE(HEX(c2))) AS g
FROM c;
-- Query using VIEW
SELECT *
FROM v0 AS a
JOIN v0 AS b
WHERE LOWER(a.g) = a.g;
-- Equivalent query using CTE
WITH CTE AS (
SELECT
GROUP_CONCAT(JSON_QUOTE(HEX(c2))) AS g
FROM c
)
SELECT *
FROM CTE AS a
JOIN CTE AS b
WHERE LOWER(a.g) = a.g;
Actual result
The VIEW query returns 1 row:
+------+------+
| g | g |
+------+------+
| "5b" | "5B" |
+------+------+
1 row in set
The equivalent CTE query returns an empty set:
Empty set
Expected result
Both queries should return the same result.
The expected result appears to be an empty set, matching the CTE query.
Suggested fix:
Reasoning
The base table contains one row:
c2
--
[
The VIEW/CTE body is:
SELECT
GROUP_CONCAT(JSON_QUOTE(HEX(c2))) AS g
FROM c
For the value '[', HEX(c2) produces:
5B
Therefore the VIEW/CTE result should contain one row:
g
----
"5B"
The outer query is:
SELECT *
FROM v0 AS a
JOIN v0 AS b
WHERE LOWER(a.g) = a.g;
The predicate compares LOWER(a.g) with a.g.
For a.g = '"5B"', LOWER(a.g) is '"5b"'. Under the observed behavior, this predicate does not evaluate to TRUE in the equivalent CTE query, so the expected result appears to be an empty set.
The VIEW query, however, returns one row and also changes the displayed value of a.g from "5B" to "5b" while b.g remains "5B".
This is unexpected because both a.g and b.g come from the same VIEW definition and should represent the same aggregate expression result.
Therefore, the VIEW query appears to be incorrectly substituting or reusing the LOWER(a.g) expression for the selected a.g column, causing both an incorrect row to pass the filter and an incorrect projected value.
Notes
The issue is deterministic and reproducible with the SQL test case above.
The VIEW and CTE queries are logically equivalent and should not return different result sets.
Description: A query using a VIEW returns a different result from an equivalent query using a CTE. The query involves: GROUP_CONCAT() JSON_QUOTE() HEX() LOWER() a self JOIN VIEW vs CTE execution The VIEW query returns 1 row, while the equivalent CTE query returns an empty set. In addition, the VIEW query returns different values for the same VIEW expression in the joined aliases: a.g is returned as "5b", while b.g is returned as "5B". This appears to be a wrong-result bug, possibly related to VIEW expansion, expression substitution, aggregate evaluation, case conversion, or optimizer handling of predicates involving LOWER(). How to repeat: DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE c ( c2 CHAR(1) ); INSERT INTO c VALUES ('['); CREATE OR REPLACE VIEW v0 AS SELECT GROUP_CONCAT(JSON_QUOTE(HEX(c2))) AS g FROM c; -- Query using VIEW SELECT * FROM v0 AS a JOIN v0 AS b WHERE LOWER(a.g) = a.g; -- Equivalent query using CTE WITH CTE AS ( SELECT GROUP_CONCAT(JSON_QUOTE(HEX(c2))) AS g FROM c ) SELECT * FROM CTE AS a JOIN CTE AS b WHERE LOWER(a.g) = a.g; Actual result The VIEW query returns 1 row: +------+------+ | g | g | +------+------+ | "5b" | "5B" | +------+------+ 1 row in set The equivalent CTE query returns an empty set: Empty set Expected result Both queries should return the same result. The expected result appears to be an empty set, matching the CTE query. Suggested fix: Reasoning The base table contains one row: c2 -- [ The VIEW/CTE body is: SELECT GROUP_CONCAT(JSON_QUOTE(HEX(c2))) AS g FROM c For the value '[', HEX(c2) produces: 5B Therefore the VIEW/CTE result should contain one row: g ---- "5B" The outer query is: SELECT * FROM v0 AS a JOIN v0 AS b WHERE LOWER(a.g) = a.g; The predicate compares LOWER(a.g) with a.g. For a.g = '"5B"', LOWER(a.g) is '"5b"'. Under the observed behavior, this predicate does not evaluate to TRUE in the equivalent CTE query, so the expected result appears to be an empty set. The VIEW query, however, returns one row and also changes the displayed value of a.g from "5B" to "5b" while b.g remains "5B". This is unexpected because both a.g and b.g come from the same VIEW definition and should represent the same aggregate expression result. Therefore, the VIEW query appears to be incorrectly substituting or reusing the LOWER(a.g) expression for the selected a.g column, causing both an incorrect row to pass the filter and an incorrect projected value. Notes The issue is deterministic and reproducible with the SQL test case above. The VIEW and CTE queries are logically equivalent and should not return different result sets.