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

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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.
          		 {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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 shows:
          {code}
          EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
          SHOW WARNINGS;
          {code}
          {noformat}
          +-------+------+-------------------------------------------------------------------------------+
          | Level | Code | Message |
          +-------+------+-------------------------------------------------------------------------------+
          | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
          +-------+------+-------------------------------------------------------------------------------+
          {noformat}
          It seems the condition was simplified in a wrong way.
          bar Alexander Barkov made changes -
          Description {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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 shows:
          {code}
          EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
          SHOW WARNINGS;
          {code}
          {noformat}
          +-------+------+-------------------------------------------------------------------------------+
          | Level | Code | Message |
          +-------+------+-------------------------------------------------------------------------------+
          | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
          +-------+------+-------------------------------------------------------------------------------+
          {noformat}
          It seems the condition was simplified in a wrong way.
          		 {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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:
          {code}
          EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
          SHOW WARNINGS;
          {code}
          returns:
          {noformat}
          +-------+------+-------------------------------------------------------------------------------+
          | Level | Code | Message |
          +-------+------+-------------------------------------------------------------------------------+
          | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
          +-------+------+-------------------------------------------------------------------------------+
          {noformat}
          It seems the condition was simplified in a wrong way.
          bar Alexander Barkov made changes -
          Description {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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:
          {code}
          EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
          SHOW WARNINGS;
          {code}
          returns:
          {noformat}
          +-------+------+-------------------------------------------------------------------------------+
          | Level | Code | Message |
          +-------+------+-------------------------------------------------------------------------------+
          | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
          +-------+------+-------------------------------------------------------------------------------+
          {noformat}
          It seems the condition was simplified in a wrong way.
          		 {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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:
          {code}
          EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
          SHOW WARNINGS;
          {code}
          returns:
          {noformat}
          +-------+------+-------------------------------------------------------------------------------+
          | Level | Code | Message |
          +-------+------+-------------------------------------------------------------------------------+
          | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
          +-------+------+-------------------------------------------------------------------------------+
          {noformat}
          It seems the condition was simplified in a wrong way. It should not have been simplified because the two operations use different collations.
          bar Alexander Barkov made changes -
          Description {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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:
          {code}
          EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
          SHOW WARNINGS;
          {code}
          returns:
          {noformat}
          +-------+------+-------------------------------------------------------------------------------+
          | Level | Code | Message |
          +-------+------+-------------------------------------------------------------------------------+
          | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
          +-------+------+-------------------------------------------------------------------------------+
          {noformat}
          It seems the condition was simplified in a wrong way. It should not have been simplified because the two operations use different collations.

          		 {code}
          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';
          {code}
          The first SELECT query returns one row:
          {noformat}
          +------+
          | a |
          +------+
          | oe |
          +------+
          {noformat}

          The second SELECT query returns two rows:
          {noformat}
          +------+
          | a |
          +------+
          | ö |
          | oe |
          +------+
          {noformat}

          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:
          {code}
          EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
          SHOW WARNINGS;
          {code}
          returns:
          {noformat}
          +-------+------+-------------------------------------------------------------------------------+
          | Level | Code | Message |
          +-------+------+-------------------------------------------------------------------------------+
          | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 'oe') |
          +-------+------+-------------------------------------------------------------------------------+
          {noformat}
          It seems the condition was simplified in a wrong way. It should not have been simplified because the two equality predicates use different collations.
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.0.15 [ 17300 ]
          bar Alexander Barkov made changes -
          Component/s OTHER [ 10125 ]
          Fix Version/s 10.0.16 [ 17900 ]
          Fix Version/s 10.0 [ 16000 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Component/s Character Sets [ 10801 ]
          Component/s OTHER [ 10125 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 58636 ] MariaDB v3 [ 64787 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 64787 ] MariaDB v4 [ 148500 ]

          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.