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

Constant propagation erroneously applied for LIKE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.0, 5.5
    • Fix Version/s: 10.0.16
    • Component/s: Optimizer
    • Labels:
      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

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

              Dates

              Created:
              Updated:
              Resolved: