Description:
A LOWER() expression over a JSON column returns a wrong comparison result when used through a VIEW or CTE.
The expression:
LOWER(c2)
where c2 is a JSON column containing:
{"a": "Xy"}
produces:
{"a": "xy"}
The query then evaluates:
UPPER(vc) = vc
For the VIEW and CTE, this predicate evaluates to true and the row is returned. However, diagnostics show that:
vc = {"a": "xy"}
UPPER(vc) = {"A": "XY"}
COLLATION(vc) = utf8mb4_bin
Under utf8mb4_bin collation, this comparison should be case-sensitive, so UPPER(vc) = vc should evaluate to false.
A TEMPORARY TABLE created from the same expression has the same displayed values and the same utf8mb4_bin collation, but evaluates the predicate correctly as false.
I selected the Optimizer category because the data type and collation are reported consistently as utf8mb4_bin in the VIEW, CTE, and TEMPORARY TABLE cases, but the equality predicate is evaluated incorrectly only in the VIEW/CTE forms.
How to repeat:
DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c2 JSON
);
INSERT INTO t0 VALUES ('{"a": "Xy"}');
CREATE VIEW v0 AS
SELECT LOWER(c2) AS vc
FROM t0;
SELECT vc
FROM v0
WHERE UPPER(vc) = vc;
WITH cte AS (
SELECT LOWER(c2) AS vc
FROM t0
)
SELECT vc
FROM cte
WHERE UPPER(vc) = vc;
CREATE TEMPORARY TABLE tmp AS
SELECT LOWER(c2) AS vc
FROM t0;
SELECT vc
FROM tmp
WHERE UPPER(vc) = vc;
Actual result:
The VIEW query returns one row:
+-------------+
| vc |
+-------------+
| {"a": "xy"} |
+-------------+
The CTE query returns one row:
+-------------+
| vc |
+-------------+
| {"a": "xy"} |
+-------------+
The TEMPORARY TABLE query returns an empty result set:
Empty set
Description: A LOWER() expression over a JSON column returns a wrong comparison result when used through a VIEW or CTE. The expression: LOWER(c2) where c2 is a JSON column containing: {"a": "Xy"} produces: {"a": "xy"} The query then evaluates: UPPER(vc) = vc For the VIEW and CTE, this predicate evaluates to true and the row is returned. However, diagnostics show that: vc = {"a": "xy"} UPPER(vc) = {"A": "XY"} COLLATION(vc) = utf8mb4_bin Under utf8mb4_bin collation, this comparison should be case-sensitive, so UPPER(vc) = vc should evaluate to false. A TEMPORARY TABLE created from the same expression has the same displayed values and the same utf8mb4_bin collation, but evaluates the predicate correctly as false. I selected the Optimizer category because the data type and collation are reported consistently as utf8mb4_bin in the VIEW, CTE, and TEMPORARY TABLE cases, but the equality predicate is evaluated incorrectly only in the VIEW/CTE forms. How to repeat: DROP VIEW IF EXISTS v0; DROP TEMPORARY TABLE IF EXISTS tmp; DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c2 JSON ); INSERT INTO t0 VALUES ('{"a": "Xy"}'); CREATE VIEW v0 AS SELECT LOWER(c2) AS vc FROM t0; SELECT vc FROM v0 WHERE UPPER(vc) = vc; WITH cte AS ( SELECT LOWER(c2) AS vc FROM t0 ) SELECT vc FROM cte WHERE UPPER(vc) = vc; CREATE TEMPORARY TABLE tmp AS SELECT LOWER(c2) AS vc FROM t0; SELECT vc FROM tmp WHERE UPPER(vc) = vc; Actual result: The VIEW query returns one row: +-------------+ | vc | +-------------+ | {"a": "xy"} | +-------------+ The CTE query returns one row: +-------------+ | vc | +-------------+ | {"a": "xy"} | +-------------+ The TEMPORARY TABLE query returns an empty result set: Empty set