| 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 ...
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;