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

Constant propagation erroneously applied for LIKE

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.0.16
    • Optimizer
    • None

    Description

      SET NAMES utf8 COLLATE utf8_german2_ci;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
      INSERT INTO t1 VALUES ('ae'),('ä');
      SELECT * FROM t1 WHERE a='ae';
      SELECT * FROM t1 WHERE a LIKE 'ä';

      returns these results:

      +------+
      | a    |
      +------+
      | ae   |
      | ä    |
      +------+
      2 rows in set (0.01 sec)
       
      +------+
      | a    |
      +------+
      | ä    |
      +------+
      1 row in set (0.00 sec)

      This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

      Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:

      SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';

      But in fact I get an empty set.

      EXPLAIN EXTENDED returns "Impossible where", which is not correct:

      mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
      |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

      After tracing the code, it seems that constant propagation was erroneously applied.

      Attachments

        Activity

          People

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