|
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.
|