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'

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL)
    • 10.1.8
    • Optimizer
    • 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

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8705 Wrong result for SELECT..WHERE latin1_bin_column='a' AND latin1_bin_column='A'

          • Major - Major loss of function.
          • Closed

          Activity

            People

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