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

Erroneous "Impossible where" when mixing decimal comparison and LIKE

    XMLWordPrintable

Details

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

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