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

Misleading output for EXPLAIN EXTENDED SELECT..WHERE func(time_col)='00:00:01'

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 10.5
    • Component/s: None
    • Labels:
      None

      Description

      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.

        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: