Description:
A REVERSE(ST_AsGeoJSON()) expression over a spatial column returns a wrong comparison result when used through a VIEW or CTE.
The expression:
REVERSE(ST_AsGeoJSON(c0))
where c0 is a POINT value, produces a string derived from the GeoJSON representation of the geometry.
When queried through a VIEW or CTE, the predicate:
LOWER(vc) = vc
evaluates to true, so COUNT(*) returns 1.
However, diagnostics show that LOWER(vc) visibly changes the string, and the byte representation also changes. The value contains "tnioP", while LOWER(vc) contains "tniop". The column collation is reported as utf8mb4_bin, so the comparison should be case-sensitive and 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 charset 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 temp_t;
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 POINT
);
INSERT INTO t0 VALUES (ST_GeomFromText('POINT(0 0)'));
CREATE VIEW v0 AS
SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc
FROM t0;
SELECT COUNT(*) AS view_cnt
FROM v0
WHERE LOWER(vc) = vc;
WITH cte AS (
SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc
FROM t0
)
SELECT COUNT(*) AS cte_cnt
FROM cte
WHERE LOWER(vc) = vc;
CREATE TEMPORARY TABLE temp_t AS
SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc
FROM t0;
SELECT COUNT(*) AS temp_cnt
FROM temp_t
WHERE LOWER(vc) = vc;
Actual result:
The VIEW query returns:
+----------+
| view_cnt |
+----------+
| 1 |
+----------+
The CTE query returns:
+---------+
| cte_cnt |
+---------+
| 1 |
+---------+
The TEMPORARY TABLE query returns:
+----------+
| temp_cnt |
+----------+
| 0 |
+----------+
Diagnostics:
SHOW CREATE TABLE temp_t;
SELECT
vc,
LOWER(vc) AS lower_vc,
LOWER(vc) = vc AS lower_eq_vc,
CHARSET(vc) AS charset_vc,
COLLATION(vc) AS collation_vc,
HEX(vc) AS hex_vc,
HEX(LOWER(vc)) AS hex_lower_vc
FROM v0;
WITH cte AS (
SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc
FROM t0
)
SELECT
vc,
LOWER(vc) AS lower_vc,
LOWER(vc) = vc AS lower_eq_vc,
CHARSET(vc) AS charset_vc,
COLLATION(vc) AS collation_vc,
HEX(vc) AS hex_vc,
HEX(LOWER(vc)) AS hex_lower_vc
FROM cte;
SELECT
vc,
LOWER(vc) AS lower_vc,
LOWER(vc) = vc AS lower_eq_vc,
CHARSET(vc) AS charset_vc,
COLLATION(vc) AS collation_vc,
HEX(vc) AS hex_vc,
HEX(LOWER(vc)) AS hex_lower_vc
FROM temp_t;
SHOW WARNINGS;
SHOW CREATE TABLE temp_t shows that the temporary table column uses utf8mb4_bin collation:
CREATE TEMPORARY TABLE `temp_t` (
`vc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
For the VIEW:
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
| vc | lower_vc | lower_eq_vc | charset_vc | collation_vc |
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
| }]0.0 ,0.0[ :"setanidrooc" ,"tnioP" :"epyt"{ | }]0.0 ,0.0[ :"setanidrooc" ,"tniop" :"epyt"{ | 1 | utf8mb4 | utf8mb4_bin |
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
For the CTE:
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
| vc | lower_vc | lower_eq_vc | charset_vc | collation_vc |
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
| }]0.0 ,0.0[ :"setanidrooc" ,"tnioP" :"epyt"{ | }]0.0 ,0.0[ :"setanidrooc" ,"tniop" :"epyt"{ | 1 | utf8mb4 | utf8mb4_bin |
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
For the TEMPORARY TABLE:
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
| vc | lower_vc | lower_eq_vc | charset_vc | collation_vc |
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
| }]0.0 ,0.0[ :"setanidrooc" ,"tnioP" :"epyt"{ | }]0.0 ,0.0[ :"setanidrooc" ,"tniop" :"epyt"{ | 0 | utf8mb4 | utf8mb4_bin |
+----------------------------------------------+----------------------------------------------+-------------+------------+--------------+
The hex values also show that vc and LOWER(vc) are different. In particular, the character P changes to p:
hex_vc contains: ... 50 ...
hex_lower_vc contains: ... 70 ...
Expected result:
The VIEW and CTE queries should return 0, the same as the TEMPORARY TABLE query.
Since COLLATION(vc) is utf8mb4_bin, the comparison should be case-sensitive. Therefore:
vc contains "tnioP"
LOWER(vc) contains "tniop"
should not compare equal.
The expected result for all three COUNT(*) queries is:
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
Why this is a bug:
This is not just a type materialization difference. The VIEW, CTE, and TEMPORARY TABLE all report:
CHARSET(vc) = utf8mb4
COLLATION(vc) = utf8mb4_bin
and LOWER(vc) visibly differs from vc. The byte representations are also different.
However, only the VIEW and CTE incorrectly evaluate:
LOWER(vc) = vc
as true. This appears to be a wrong-result bug in comparison evaluation or optimization for expression-derived strings in VIEW/CTE contexts.
I selected S2 because this is a wrong-result issue: the predicate evaluates to true although the two compared strings are different under utf8mb4_bin collation.
Description: A REVERSE(ST_AsGeoJSON()) expression over a spatial column returns a wrong comparison result when used through a VIEW or CTE. The expression: REVERSE(ST_AsGeoJSON(c0)) where c0 is a POINT value, produces a string derived from the GeoJSON representation of the geometry. When queried through a VIEW or CTE, the predicate: LOWER(vc) = vc evaluates to true, so COUNT(*) returns 1. However, diagnostics show that LOWER(vc) visibly changes the string, and the byte representation also changes. The value contains "tnioP", while LOWER(vc) contains "tniop". The column collation is reported as utf8mb4_bin, so the comparison should be case-sensitive and 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 charset 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 temp_t; DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 POINT ); INSERT INTO t0 VALUES (ST_GeomFromText('POINT(0 0)')); CREATE VIEW v0 AS SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc FROM t0; SELECT COUNT(*) AS view_cnt FROM v0 WHERE LOWER(vc) = vc; WITH cte AS ( SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc FROM t0 ) SELECT COUNT(*) AS cte_cnt FROM cte WHERE LOWER(vc) = vc; CREATE TEMPORARY TABLE temp_t AS SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc FROM t0; SELECT COUNT(*) AS temp_cnt FROM temp_t WHERE LOWER(vc) = vc; Actual result: The VIEW query returns: +----------+ | view_cnt | +----------+ | 1 | +----------+ The CTE query returns: +---------+ | cte_cnt | +---------+ | 1 | +---------+ The TEMPORARY TABLE query returns: +----------+ | temp_cnt | +----------+ | 0 | +----------+ Diagnostics: SHOW CREATE TABLE temp_t; SELECT vc, LOWER(vc) AS lower_vc, LOWER(vc) = vc AS lower_eq_vc, CHARSET(vc) AS charset_vc, COLLATION(vc) AS collation_vc, HEX(vc) AS hex_vc, HEX(LOWER(vc)) AS hex_lower_vc FROM v0; WITH cte AS ( SELECT REVERSE(ST_AsGeoJSON(c0)) AS vc FROM t0 ) SELECT vc, LOWER(vc) AS lower_vc, LOWER(vc) = vc AS lower_eq_vc, CHARSET(vc) AS charset_vc, COLLATION(vc) AS collation_vc, HEX(vc) AS hex_vc, HEX(LOWER(vc)) AS hex_lower_vc FROM cte; SELECT vc, LOWER(vc) AS lower_vc, LOWER(vc) = vc AS lower_eq_vc, CHARSET(vc) AS charset_vc, COLLATION(vc) AS collation_vc, HEX(vc) AS hex_vc, HEX(LOWER(vc)) AS hex_lower_vc FROM temp_t; SHOW WARNINGS; SHOW CREATE TABLE temp_t shows that the temporary table column uses utf8mb4_bin collation: CREATE TEMPORARY TABLE `temp_t` ( `vc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci For the VIEW: +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ | vc | lower_vc | lower_eq_vc | charset_vc | collation_vc | +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ | }]0.0 ,0.0[ :"setanidrooc" ,"tnioP" :"epyt"{ | }]0.0 ,0.0[ :"setanidrooc" ,"tniop" :"epyt"{ | 1 | utf8mb4 | utf8mb4_bin | +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ For the CTE: +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ | vc | lower_vc | lower_eq_vc | charset_vc | collation_vc | +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ | }]0.0 ,0.0[ :"setanidrooc" ,"tnioP" :"epyt"{ | }]0.0 ,0.0[ :"setanidrooc" ,"tniop" :"epyt"{ | 1 | utf8mb4 | utf8mb4_bin | +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ For the TEMPORARY TABLE: +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ | vc | lower_vc | lower_eq_vc | charset_vc | collation_vc | +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ | }]0.0 ,0.0[ :"setanidrooc" ,"tnioP" :"epyt"{ | }]0.0 ,0.0[ :"setanidrooc" ,"tniop" :"epyt"{ | 0 | utf8mb4 | utf8mb4_bin | +----------------------------------------------+----------------------------------------------+-------------+------------+--------------+ The hex values also show that vc and LOWER(vc) are different. In particular, the character P changes to p: hex_vc contains: ... 50 ... hex_lower_vc contains: ... 70 ... Expected result: The VIEW and CTE queries should return 0, the same as the TEMPORARY TABLE query. Since COLLATION(vc) is utf8mb4_bin, the comparison should be case-sensitive. Therefore: vc contains "tnioP" LOWER(vc) contains "tniop" should not compare equal. The expected result for all three COUNT(*) queries is: +----------+ | COUNT(*) | +----------+ | 0 | +----------+ Why this is a bug: This is not just a type materialization difference. The VIEW, CTE, and TEMPORARY TABLE all report: CHARSET(vc) = utf8mb4 COLLATION(vc) = utf8mb4_bin and LOWER(vc) visibly differs from vc. The byte representations are also different. However, only the VIEW and CTE incorrectly evaluate: LOWER(vc) = vc as true. This appears to be a wrong-result bug in comparison evaluation or optimization for expression-derived strings in VIEW/CTE contexts. I selected S2 because this is a wrong-result issue: the predicate evaluates to true although the two compared strings are different under utf8mb4_bin collation.