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

Erroneous "Impossible where" when mixing decimal comparison and LIKE

    XMLWordPrintable

    Details

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

      Description

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(8,2));
      INSERT INTO t1 VALUES (10);
      SELECT * FROM t1 WHERE a=10.0;
      SELECT * FROM t1 WHERE a LIKE 10.00;

      returns one row in both SELECT queries.

      Now if I join the two conditions into a single query:

      SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00;

      it returns empty set.

      EXPLAIN tells that where condition is Impossible:

      MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00; 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
      | 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 |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

      The above is wrong.
      It seems that LIKE erroneously sets cmp_context for the arguments to DECIMAL_RESULT.
      It should set cmp_context either to STRING_RESULT or keep IMPOSSIBLE_RESULT.

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved: