Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4
-
Unexpected results
Description
With cset_narrowing on, MariaDB decides whether it can use an index on a utf8mb3 column by looking at the collation the comparison uses, but decides how to turn the constant into an index key by looking at the constant's own collation. When the constant is not utf8mb4_general_ci, a 4-byte character such as an emoji goes into the key as '?' instead of U+FFFD, so the optimizer concludes that no row can match and the query returns nothing.
The result of the following is the empty set on MariaDB, which is incorrect.
SET optimizer_switch='cset_narrowing=on'; |
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; |
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci, |
b VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, |
KEY(a)); |
INSERT INTO t1 VALUES (_utf8mb3 0xEFBFBD, _utf8mb4 0xF09F9880), ('a', 'a'); |
SELECT HEX(a), HEX(b) FROM t1 WHERE a = b AND b = '😀'; |
DROP TABLE t1; |
MySQL 9.6.0 correctly returns a single row:
+--------+----------+ |
| HEX(a) | HEX(b) |
|
+--------+----------+ |
| EFBFBD | F09F9880 |
|
+--------+----------+ |
1 row in set (0.000 sec) |
An equivalent test case for Postgres also returns a single row:
CREATE COLLATION smile_eq (provider = icu, locale = 'und', |
rules = '&☺ = 😀', deterministic = false); |
CREATE TABLE t1 (a varchar(10), b varchar(10) COLLATE smile_eq); |
CREATE INDEX t1_a ON t1 (a); |
INSERT INTO t1 VALUES (U&'\263A', U&'\+01F600'), ('a', 'a'); |
SELECT encode(convert_to(a, 'UTF8'), 'hex') AS hex_a, |
encode(convert_to(b, 'UTF8'), 'hex') AS hex_b |
FROM t1 WHERE a = b AND b = '😀'; |
DROP TABLE t1; |
DROP COLLATION smile_eq; |
