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

UTF8 escape wildcard LIKE match has different behavior in different collations

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

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17064 LIKE function has error behavior on the fields in which the collation is xxx_unicode_xx

          • Major - Major loss of function.
          • Closed
          links to

          Web Link mysql

          Activity

            Transition Time In Source Status Execution Times
            Alice Sherepa made transition -
            Open Confirmed
            		11h 27m 1
            Alexander Barkov made transition -
            Confirmed Closed
            		649d 18h 21m 1

            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.