MySQL Bugs: #55321: Select involving TIME(datetime) function returns unexpected results

Bug #55321	Select involving TIME(datetime) function returns unexpected results
Submitted:	16 Jul 2010 13:25	Modified:	2 Dec 2012 17:44
Reporter:	Vladislav Vaintroub	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using time function on datetime column, 
SELECT involving  comparison of TIME(datetime_column) and strings representing valid time return unexpected results. 

It looks like string comparison is used in this case, which is not consistent with the behavior of TIME datatype.

How to repeat:
1) CREATE TABLE test(dt datetime);
INSERT INTO test VALUES ('2010-01-01 17:00:00');
INSERT INTO test VALUES ('2010-03-01 18:00:00');
INSERT INTO test VALUES ('2010-05-06 19:00:00');

2) Execute test queries

a ) select count(*) from test where time(dt) > '100:00:00';

returns 3 (should return 0, as 100 hours is larger than anything time() can return)

b ) select count(*) from test where time(dt) >'0 18:00:00';

returns 3 (should return 1, corresponding to row with 19:00:00 time)

================================================

Retry the same with TIME columns and queries not involving TIME() function. This time results are better, as expected.

CREATE TABLE test2(t time);
INSERT INTO test2 VALUES ('17:00:00');
INSERT INTO test2 VALUES ('18:00:00');
INSERT INTO test2 VALUES ('19:00:00');

select count(*) from test2 where t > '100:00:00';
returns 0, as expected

select count(*) from test2 where t >'0 18:00:00';
returns 1, as expected

The problem was originally found while investigating Bug#54631

Verified with recent 5.1.49 from bzr on Mac OS X:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  CREATE TABLE test(dt datetime);
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO test VALUES ('2010-01-01 17:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test VALUES ('2010-03-01 18:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES ('2010-05-06 19:00:00');
Query OK, 1 row affected (0.00 sec)

mysql>  select count(*) from test where time(dt) > '100:00:00';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from test where time(dt) >'0 18:00:00';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test2(t time);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test2 VALUES ('17:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test2 VALUES ('18:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test2 VALUES ('19:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from test2 where t > '100:00:00';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test2 where t >'0 18:00:00';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Query on the 1st table with DATETIME field is resolved as:

  WHERE:(original) (cast(`test`.`test`.`dt` as time) > '100:00:00')

whereas the one on the 2nd table with a TIME field 
is resolved as:

  WHERE:(original) (`test`.`test2`.`t` > 1000000)

So the left hand side in the comparisons is always a 
TIME value, the right hand side is only casted from string
to integer with the native TIME value in the 2nd table, 
not for the casted value coming from the TIME() function ...

no more interested in having it fixed