[MDEV-12721] Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE) - Jira

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.

blocks

MDEV-4912 Data type plugin API version 1

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server