Description:
A query using a VIEW returns a different result from an equivalent query using a CTE.
The query involves:
GROUP_CONCAT()
JSON_QUOTE()
UPPER()
UNION
VIEW vs CTE execution
The VIEW query returns 2 rows, while the equivalent CTE query returns 1 row.
In the VIEW query, the second row appears as "A", even though the VIEW definition only selects GROUP_CONCAT(JSON_QUOTE(c3)), and the base value is 'a'.
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 UPPER().
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE t0 (
c3 TEXT
);
INSERT INTO t0 VALUES ('a');
CREATE OR REPLACE VIEW v0 AS
SELECT
GROUP_CONCAT(JSON_QUOTE(c3)) AS vc_0
FROM t0;
-- Query using VIEW
SELECT *
FROM v0
UNION
SELECT *
FROM v0
WHERE UPPER(vc_0) = vc_0;
-- Equivalent query using CTE
WITH CTE AS (
SELECT
GROUP_CONCAT(JSON_QUOTE(c3)) AS vc_0
FROM t0
)
SELECT *
FROM CTE
UNION
SELECT *
FROM CTE
WHERE UPPER(vc_0) = vc_0;
Actual result
The VIEW query returns 2 rows:
+------+
| vc_0 |
+------+
| "a" |
| "A" |
+------+
2 rows in set
The equivalent CTE query returns 1 row:
+------+
| vc_0 |
+------+
| "a" |
+------+
1 row in set
Expected result
Both queries should return the same result.
The expected result appears to be 1 row:
+------+
| vc_0 |
+------+
| "a" |
+------+
Suggested fix:
Reasoning
The base table contains one row:
c3
--
a
The VIEW/CTE body is:
SELECT
GROUP_CONCAT(JSON_QUOTE(c3)) AS vc_0
FROM t0
For the value 'a', JSON_QUOTE(c3) produces:
"a"
Therefore the VIEW/CTE result should contain one row:
vc_0
----
"a"
The outer query is:
SELECT *
FROM v0
UNION
SELECT *
FROM v0
WHERE UPPER(vc_0) = vc_0;
The first branch returns "a".
The second branch should not change the projected value. Even if the predicate UPPER(vc_0) = vc_0 is evaluated as TRUE under a case-insensitive collation, the selected column is still vc_0, not UPPER(vc_0). Therefore the second branch should also return "a", and the outer UNION should remove the duplicate.
The query should not return "A" because no branch selects UPPER(vc_0) as the output column.
The CTE query returns 1 row, which matches this reasoning. The VIEW query returns an extra row "A", which appears incorrect.
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.
This may be related to PS-11406, which also involves a wrong result with GROUP_CONCAT(), JSON_QUOTE(), case conversion, and VIEW expansion. However, this test case is not identical: PS-11406 uses LOWER() and a self JOIN, while this test case uses UPPER() and UNION.
Description: A query using a VIEW returns a different result from an equivalent query using a CTE. The query involves: GROUP_CONCAT() JSON_QUOTE() UPPER() UNION VIEW vs CTE execution The VIEW query returns 2 rows, while the equivalent CTE query returns 1 row. In the VIEW query, the second row appears as "A", even though the VIEW definition only selects GROUP_CONCAT(JSON_QUOTE(c3)), and the base value is 'a'. 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 UPPER(). How to repeat: DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE t0 ( c3 TEXT ); INSERT INTO t0 VALUES ('a'); CREATE OR REPLACE VIEW v0 AS SELECT GROUP_CONCAT(JSON_QUOTE(c3)) AS vc_0 FROM t0; -- Query using VIEW SELECT * FROM v0 UNION SELECT * FROM v0 WHERE UPPER(vc_0) = vc_0; -- Equivalent query using CTE WITH CTE AS ( SELECT GROUP_CONCAT(JSON_QUOTE(c3)) AS vc_0 FROM t0 ) SELECT * FROM CTE UNION SELECT * FROM CTE WHERE UPPER(vc_0) = vc_0; Actual result The VIEW query returns 2 rows: +------+ | vc_0 | +------+ | "a" | | "A" | +------+ 2 rows in set The equivalent CTE query returns 1 row: +------+ | vc_0 | +------+ | "a" | +------+ 1 row in set Expected result Both queries should return the same result. The expected result appears to be 1 row: +------+ | vc_0 | +------+ | "a" | +------+ Suggested fix: Reasoning The base table contains one row: c3 -- a The VIEW/CTE body is: SELECT GROUP_CONCAT(JSON_QUOTE(c3)) AS vc_0 FROM t0 For the value 'a', JSON_QUOTE(c3) produces: "a" Therefore the VIEW/CTE result should contain one row: vc_0 ---- "a" The outer query is: SELECT * FROM v0 UNION SELECT * FROM v0 WHERE UPPER(vc_0) = vc_0; The first branch returns "a". The second branch should not change the projected value. Even if the predicate UPPER(vc_0) = vc_0 is evaluated as TRUE under a case-insensitive collation, the selected column is still vc_0, not UPPER(vc_0). Therefore the second branch should also return "a", and the outer UNION should remove the duplicate. The query should not return "A" because no branch selects UPPER(vc_0) as the output column. The CTE query returns 1 row, which matches this reasoning. The VIEW query returns an extra row "A", which appears incorrect. 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. This may be related to PS-11406, which also involves a wrong result with GROUP_CONCAT(), JSON_QUOTE(), case conversion, and VIEW expansion. However, this test case is not identical: PS-11406 uses LOWER() and a self JOIN, while this test case uses UPPER() and UNION.