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:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D3 (Medium)

[24 Mar 2013 12:49] Elena Stepanova
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;
[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] Umesh Shastry
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] Umesh Shastry
## 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 ...