Bug #12428 Working with TIMEs problem
Submitted: 8 Aug 2005 8:40 Modified: 11 Aug 2005 16:31
Reporter: Ante Drnasin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4 ... 5.0 OS:Any (*)
Assigned to: Paul DuBois CPU Architecture:Any

[8 Aug 2005 8:40] Ante Drnasin
Description:
This simple query returns false and this is not right...(bool values are used like strings just for debugging..otherwise TRUE and FALSE are used with no quotes)

SELECT IF(ADDTIME('88:00:00', '08:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE') AS canEnter

but

SELECT IF(ADDTIME('01:00:00', '02:00:00') <= TIME('03:00:00'), 'TRUE', 'FALSE') AS canEnter

returns TRUE !!???

I have found workaround but I think that my query is very logical and should work....

Here's the workaround

SELECT IF(TIME_TO_SEC('88:00:00') +  TIME_TO_SEC('08:00:00') <= TIME_TO_SEC('168:00:00'), 'TRUE', 'FALSE') AS canEnter

then I get TRUE...

How to repeat:
SELECT IF(ADDTIME('88:00:00', '08:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE') AS canEnter
[8 Aug 2005 10:23] Aleksey Kishkin
mysql> SELECT IF(TIME('101:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE');
+-------------------------------------------------------------+
| IF(TIME('101:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE') |
+-------------------------------------------------------------+
| TRUE                                                        |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(TIME('99:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE');
+------------------------------------------------------------+
| IF(TIME('99:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE') |
+------------------------------------------------------------+
| FALSE                                                      |
+------------------------------------------------------------+
1 row in set (0.00 sec)
[8 Aug 2005 10:27] Ante Drnasin
Hi Alexey and thanx for the reply but as you see I'm working with ADDTIME...did you try MY query that I have sent?I know that this you gave work but this query always returns false which is weird....

SELECT IF(ADDTIME('88:00:00', '08:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE')
AS canEnter

Try running that query thru your DB and then let me know the result...
for now I'm using

SELECT IF(TIME_TO_SEC(ADDTIME('88:00:00', '08:00:00')) <= TIME_TO_SEC('168:00:00'), 'TRUE', 'FALSE')
AS canEnter

whihc is very very bad workaround....

I still think there's a BUG somewhere but I just can't put my finger on it....
[8 Aug 2005 10:36] Ante Drnasin
Hi again :) 
I tried wrapping the results in TIME

1.
SELECT IF(TIME(ADDTIME('88:00:00', '08:00:00')) <= TIME('168:00:00'), 'TRUE', 'FALSE') AS canEnter

canEnter = false //WRONG!!

2.
88:00:00 + 08:00:00 = 96:00:00 so...

SELECT IF(TIME('96:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE') AS canEnter

canEnter = false //WRONG!!

So you see there is something wrong....how can they both be false...it's just not right....or am I missing something here?
[8 Aug 2005 11:12] Ante Drnasin
again..but this time from command prompt...

mysql> SELECT IF(TIME('96:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE');
+------------------------------------------------------------+
| IF(TIME('96:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE') |
+------------------------------------------------------------+
| FALSE                                                      |
+------------------------------------------------------------+
1 row in set (0.00 sec)
[8 Aug 2005 13:40] Andrey Hristov
TIME() returns a string because it is used in such a context. And "96:00:00" is greater than "168:00:00" when using string comparison.  Using TIME_TO_SEC is the right way to perform the comparison. Comparing dates and datetimes works the same way - string comparison but there there is no value over 99, which in this case creates the "problem".
[8 Aug 2005 13:44] Ante Drnasin
Thank you for the explanation Andrey but from the programers point of view this is just IMHO very very stupid behaviour...I think that you should document that ANY operation on TIME values should first be converted to seconds...
[11 Aug 2005 16:31] Mike Hillyer
Documented that the return type of the TIME() function is a string.