Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13335

UTF8 escape wildcard LIKE match has different behavior in different collations

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • N/A
    • Character Sets
    • 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

          Activity

            People

              bar Alexander Barkov
              sujunmin Su, Jun-Ming
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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