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

Wrong result set for WHERE a='oe' COLLATE utf8_german2_ci AND a='oe'

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.40, 10.0.14, 10.1.1
    • 10.0.16
    • Character Sets
    • None

    Description

      SET NAMES utf8 COLLATE utf8_german2_ci;
      		 
      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a CHAR(10) CHARACTER SET utf8);
      		 
      INSERT INTO t1 VALUES ('ö'),('oe');
      		 
      SELECT * FROM t1 WHERE a='oe' AND a='oe' COLLATE utf8_german2_ci;
      		 
      SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';

      The first SELECT query returns one row:

      +------+
      		 
      | a    |
      		 
      +------+
      		 
      | oe   |
      		 
      +------+

      The second SELECT query returns two rows:

      +------+
      		 
      | a    |
      		 
      +------+
      		 
      | ö    |
      		 
      | oe   |
      		 
      +------+

      The result for the second query is wrong.
      Both SELECT queries should return the same result with one row.
      The WHERE condition is logically the same in the two queries, the only different is the order of the AND operands.

      EXPLAIN for the second query:

      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
      		 
      SHOW WARNINGS;

      returns:

      +-------+------+-------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                       |
      		 
      +-------+------+-------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
      		 
      +-------+------+-------------------------------------------------------------------------------+

      It seems the condition was simplified in a wrong way. It should not have been simplified because the two equality predicates use different collations.

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.