Segfaults when querying timestamptz outside of legal pandas range

[glentakahashi]

What happens?

When querying a timestamptz outside valid pandas ranges, pandas will segfault when attempting to render the table, rather than throw an error.

tested on pandas versions 2.2.3 and 2.3.3

To Reproduce

import duckdb
conn = duckdb.connect(database=":memory:")
conn.execute("SET TimeZone = 'America/Los_Angeles'") # Segfaults
# conn.execute("SET TimeZone = 'UTC'") # does not segfault
df = conn.execute("SELECT '0001-01-01 00:00:00'::timestamptz").df()
print(df)

OS:

x86_64

DuckDB Version:

1.4.2

DuckDB Client:

Python

Hardware:

No response

Full Name:

Glen Takahashi

Affiliation:

Hex Technologies

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes

[glentakahashi]

Okay so I figured out the source of the bug but I think its still a duckdb issue as pandas doesn't really expect to be given invalid values (you cant construct a pandas dataframe manually with these timestamps, it gives a validation error), so I assume this is because duckdb does some stuff un the hood to directly map data through and bypass those protections.

The source of the bug comes from when pandas tries to convert a timestamp with timezone to local format from UTC. It crashes for dates before 1677-09-21T00:12:43.145224192. This is because pandas uses timezone info to look up utc transition info, but for pytz specifically overrides the default tzinfo of having year 0001-01-01 00:00:00 to instead be the minimum representable timestamp in timestamp64[ns] https://github.com/pandas-dev/pandas/blob/main/pandas/_libs/tslibs/timezones.pyx#L307

Then, when it tries to convert timestamps in UTC to timestamps in local timezone, it uses a bisect_right search to find the utc_transition time and offsets that match. https://github.com/pandas-dev/pandas/blob/main/pandas/_libs/tslibs/tzconversion.pyx#L134 But because the timestamp passed in is smaller than minimum transition time, and this logic assumes that the timestamp exists somewhere between min(utc_time) and max(utc_time) this results in that logic returning -1 as the index of the utc_transition.

Then when it goes to actually look up the offset, it tries to read index -1 here https://github.com/pandas-dev/pandas/blob/main/pandas/_libs/tslibs/vectorized.pyx#L169, and because in cython it tries to track references to objects, index -1 returns garbage data and results in a segfault.

[elefeint]

@szarnyasg is it too late for an extra validation to prevent this segfault to make it into 1.4.3?

[evertlammerts]

This also reproduces the bug, just using numpy and pandas (the same way we do under the hood):

import numpy as np
import pandas as pd

# Create a dataframe using the system's default tz
df = pd.DataFrame(np.array(['0001-01-01T00'], dtype='M8'), columns=['test'])
# Localize the column to UTC
local = df['test'].dt.tz_localize("UTC")
# Now localize it to the timezone we want...
new_col = local.dt.tz_convert('America/Los_Angeles')
# ... and printing / materializing it triggers the segfault
print(new_col)

For the record: tested with pandas 2.3.3 and numpy 1.26.4 and 2.3.5.

There's no quick fix for this in duckdb-python I can see right now. We could maybe prevent the segfault by proactively checking date ranges and time zones and throw an exception, but that's pretty hacky and is likely going to have an impact on performance for date ranges that most people will not care about.

The workaround is what you are doing: setting the timezone to UTC and then materializing the dataframe.

[evertlammerts]

As of Pandas 3 this doesn't segfault anymore:

>>> import duckdb
... conn = duckdb.connect(database=":memory:")
... conn.execute("SET TimeZone = 'America/Los_Angeles'") # Segfaults
... # conn.execute("SET TimeZone = 'UTC'") # does not segfault
... df = conn.execute("SELECT '0001-01-01 00:00:00'::timestamptz").df()
... print(df)
...
  CAST('0001-01-01 00:00:00' AS "TIMESTAMP WITH TIME ZONE")
0                       0001-01-01 00:00:00-07:52:58

Without DuckDB Pandas still throws an error, but no longer a segfault.