Bug #72493 Incorrect conversion of the negative value of the TIME type (mysql) to timedelt
Submitted: 30 Apr 2014 16:45 Modified: 26 Jun 2014 17:07
Reporter: Vitali Graf (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:1.1.6 OS:Any
Assigned to: Geert Vanderkelen CPU Architecture:Any
Tags: mysql:time, python3:timedelta

[30 Apr 2014 16:45] Vitali Graf
Description:
In my project stores many time fields in MySQL (begin and end of work times, pauses and to working time) MySQL calculates the difference (for every day, month and over the job).

I have found that many negative MySQL time values are converted incorrectly in timedelta. Go back in time values negative timedelta will also be converted incorrectly.

How to repeat:
I created a 10 test cases in 'test-timedelta.py'
Tested on Lubuntu 14.04 x86, python 3.4.0 and python 3.3.x, mysql 5.6.16-1~exp1 (Ubuntu)

The output of the tests with my comments:
vitali@vitali-VirtualBox:~$ python3 test-timedelta.py 
Readtest:
1 0:00:00.000001
2 0:00:00.000001 # wrong, should be negative
3 2 days, 0:00:00.000001
4 -2 days, 0:00:00.000001
5 0:00:01
6 0:00:01 # wrong, should be negative
7 0:01:01
8 0:01:01 # wrong, should be negative
9 1:01:01
10 -1 day, 23:01:01 # I think it is wrong, should be -1 day, 22:58:59 

Verification:
1 0:00:00.000001 0:00:00.000001 yes
2 0:00:00.000001 0:00:00.000001 no
3 2 days, 0:00:00.000001 2 days, 0:00:00.000001 yes
4 -2 days, 0:00:00.000001 -2 days, 0:00:00.000001 yes
5 0:00:01 0:00:01 yes
6 0:00:01 0:00:01 no
7 0:01:01 0:01:01 yes
8 0:01:01 0:01:01 no
9 1:01:01 1:01:01 yes
10 -1 day, 23:01:01 -1 day, 23:01:01 yes
vitali@vitali-VirtualBox:~$ 

Verification with mysql:
vitali@vitali-VirtualBox:~$ mysql -u root -p
Enter password: 
....
Server version: 5.6.16-1~exp1 (Ubuntu)
....
mysql> use test;
....
mysql> SELECT *, IF(read_ = write_,'yes','no') AS `equal?` FROM timedelta;
+----+------------------+------------------+--------+
| id | read_            | write_           | equal? |
+----+------------------+------------------+--------+
|  1 | 00:00:00.000001  | 00:00:00.000001  | yes    |
|  2 | -00:00:00.000001 | 00:00:00.000001  | no     |
|  3 | 48:00:00.000001  | 48:00:00.000001  | yes    |
|  4 | -48:00:00.000001 | -48:00:00.000001 | yes    |
|  5 | 00:00:01.000000  | 00:00:01.000000  | yes    |
|  6 | -00:00:01.000000 | 00:00:01.000000  | no     |
|  7 | 00:01:01.000000  | 00:01:01.000000  | yes    |
|  8 | -00:01:01.000000 | 00:01:01.000000  | no     |
|  9 | 01:01:01.000000  | 01:01:01.000000  | yes    |
| 10 | -01:01:01.000000 | -01:01:01.000000 | yes    |
+----+------------------+------------------+--------+
10 rows in set (0,00 sec)

mysql> 

Suggested fix:
I created the patch for mysql-connector-python-1.1.6/python3/mysql/connector/conversion.py
[30 Apr 2014 16:45] Vitali Graf
Test case

Attachment: test-timedelta.py (text/x-python), 1.33 KiB.

[30 Apr 2014 16:46] Vitali Graf
the patch for python3 and connenctor version 1.1.6

Attachment: conversion.patch (application/octet-stream, text), 1.99 KiB.

[13 May 2014 6:21] Geert Vanderkelen
Thank you for this bug report and thank you for your patch.
We have verified and we'll make sure it's fixed.
[20 May 2014 15:55] Paul DuBois
Noted in 1.2.2 changelog.

Negative timedelta values were incorrectly converted to and from
Python.
[23 Jun 2014 12:34] Geert Vanderkelen
Posted by developer:
 
Pushed to v1.2.3.
[26 Jun 2014 17:07] Paul DuBois
Noted in 1.2.3 changelog.

Negative timedelta values were incorrectly converted to and from
Python. Thanks to Vitali Graf for the patch.