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'

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.40, 10.0.14, 10.1.1
    • Fix Version/s: 10.0.16
    • Component/s: Character Sets
    • Labels:
      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

            Assignee:
            bar Alexander Barkov
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: