Bug #121606 Query with COMPRESS() fails with ERROR 3854 under JOIN_ORDER hint

• Submitted: 26 Oct 8:53
• Modified: 27 Oct 4:39
• Reporter: Hao Wang
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.7.2
• OS: Any
• CPU Architecture: Any

[26 Oct 8:53] Hao Wang

Description:
A query combining a `COMPRESS()`-containing derived table with `UNION` returns results normally without a `JOIN_ORDER` hint. Adding `/*+ JOIN_ORDER(t3,t1)*/` causes the same query to fail with `ERROR 3854 (HY000): Cannot convert string '\x0A\x00\x00\x00x\x9C...' from binary to utf8mb4`.

How to repeat:
mysql> CREATE TABLE t0 (c1 char(1));
Query OK, 0 rows affected (0.169 sec)

mysql> INSERT INTO t0 (c1) VALUES (null);
Query OK, 1 row affected (0.033 sec)

mysql> SELECT t1.c1 FROM t0 as t1 CROSS JOIN (SELECT t2.c1 FROM t0 as t2 GROUP BY t2.c1 HAVING (COMPRESS('2010-03-15') NOT LIKE (t2.c1))) AS t3 ON YEARWEEK(t1.c1) UNION select c1 from t0;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.011 sec)

-- Run the same query with JOIN_ORDER hint:
mysql> SELECT /*+ JOIN_ORDER(t3,t1)*/ t1.c1 FROM t0 as t1 CROSS JOIN (SELECT t2.c1 FROM t0 as t2 GROUP BY t2.c1 HAVING (COMPRESS('2010-03-15') NOT LIKE (t2.c1))) AS t3 ON YEARWEEK(t1.c1) UNION select c1 from t0;
ERROR 3854 (HY000): Cannot convert string '\x0A\x00\x00\x00x\x9C...' from binary to utf8mb4

Expected Result：The two queries return the same result. An optimizer hint should not change query semantics or introduce  errors.

[27 Oct 4:39] Chaithra Marsur Gopala Reddy

Hi Hao Wang,

Thank you for the test case. We can reproduce your observation. This is expected behavior. Without the hint, the join condition filters out all rows from t1, so t3 is not evaluated. With the changed join order, t3 is evaluated first, which exposes the error when compressed binary data is converted to utf8mb4.
The conversion error is valid, and whether it is encountered depends on the evaluation order. Therefore, this is not a bug.

Simplified test case:
CREATE TABLE t(c CHAR(1) CHARACTER SET utf8mb4);
INSERT INTO t VALUES(NULL);

  -- Returns NULL.
SELECT MIN(a.c)
FROM t a, (SELECT c FROM t HAVING X'FF' LIKE c) b
WHERE a.c;

-- ERROR 3854: Cannot convert string '\xFF' from binary to utf8mb4.
SELECT /*+ JOIN_ORDER(b,a) */ MIN(a.c)
FROM t a, (SELECT c FROM t HAVING X'FF' LIKE c) b
WHERE a.c;