|
CREATE OR REPLACE TABLE t1 (id INT, a TIME);
|
INSERT INTO t1 VALUES (1,'00:00:01'),(2,'00:00:02');
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='00:00:01';
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:01' |
|
+-------+------+----------------------------------------------------------------------------------------------------------------+
|
In the above script we clear see that the string literal was replaced to TIME literal, so string-to-TIME conversion won't happen on every row.
Now I change the query to use a function (instead of the column directly):
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)='00:00:01';
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:01' |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------+
|
Notice, the string literal is still displayed as such (it's not displayed as a TIME literal), there is an impression that string-to-TIME conversion will happen on every row, which would be slower.
However, if we put a break point in Item_func_eq::val_int() and run this query:
SELECT * FROM t1 WHERE COALESCE(a)='00:00:01';
|
(gdb) set p o
|
(gdb) p cmp.a[0]
|
$17 = (Item_func_coalesce *) 0x7ffe6c017168
|
(gdb) p cmp.b[0]
|
$18 = (Item_cache_time *) 0x7ffe6c0186c8
|
we can see that it's an Item_cache_time in the right side of the comparator, so string-to-TIME conversion won't really happen on every row.
It should be fixed to make EXPLAIN EXTENDED clearly explain that conversion won't happen per row.
There is a difference in how columns vs functions are handled for comparison:
- In case of a column, Item_func_eq::args[1] is replaced to Item_time_literal, so Arg_comparator already gets a converted value. Replacing happens in Item_func_eq::build_equal_items().
- In case of a function, Item_func_eq::args[1] still points to the original Item_string, while Arg_comparator takes care of conversion and caching.
Perhaps in case of COALESCE(a), the optimizer could replace Item_string to Item_time_literal at optimize_cond time, like it does in case of a column.
|