connector-x: Reading date'9999-12-31' from Oracle leads to 'out of range DateTime' error

What language are you using?

Python

What version are you using?

3.9.13

What database are you using?

Oracle

What dataframe are you using?

polars

Can you describe your bug?

If reading date’9999-12-31’ from Oracle I get the error: ‘out of range DateTime’

What are the steps to reproduce the behavior?

Described here in details: https://github.com/pola-rs/polars/issues/16768

Database setup if the error only happens on specific data or data type
create table my_test nologging pctfree 0 as
select
   'aaa' prod,
   date'2000-01-01' start_date,
   date'9999-12-31' end_date
from dual
Example query / code
import polars as pl

uri = f"oracle://{user}:{password}@{host}:{port}/{service_name}"
v_sql = "select * from my_test"
df = pl.read_database_uri(
    v_sql, uri, engine="connectorx"
)

What is the error?

thread ‘<unnamed>’ panicked at /__w/connector-x/connector-x/connectorx/src/destinations/arrow2/arrow_assoc.rs:312:36: out of range DateTime


PanicException Traceback (most recent call last) /opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/polars/io/database/_utils.py in _read_sql_connectorx(query, connection_uri, partition_on, partition_range, partition_num, protocol, schema_overrides) 53 try: —> 54 tbl = cx.read_sql( 55 conn=connection_uri,

/opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/connectorx/init.py in read_sql(conn, query, return_type, protocol, partition_on, partition_range, partition_num, index_col) 385 –> 386 result = _read_sql( 387 conn,

PanicException: out of range DateTime

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

PanicException Traceback (most recent call last) /tmp/1000780000/ipykernel_12937/202598540.py in <module> 4 v_sql = “select * from my_test” ----> 5 df = pl.read_database_uri( 6 v_sql, uri, engine=“connectorx”,

/opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/polars/io/database/functions.py in read_database_uri(query, uri, partition_on, partition_range, partition_num, protocol, engine, schema_overrides, execute_options) 417 msg = “the ‘connectorx’ engine does not support use of execute_options” 418 raise ValueError(msg) –> 419 return _read_sql_connectorx( 420 query, 421 connection_uri=uri,

/opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/polars/io/database/_utils.py in _read_sql_connectorx(query, connection_uri, partition_on, partition_range, partition_num, protocol, schema_overrides) 64 # basic sanitisation of /user:pass/ credentials exposed in connectorx errs 65 errmsg = re.sub(“😕/[^:]+:[^:]+@”, “😕/:@”, str(err)) —> 66 raise type(err)(errmsg) from err 67 68 return from_arrow(tbl, schema_overrides=schema_overrides) # type: ignore[return-value]

PanicException: out of range DateTime

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Reactions: 1
  • Comments: 16 (9 by maintainers)

Commits related to this issue

Most upvoted comments

Hi, same error with a MySQL db despite having version 0.4.0 installed. The date is ‘0001-01-01’, weird but correct as far as I know.

Hi @lmocsi , thanks for opening the issue with a detailed example provided. In connectorx we parse the data to NaiveDateTime rust type first and then fill in to arrow Timestamp with nano second unit. The valid range of parsing rust NaiveDateTime to nano second is 1677-09-21T00:12:43.145224192 and 2262-04-11T23:47:16.854775807, and we will panic if the data is not in this range.

Someone in another issue wrote this: “This is a connectorx issue as it reads timestamps in nanoseconds (which it shouldn’t, as few databases actually support nanosecond precision - the majority top-out at microseconds).”

datetime.datetime(9999, 12, 31) is a valid date in python. How / when will connectorx support it?

Yeah, 0.3.4a2 solved the problem! Thx!

The data here is from a real database. Python version 3.11.8 return_type=arrow2 protocol=binary

CREATE TABLE date_test (
	date_field timestamp NULL
);


INSERT INTO date_test (date_field) VALUES('2023-03-04 00:00:00.000');
INSERT INTO date_test (date_field) VALUES('2223-03-04 00:00:00.000');
INSERT INTO date_test (date_field) VALUES('4073-04-09 23:59:59.000');

Sending this to read_sql fails in connectorx==0.3.3, but is successful in connectorx==0.3.2

select date_field from date_test where date_field > '2200-01-01';

This is the expected output.

date_field |
---------------------------+
    2223-03-04 00:00:00.000|
    4073-04-09 23:59:59.000|

Sending this to read_sql works fine with both versions.

select date_field from date_test where date_field < '2200-01-01';