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

Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE)

    Details

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

      Description

      I run this script:

      CREATE OR REPLACE TABLE t1(a DATE,KEY(a));
      INSERT INTO t1 VALUES ('2012-01-01'),('2012-02-02');
      EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:01' AND TRUE;
      

      +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
      +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
      +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      

      Notice, it correctly returned Impossible WHERE, because the literal has a non-zero TIME part.

      Now I rewrite the query slightly:

      EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:01') AND TRUE;
      

      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      |    1 | SIMPLE      | t1    | ref  | a             | a    | 4       | const |    0 | Using where; Using index |
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      

      Notice, it does not return Impossible WHERE any more.

      The problem resides in this piece of the code:

        if (err > 0)
        {
          if (field->cmp_type() != value->result_type())
          {
            if ((type == EQ_FUNC || type == EQUAL_FUNC) &&
                value->result_type() == item_cmp_type(field->result_type(),
                                                      value->result_type()))
            {
              tree= new (alloc) SEL_ARG(field, 0, 0);
              tree->type= SEL_ARG::IMPOSSIBLE;
              goto end;
            }
      

      In case of the TIMESTAMP literal:

      • value points to an Item_datetime_literal instance.
      • field->cmp_type() gives TIME_RESULT
      • value->result_type() gives STRING_RESULT
      • item_cmp_type(field->result_type(), value->result_type()) gives STRING_RESULT
        The condition evaluates to true and an SEL_ARG::IMPOSSIBLE tree is returned.

      In case of the TIMESTAMP function:

      • value points to an instance of Item_cache_temporal
      • field->cmp_type() gives TIME_RESULT
      • value->result_type() gives INT_RESULT
      • item_cmp_type(field->result_type(), value->result_type()) gives REAL_RESULT
        The condition evaluates to false and the optimizer decides to use a range.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: