Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
-
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
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-