Bug #121748 Pure-Python prepared statements send -1 hour as TIME -47:00:00 and read TIME -01:00:00 as +1 hour

Submitted: 11 Nov 20:53
Reporter: Ke Han Email Updates:
Status: Open Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version: OS:Ubuntu
Assigned to: CPU Architecture:x86

[11 Nov 20:53] Ke Han
Description:
In the pure-Python implementation (use_pure=True, and the only implementation
where the C extension is not installed), the binary-protocol TIME conversion
applies the sign to the day count only, in both directions:

- write: a negative datetime.timedelta parameter of a prepared statement that
  is not a whole number of days is stored with the wrong magnitude:
  timedelta(hours=-1) is stored as -47:00:00, timedelta(minutes=-30) as
  -47:30:00, timedelta(days=-2, hours=3) (= -45:00:00) as -51:00:00;
- read: a negative TIME value read through a prepared cursor loses its sign or
  gets the wrong magnitude: -01:00:00 is returned as timedelta(seconds=3600)
  (+1 h), -00:00:00.5 as +0.5 s, -183:00:34 as -7 days + 15:00:34
  (= -152:59:26).

So a table copied with prepared statements has 01:00:00 where the source has
-01:00:00. No error or warning is raised. The text-protocol cursor of the same
connection is correct in both directions, and the C extension reads negative
TIME values correctly (its write path has a separate defect with timedelta
parameters).

How to repeat:
# Python 3.12, pip install mysql-connector-python==26.10.0, server 26.10.0
import datetime
import mysql.connector

cnx = mysql.connector.connect(host='127.0.0.1', port=3306, user='root', autocommit=True, use_pure=True)
print(mysql.connector.__version__, type(cnx).__name__)
txt = cnx.cursor()
txt.execute('DROP DATABASE IF EXISTS cpy204'); txt.execute('CREATE DATABASE cpy204'); txt.execute('USE cpy204')
txt.execute('CREATE TABLE w (id INT PRIMARY KEY, via VARCHAR(10), v TIME(6))')
prep = cnx.cursor(prepared=True)
for i, td in enumerate([datetime.timedelta(hours=-1), datetime.timedelta(minutes=-30), datetime.timedelta(days=-2, hours=3)]):
    txt.execute('INSERT INTO w VALUES (%s, %s, %s)', (10 * i, 'text', td))
    prep.execute('INSERT INTO w VALUES (%s, %s, %s)', (10 * i + 1, 'prepared', td))
txt.execute('SELECT id, via, CAST(v AS CHAR) FROM w ORDER BY id')
print('write:'); [print(' ', r) for r in txt.fetchall()]
# read side
txt.execute("CREATE TABLE r (id INT PRIMARY KEY, v TIME(6))")
txt.execute("INSERT INTO r VALUES (1, '-01:00:00'), (2, '-00:00:00.5'), (3, '-183:00:34'), (4, '12:34:56')")
txt.execute('SELECT id, v FROM r ORDER BY id'); a = txt.fetchall()
prep.execute('SELECT id, v FROM r WHERE id > %s ORDER BY id', (0,)); b = prep.fetchall()
print('read (text cursor / prepared cursor):')
for (i, x), (_, y) in zip(a, b):
    print(f'  {i}: {x!r} / {y!r}')

Output:
26.10.0 MySQLConnection
write:
  (0, 'text', '-01:00:00.000000')
  (1, 'prepared', '-47:00:00.000000')
  (10, 'text', '-00:30:00.000000')
  (11, 'prepared', '-47:30:00.000000')
  (20, 'text', '-45:00:00.000000')
  (21, 'prepared', '-51:00:00.000000')
read (text cursor / prepared cursor):
  1: datetime.timedelta(days=-1, seconds=82800) / datetime.timedelta(seconds=3600)
  2: datetime.timedelta(days=-1, seconds=86399, microseconds=500000) / datetime.timedelta(microseconds=500000)
  3: datetime.timedelta(days=-8, seconds=32366) / datetime.timedelta(days=-7, seconds=54034)
  4: datetime.timedelta(seconds=45296) / datetime.timedelta(seconds=45296)

Expected: each "prepared" row equals the "text" row above it, and the
prepared cursor reads the same timedelta values as the text cursor.

Suggested fix:
lib/mysql/connector/protocol.py:
- MySQLProtocol.prepare_binary_time() sets is_negative when value.days < 0
  but encodes abs(value.days) days plus the hours/minutes/seconds of
  value.seconds. Python normalises a negative timedelta to days < 0 and a
  positive seconds remainder (-1 h is days=-1, seconds=82800), so the value
  sent is -(1 day 23:00:00) = -47 h. The magnitude should be computed from
  the whole value, e.g. abs(value) (or -value when negative), and then split
  into days/hours/minutes/seconds/microseconds:
  https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
- MySQLProtocol._parse_binary_time() negates only days
  (if data[0] == 1: days *= -1) and adds hours, minutes, seconds and
  microseconds with a positive sign, so -(0 days 01:00:00) becomes +01:00:00
  and -(7 days 15:00:34) becomes -7 days + 15:00:34. It should build the
  positive timedelta from all fields and negate the result:
  https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...

The text paths (MySQLConverter._timedelta_to_mysql in conversion.py and the
text TIME parser) handle the sign correctly. Related, different path:
Bug #72493 (negative TIME read through the text protocol, fixed in 2014).

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.