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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.0
    • Fix Version/s: 10.1.8
    • Component/s: Optimizer
    • Labels:
      None

      Description

      This script:

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

      correctly returns empty set for both SELECT queries.

      If I now change the order of the two conditions in the second SELECT query:

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

      it erroneously returns one row:

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

      The same problem is repeatable with character set introducers
      (SET NAMES latin1 is not needed in this case):

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(20) COLLATE latin1_bin);
      INSERT INTO t1 VALUES ('a');
      SELECT * FROM t1 WHERE a=_latin1'A';
      SELECT * FROM t1 WHERE a=_latin1'A' AND a=_latin1'a';
      SELECT * FROM t1 WHERE a=_latin1'a' AND a=_latin1'A';

      The problem happens in Item_equal::add_const(), in this code:

        else
        {
          Item_func_eq *func= new (thd->mem_root) Item_func_eq(thd, c, const_item);
          if (func->set_cmp_func())
          {
            /*
              Setting a comparison function fails when trying to compare
              incompatible charsets. Charset compatibility is checked earlier,
              except for constant subqueries where we may do it here.
            */
            return;
          }
          func->quick_fix_field();
          cond_false= !func->val_int();
        }

      It does not take into account the column collation (latin1_bin) and compares the two constants as latin1_swedish_ci, which gives TRUE. The second part of the AND is then eliminated from the query.

      So:

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

      gets rewritten to:

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

      which returns no rows, while:

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

      gets rewritten as

      SELECT * FROM t1 WHERE a=_latin1'a';

      which returns 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: