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

Optimizer erroneously treats equal constants of different formats as same

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4.0
    • Component/s: Data types, Optimizer
    • Labels:
      None

      Description

      This script demonstrates that although TIME'00:00:00.0' and TIME'00:00:00.00' are equal to each other for comparison, they are still not equal in other contexts:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME);
      INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'),('00:00:02');
      SELECT LENGTH(COALESCE(TIME'00:00:00.0',a)),LENGTH(COALESCE(TIME'00:00:00.00',a)) FROM t1;
      

      +--------------------------------------+---------------------------------------+
      | LENGTH(COALESCE(TIME'00:00:00.0',a)) | LENGTH(COALESCE(TIME'00:00:00.00',a)) |
      +--------------------------------------+---------------------------------------+
      |                                   10 |                                    11 |
      |                                   10 |                                    11 |
      |                                   10 |                                    11 |
      +--------------------------------------+---------------------------------------+
      

      Notice, the two expressions return a different result (as expected), although TIME literals are equivalent for each other for comparison.

      Now I put the same expressions into a WHERE condition with <=>:

      SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.00',a));
      

      +----------+
      | a        |
      +----------+
      | 00:00:00 |
      | 00:00:01 |
      | 00:00:02 |
      +----------+
      

      It returns all rows. This is wrong. The condition is expected to be false, and the query should return empty set.

      EXPLAIN for the same query tells that the condition was erroneously simplified to true by the optimizer:

      EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.00',a));
      SHOW WARNINGS;
      

      +-------+------+--------------------------------------------------------+
      | Level | Code | Message                                                |
      +-------+------+--------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
      +-------+------+--------------------------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: