Bug #68759 Inconsistent results on comparison a time field with a non-time literal
| Submitted: | 24 Mar 2013 12:49 | Modified: | 25 Mar 2013 5:29 |
|---|---|---|---|
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.6.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[24 Mar 2013 12:57]
Elena Stepanova
Please also consider a slightly different test case (InnoDB-only): MySQL [test]> CREATE TABLE t_inno (pk INT PRIMARY KEY, t_key TIME, t TIME, KEY(t_key)) ENGINE=InnoDB; Query OK, 0 rows affected (1.37 sec) MySQL [test]> INSERT INTO t_inno VALUES (1,'12:12:12','12:12:12'),(2,'21:21:21','21:21:21'); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 MySQL [test]> MySQL [test]> SELECT * FROM t_inno WHERE t < 'z'; +----+----------+----------+ | pk | t_key | t | +----+----------+----------+ | 1 | 12:12:12 | 12:12:12 | | 2 | 21:21:21 | 21:21:21 | +----+----------+----------+ 2 rows in set, 1 warning (0.00 sec) MySQL [test]> SELECT pk FROM t_inno WHERE t_key < 'z'; +----+ | pk | +----+ | 1 | | 2 | +----+ 2 rows in set, 2 warnings (0.00 sec) MySQL [test]> SELECT * FROM t_inno WHERE t_key < 'z'; Empty set, 2 warnings (0.00 sec)
[25 Mar 2013 5:29]
MySQL Verification Team
Hello Elena, Thank you for the report. Verified as described. Workaround: Compare time field with a valid value Thanks, Umesh
[25 Mar 2013 5:30]
MySQL Verification Team
## 5.6.10 mysql> show variables like '%mode%'; +--------------------------+--------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------+ | gtid_mode | OFF | | innodb_autoinc_lock_mode | 1 | | innodb_strict_mode | OFF | | pseudo_slave_mode | OFF | | slave_exec_mode | STRICT | | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------+--------------------------------------------+ mysql> DROP TABLE t_my, t_in; Query OK, 0 rows affected (0.04 sec) mysql> mysql> CREATE TABLE t_my (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t_my VALUES (1,'00:00:00'),(2, '23:59:59'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT DISTINCT pk FROM t_my WHERE t < 'time'; +----+ | pk | +----+ | 1 | +----+ 1 row in set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1292 | Incorrect time value: 'time' for column 't' at row 1 | | Warning | 1292 | Incorrect time value: 'time' for column 't' at row 1 | +---------+------+------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT DISTINCT pk FROM t_my WHERE t < 'time'; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE | t_my | range | t | t | 4 | NULL | 1 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 row in set, 2 warnings (0.00 sec) mysql> CREATE TABLE t_in (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t_in VALUES (1,'00:00:00'),(2, '23:59:59'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT DISTINCT pk FROM t_in WHERE t < 'time'; +----+ | pk | +----+ | 1 | | 2 | +----+ 2 rows in set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1292 | Incorrect time value: 'time' for column 't' at row 1 | | Warning | 1292 | Incorrect time value: 'time' for column 't' at row 1 | +---------+------+------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT DISTINCT pk FROM t_in WHERE t < 'time'; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t_in | index | t | t | 4 | NULL | 2 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set, 2 warnings (0.00 sec) ### 5.5.31 mysql> show variables like '%mode'; +--------------------------+--------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------+ | innodb_autoinc_lock_mode | 1 | | innodb_strict_mode | OFF | | pseudo_slave_mode | OFF | | slave_exec_mode | STRICT | | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------+--------------------------------------------+ 5 rows in set (0.01 sec) mysql> DROP TABLE IF EXISTS t_my, t_in; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> mysql> CREATE TABLE t_my (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=MyISAM; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t_my VALUES (1,'00:00:00'),(2, '23:59:59'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT DISTINCT pk FROM t_my WHERE t < 'time'; +----+ | pk | +----+ | 1 | | 2 | +----+ 2 rows in set, 2 warnings (0.02 sec) mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1292 | Incorrect time value: 'time' for column 't' at row 1 | | Warning | 1292 | Incorrect time value: 'time' for column 't' at row 1 | +---------+------+------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT DISTINCT pk FROM t_my WHERE t < 'time'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_my | ALL | t | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> mysql> CREATE TABLE t_in (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=InnoDB; Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO t_in VALUES (1,'00:00:00'),(2, '23:59:59'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT DISTINCT pk FROM t_in WHERE t < 'time'; +----+ | pk | +----+ | 1 | | 2 | +----+ 2 rows in set, 2 warnings (0.00 sec) mysql> explain SELECT DISTINCT pk FROM t_in WHERE t < 'time'; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t_in | index | t | t | 4 | NULL | 2 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set, 2 warnings (0.00 sec)
[14 Nov 2013 14:23]
Hartmut Holzgraefe
same wrong results with 5.6.14, with 5.5.34 for the first test both the myisam and innodb variants return 2 rows, and for the 2nd innodb-only test all queries return 2 rows as expected ...
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: A query of the kind SELECT DISTINCT pk FROM t WHERE t < 'time' returns different results depending on the engine in use: MySQL [test]> CREATE TABLE t_my (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=MyISAM; MySQL [test]> INSERT INTO t_my VALUES (1,'00:00:00'),(2, '23:59:59'); MySQL [test]> SELECT DISTINCT pk FROM t_my WHERE t < 'time'; +----+ | pk | +----+ | 1 | +----+ 1 row in set, 2 warnings (0.00 sec) MySQL [test]> CREATE TABLE t_in (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=InnoDB; MySQL [test]> INSERT INTO t_in VALUES (1,'00:00:00'),(2, '23:59:59'); MySQL [test]> SELECT DISTINCT pk FROM t_in WHERE t < 'time'; +----+ | pk | +----+ | 1 | | 2 | +----+ 2 rows in set, 2 warnings (0.00 sec) While it's difficult to say for sure what is a correct result of an invalid comparison, it's clear that at least one of the above is wrong -- I'd say that logically MyISAM (1 row) is wrong and InnoDB (2 rows) is correct, considering the following: MySQL [test]> SELECT CAST('00:00:00' AS TIME) < 'time'; +-----------------------------------+ | CAST('00:00:00' AS TIME) < 'time' | +-----------------------------------+ | 1 | +-----------------------------------+ 1 row in set (0.00 sec) MySQL [test]> SELECT CAST('23:59:59' AS TIME) < 'time'; +-----------------------------------+ | CAST('23:59:59' AS TIME) < 'time' | +-----------------------------------+ | 1 | +-----------------------------------+ 1 row in set (0.00 sec) (I have a big time déjà vu filing this, most likely it's a duplicate -- sorry if it is, -- but I tried hard to find it and failed) How to repeat: DROP TABLE IF EXISTS t_my, t_in; CREATE TABLE t_my (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=MyISAM; INSERT INTO t_my VALUES (1,'00:00:00'),(2, '23:59:59'); SELECT DISTINCT pk FROM t_my WHERE t < 'time'; CREATE TABLE t_in (pk INT PRIMARY KEY, t TIME, KEY(t)) ENGINE=InnoDB; INSERT INTO t_in VALUES (1,'00:00:00'),(2, '23:59:59'); SELECT DISTINCT pk FROM t_in WHERE t < 'time'; DROP TABLE t_my, t_in;