Bug #99955 ZEROFILL not handled by the Python Connector (ValueError, invalid literal)
Submitted: 23 Jun 7:37 Modified: 23 Jun 11:26
Reporter: Christian Proust Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:8.0.20 OS:CentOS
Assigned to: CPU Architecture:x86
Tags: Python Connector, ZEROFILL

[23 Jun 7:37] Christian Proust
Description:
When a SELECT statement fetch an INT ZEROFILL column that will be left 0-padded, the following error message occurs:

    ValueError: invalid literal for int() with base 0: '0001'

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "/home/phink/test_bug.py", line 62, in <module>
        assert cursor.fetchall() == [(1,)]
      File "/usr/local/lib64/python3.6/site-packages/mysql/connector/cursor_cext.py", line 490, in fetchall
        rows = self._cnx.get_rows()
      File "/usr/local/lib64/python3.6/site-packages/mysql/connector/connection_cext.py", line 301, in get_rows
        else self._cmysql.fetch_row()
    SystemError: <built-in method fetch_row of _mysql_connector.MySQL object at 0x55caac5c6010> returned a result with an error set

How to repeat:
- Create a table with an INT ZEROFILL column.
- Populate it with some values.
- Try to fetch the value back.

The following python script reproduce the error.

#!/usr/bin/env python3

from mysql import connector

# Create connection and cursor instance
# =====================================
# This part should likely be updated with the tester credentials in order to
# reproduce the bug
connection = connector.connect(
    user='foo',
    password='bar',
    database='test',
)
cursor = connection.cursor()

# Define the temporary table and populate it with some values
# ===========================================================
results = cursor.execute(
    """
        CREATE TEMPORARY TABLE bug_connector_zerofill(
            id INT(4) UNSIGNED ZEROFILL NOT NULL
        );
        INSERT INTO bug_connector_zerofill
        (id)
        VALUES
        (1),
        (10),
        (100),
        (1000);
    """,
    multi=True
)
for result in results:
    if result.with_rows:
        for row in result:
            pass

# Test of a ZEROFILL without left-padding
# =======================================
cursor.execute(
    """
        SELECT id FROM bug_connector_zerofill
        WHERE id=1000
    """
)
# This assertion pass
assert cursor.fetchall() == [(1000,)]

# Test of a ZEROFILL with padding
# ===============================
cursor.execute(
    """
        SELECT id FROM bug_connector_zerofill
        WHERE id=1
    """
)

# Up to here, everything works fine
print('--------------- Hello word ------------')

# The following statement will raise the following error:
assert cursor.fetchall() == [(1,)]
[23 Jun 7:38] Christian Proust
Test script

Attachment: test_bug.py (text/plain), 1.44 KiB.

[23 Jun 11:26] MySQL Verification Team
Hello Christian,

Thank you for the report and feedback.

regards,
Umesh