Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
-
Windows Plateform
Description
MariaDB [(none)]> use test;
|
Database changed
|
|
|
MariaDB [test]> create table like_test(name varchar(20) character set 'utf8' collate 'utf8_unicode_ci');
|
Query OK, 0 rows affected (0.012 sec)
|
|
|
MariaDB [test]> insert into like_test values('radio! test');
|
Query OK, 1 row affected (0.003 sec)
|
|
|
MariaDB [test]> select * from like_test where name like '%!!%' escape '!';
|
Empty set (0.002 sec) -- this is wrong!
|
|
|
MariaDB [test]> ALTER TABLE like_test CHANGE COLUMN name name VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
|
Query OK, 1 row affected (0.032 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select * from like_test where name like '%!!%' escape '!';
|
+-------------+
|
| name |
|
+-------------+
|
| radio! test |
|
+-------------+
|
1 row in set (0.001 sec)
|
I still get empty set when I alter the collation of the field "name" to utf8_unicode_520_ci, utf8mb4_unicode_ci, utf8mb4_unicode_520_ci. But all work fine on utf8_general_ci, utf8mb4_general_ci.
Pls forgive me for my bad english.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- links to
Activity
A smaller script reproducing the problem:
CREATE OR REPLACE TABLE t1 (name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci); |
INSERT INTO t1 VALUES ('radio! test'); |
SELECT name LIKE '%!!%' ESCAPE '!' AS c1, |
name LIKE '%!!%' COLLATE utf8_general_ci ESCAPE '!' AS c2 |
FROM t1; |
+------+------+
|
| c1 | c2 |
|
+------+------+
|
| 0 | 1 |
|
+------+------+
|
A similar bug was earlier fixed in MySQL in utf8_general_ci:
https://bugs.mysql.com/bug.php?id=11754
Thanks for the report!
Reproduced as described on MariaDB 10.0-10.3
not repeatable in MySQL 8.0.11
mysql> show create table like_test;+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| like_test | CREATE TABLE `like_test` (`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from like_test where name like '%!!%' escape '!';+-------------+| name |+-------------+| radio! test |+-------------+1 row in set (0.00 sec)mysql> select version();+-----------+| version() |+-----------+| 8.0.11 |+-----------+1 row in set (0.00 sec)MariaDB 10.2
MariaDB [test]> create or replace table t1(name varchar(20) character set 'utf8' collate 'utf8_unicode_ci') ;Query OK, 0 rows affected (0.07 sec)MariaDB [test]> insert into t1 values('radio! test');Query OK, 1 row affected (0.01 sec)MariaDB [test]> select * from t1 where name like '%!!%' escape '!';Empty set (0.00 sec)MariaDB [test]> select * from t1 where name like '%!!!%' escape '!';+-------------+| name |+-------------+| radio! test |+-------------+1 row in set (0.00 sec)MariaDB [test]> ALTER TABLE t1 CHANGE COLUMN name name VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';Query OK, 1 row affected (0.10 sec)Records: 1 Duplicates: 0 Warnings: 0MariaDB [test]> select * from t1 where name like '%!!%' escape '!';+-------------+| name |+-------------+| radio! test |+-------------+1 row in set (0.00 sec)