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

Wrong result for SELECT..WHERE LENGTH(time_column)=8 AND time_column=TIMESTAMP'2001-01-01 10:20:31'

        Details

          Description

          SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03');
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31';

          In the above script the first and the second SELECT queries correctly return one row, the third SELECT query returns erroneously no rows.

            Gliffy Diagrams

              Attachments

                Issue Links

                blocks

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8728 Fix a number of problems in equal field and equal expression propagation

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                  Activity

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  MySQL-5.7.8 does not support propagation in this case:

                  explain extended SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31';
SHOW WARNINGS;

                  +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                      |
| Note    | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((length(`test`.`t1`.`a`) = 8) and (`test`.`t1`.`a` = '10:20:31')) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
                  Show
                  bar Alexander Barkov added a comment - MySQL-5.7.8 does not support propagation in this case: explain extended SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31'; SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. | | Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((length(`test`.`t1`.`a`) = 8) and (`test`.`t1`.`a` = '10:20:31')) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+

                    People

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

                      Dates

                      • Created:
                        Updated:
                        Resolved: