Bug #21814 TIME between 23:59:59 and 99:59:59 is wrapped (modulo 24h)
Submitted: 24 Aug 2006 16:57 Modified: 18 Oct 2006 21:35
Reporter: Marc Herbert Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1.13 OS:N/A
Assigned to: CPU Architecture:Any
Tags: time wrap sql date

[24 Aug 2006 16:57] Marc Herbert
Description:
See title. Tagged as "serious" because this is plain data corruption.

Trying to re-insert the java object demonstrates that the result is really wrapped; I mean this not just a display issue.

Similar (but _different_) complains:
<http://bugs.mysql.com/bug.php?id=11154>
<http://bugs.mysql.com/bug.php?id=4978>

How to repeat:
Using any interactive JDBC client:

create table ts (t TIME)
insert into ts values ('24:01')
select * from ts

       ->   00:01:00

Suggested fix:
Above 100 hours an exception is thrown which looks like the Right Thing. Why not do that as soon as from 24 hours ?
[24 Aug 2006 18:10] Marc Herbert
Actually the title is exact only for regular Statements,
for PreparedStatements no exception is ever thrown.

Here is the fixed bug title for PreparedStatement:
 "TIME has its negative sign removed, then is wrapped (modulo 24h)"
I mean, all across the '-838:59:59' to '838:59:59' range (even more data corruption).
[24 Aug 2006 18:28] Mark Matthews
I see where to fix this in the driver (and we will), but it's only a workaround, since really, java.sql.Time shouldn't accept invalid data (i.e. values outside what's defined for the SQL type "TIME", which is what the spec says java.sql.Time represents) in it's constructors.
[24 Aug 2006 18:46] Marc Herbert
If by "a fix" you mean "always throw an exception", then I agree 200%.
Always throwing an exception simply looks like the Right Thing to me (not just a workaround!) considering the limited range of java.sql.Time you mentioned.
[24 Aug 2006 19:59] Mark Matthews
Fix by throwing exceptions is what I meant. All of our java.sql.Time instances are that actually instantiated in one place, so it's a few lines to check the bounds.
[25 Aug 2006 18:49] Marc Herbert
As a minor side note, the behaviour for values returned by the "timediff"
function seems to be exactly the same than described above.
I mean the same than for values extracted from "TIME" columns.
Not so surprising...
[18 Oct 2006 21:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13915
[18 Oct 2006 21:35] Mark Matthews
Fixed in 3.1.14 (and 5.0.4 and trunk).