Panic when fetching 0000-00-00 date from MySQL Aurora

[dafydds]

What language are you using?

Python 3

What version are you using?

connectorx==0.3.1 polars==0.16.14

What database are you using?

MySQL AURORA

What dataframe are you using?

Polars

Can you describe your bug?

Give a high level description of the bug.

What are the steps to reproduce the behavior?

If possible, please include a minimal simple example including:

Database setup if the error only happens on specific data or data type

MySQL AURORA

CREATE TABLE Person (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  birthDate date DEFAULT NULL
)

ENGINE = INNODB,
AUTO_INCREMENT = 1227146,
AVG_ROW_LENGTH = 180,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

Example query / code

conn= "mysql://" + sql_username + ":" + sql_password + "@" + sql_host + ":" + sql_port + "/" + sql_main_database
sql = "select id, birthDate from Person where id = 1"  # this person has birthDate '0000-00-00'
df = pl.read_sql(sql, conn)  ## this player has a birth date of '0000-00-00'

What is the error?

thread '<unnamed>' panicked at 'Could not retrieve core::option::Option<chrono::naive::date::NaiveDate> from Value', /github/home/.cargo/registry/src/github.com-1ecc6299db9ec823/mysql_common-0.27.5/src/value/convert/mod.rs:175:23
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/ds/dev/work/prototypes/benh/run_sql/run_sql.py", line 180, in run_query
    df = pl.read_sql(query_str, conn_str)
File "/home/ds/miniconda3/lib/python3.9/site-packages/polars/utils/decorators.py", line 35, in wrapper
    return function(*args, **kwargs)
File "/home/ds/miniconda3/lib/python3.9/site-packages/polars/utils/decorators.py", line 126, in wrapper
    return function(*args, **kwargs)
File "/home/ds/miniconda3/lib/python3.9/site-packages/polars/io/database.py", line 198, in read_sql
    return read_database(
File "/home/ds/miniconda3/lib/python3.9/site-packages/polars/io/database.py", line 95, in read_database
    return _read_sql_connectorx(
File "/home/ds/miniconda3/lib/python3.9/site-packages/polars/io/database.py", line 224, in _read_sql_connectorx
    tbl = cx.read_sql(
File "/home/ds/miniconda3/lib/python3.9/site-packages/connectorx/__init__.py", line 257, in read_sql
    result = _read_sql(
pyo3_runtime.PanicException: Could not retrieve core::option::Option<chrono::naive::date::NaiveDate> from Value

[zbentzinger]

Also encountering this error:

Traceback (most recent call last):
  File ".venv/lib/python3.11/site-packages/polars/io/database/_utils.py", line 54, in _read_sql_connectorx
    tbl = cx.read_sql(
          ^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/connectorx/__init__.py", line 386, in read_sql
    result = _read_sql(
             ^^^^^^^^^^
pyo3_runtime.PanicException: Could not retrieve chrono::naive::date::NaiveDate from Value

Polars: 0.20.31 Connectorx: 0.3.3

MySQL (5.7, allowing invalid datetimes) table schema in question:

`StartDate` date NOT NULL DEFAULT '0000-00-00',
`EndDate` date NOT NULL DEFAULT '0000-00-00',

Omitting these columns from query resolves the issue.