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

Wrong result for SELECT..WHERE latin1_bin_column='a' AND latin1_bin_column='A'

    XMLWordPrintable

    Details

      Description

      This script:

      SET NAMES latin1;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin);
      INSERT INTO t1 VALUES ('a'),('A');
      SELECT * FROM t1 WHERE a='a' AND a='A';

      erroneously returns one row:

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

      The expected result is empty set.

      If I change the order in WHERE:

      SELECT * FROM t1 WHERE a='A' AND a='a';

      it erroneously returns one row again, but a different row:

      +------+
      | a    |
      +------+
      | A    |
      +------+

      The problem happens in Item_equal::add_const(). It does not take into account collation of the field and compares the two string literals using the current session collation, which is latin1_swedish_ci.

      A related problem:

      SET NAMES utf8 COLLATE utf8_german2_ci;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin);
      INSERT INTO t1 VALUES ('a');
      SELECT * FROM t1 WHERE a='a';
      SELECT * FROM t1 WHERE a=_utf8'a';
      SELECT * FROM t1 WHERE a='a' AND a=_utf8'a';

      The first and the second SELECT queries correctly return one row.
      The third query returns error:

      ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (utf8_german2_ci,COERCIBLE) for operation '='

      It should return one row.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: