[MDEV-13335] UTF8 escape wildcard LIKE match has different behavior in different collations - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Duplicate
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: N/A
Component/s: Character Sets
Labels:
- upstream
Environment:
Windows 2012 R2 Server, Ubuntu LTS 16.04.2

Description

I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching.

mysql> SET collation_connection = utf8_unicode_ci;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';

+------------------+

| '\%b' LIKE '%\%' |

+------------------+

|                1 |

+------------------+

1 row in set (0.00 sec)

mysql> SET collation_connection = utf8_general_ci;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';

+------------------+

| '\%b' LIKE '%\%' |

+------------------+

|                0 |

+------------------+

1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_ci;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';

+------------------+

| '\%b' LIKE '%\%' |

+------------------+

|                1 |

+------------------+

1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_general_ci;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';

+------------------+

| '\%b' LIKE '%\%' |

+------------------+

|                0 |

+------------------+

1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_520_ci;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';

+------------------+

| '\%b' LIKE '%\%' |

+------------------+

|                1 |

+------------------+

1 row in set (0.00 sec)

I found out this problem on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?

Attachments

Issue Links

duplicates

MDEV-17064 LIKE function has error behavior on the fields in which the collation is xxx_unicode_xx

Closed

links to

mysql

Activity

Ascending order - Click to sort in descending order

Su, Jun-Ming created issue - 2017-07-17 01:06

Su, Jun-Ming made changes - 2017-07-17 01:07

Field	Original Value	New Value
Description	I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching. {{mysql> SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 1 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 0 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8mb4_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 1 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8mb4_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 0 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8mb4_unicode_520_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 1 \| +------------------+ 1 row in set (0.00 sec) }} I found out this [problem\|https://bugs.mysql.com/bug.php?id=74901] on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?	I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching. mysql> SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) {{ mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 1 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 0 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8mb4_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 1 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8mb4_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 0 \| +------------------+ 1 row in set (0.00 sec) mysql> SET collation_connection = utf8mb4_unicode_520_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT '\%b' LIKE '%\%'; +------------------+ \| '\%b' LIKE '%\%' \| +------------------+ \| 1 \| +------------------+ 1 row in set (0.00 sec) }} I found out this [problem\|https://bugs.mysql.com/bug.php?id=74901] on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?

Su, Jun-Ming made changes - 2017-07-17 01:08

Description

I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching.

mysql> SET collation_connection = utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

{{
mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_520_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
}}

I found out this [problem|https://bugs.mysql.com/bug.php?id=74901] on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?

I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching.

mysql> SET collation_connection = utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

{code:console}
mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_520_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
{code}

I found out this [problem|https://bugs.mysql.com/bug.php?id=74901] on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?

Su, Jun-Ming made changes - 2017-07-17 01:09

Description

I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching.

mysql> SET collation_connection = utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

{code:console}
mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_520_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
{code}

I found out this [problem|https://bugs.mysql.com/bug.php?id=74901] on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?

I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching.

{code:console}
mysql> SET collation_connection = utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> SET collation_connection = utf8mb4_unicode_520_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\%b' LIKE '%\%';
+------------------+
| '\%b' LIKE '%\%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
{code}

I found out this [problem|https://bugs.mysql.com/bug.php?id=74901] on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?

Elena Stepanova made changes - 2017-07-17 09:22

Assignee

Alice Sherepa [ alice ]

Alice Sherepa made changes - 2017-07-17 12:34

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa added a comment - 2017-07-17 12:45 - edited

reproduced as described on MariaDB 10.2.7, 10.1.25, 10.3.1

Alice Sherepa added a comment - 2017-07-17 12:45 - edited reproduced as described on MariaDB 10.2.7, 10.1.25, 10.3.1

Alice Sherepa made changes - 2017-07-17 12:48

Labels

upstream

Alice Sherepa made changes - 2017-07-17 12:55

Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]

Alice Sherepa made changes - 2017-07-17 12:57

Remote Link

This issue links to "mysql (Web Link)" [ 28009 ]

Elena Stepanova made changes - 2017-07-17 23:03

Fix Version/s		10.2 [ 14601 ]
Assignee	Alice Sherepa [ alice ]	Alexander Barkov [ bar ]
Priority	Major [ 3 ]	Minor [ 4 ]

Alexander Barkov made changes - 2019-04-28 06:54

Link

This issue duplicates ~~MDEV-17064~~ [ ~~MDEV-17064~~ ]

Alexander Barkov made changes - 2019-04-28 06:55

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Duplicate [ 3 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 81685 ]

MariaDB v4 [ 152495 ]

People

Assignee:: Alexander Barkov

Reporter:: Su, Jun-Ming

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-07-17 01:06

Updated:: 2019-04-28 07:05

Resolved:: 2019-04-28 06:55

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration