Repository: duckdb/duckdb (language: python)

Issue:
Ungraceful Handling of Apache Arrow `ArrowNotImplementedError` with `timestamp` Datatype
### What happens?

Executing a query against an Apache Arrow table from LanceDB that includes a column of datatype `timestamp[ns]`:
- FAILS if the query contains a `WHERE` clause;
- SUCCEEDS if the query does _not_ contain a `WHERE` clause.

Specifically, this behavior seems to be present when creating a LanceDB table from a `pandas.DataFrame`.

### To Reproduce

## Minimum Reproducible Example

```python
import lancedb
import duckdb
import pandas as pd

# Connect to a fresh database
db = lancedb.connect("./test_db")

# Create a LanceDB table (uses Apache Arrow)
data = pd.DataFrame({
    "corpus": ["day", "night"],
    "date": [pd.Timestamp(1708951076175900000, unit="ns")] * 2
})
table = db.create_table('test-table', data=data, exist_ok=True, mode="overwrite")

print("✨ Table Schema:\n", table.schema, "\n\n")

# Convert the "Table" to a "dataset" which is an Apache Arrow
dataset = table.to_lance() # type: ignore (it works :shrug:)

# Succeeds
print("✅ ", duckdb.execute('SELECT * FROM dataset').arrow(), "\n\n")

args = {"corpus": "day"}

# Fails
print(duckdb.execute('SELECT * FROM dataset WHERE corpus LIKE $corpus', args).arrow())
```

## Output:
```bash
✨ Table Schema:
corpus: string
date: timestamp[ns] 


✅  pyarrow.Table
corpus: string
date: timestamp[ns]
----
corpus: [["day","night"]]
date: [[2024-02-26 12:37:56.175900000,2024-02-26 12:37:56.175900000]] 


Traceback (most recent call last):
  File "<redacted>", line 39, in <module>
    test()
  File "<redacted>", line 34, in test
    print(duckdb.execute('SELECT * FROM dataset WHERE corpus LIKE $corpus', args).arrow())
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
duckdb.duckdb.InvalidInputException: Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result
Error: Invalid Error: ArrowNotImplementedError: conversion to substrait::Type from timestamp[ns]

At:
  pyarrow/error.pxi(91): pyarrow.lib.check_status
  ```

> [!Important]
> It turns out that if you replace the `data` variable with a list of dicts instead of a DataFrame, there are no errors. Curious!
> ```python
>data = [
>    {"corpus": "day", "date": pd.Timestamp(1708951076175900000, unit="ns")},
>    {"corpus": "night", "date": pd.Timestamp(1708951076175900000, unit="ns")},
>]
>```

### OS:

Linux (Ubuntu x64)

### DuckDB Version:

0.10.1

### DuckDB Client:

Python (Python 3.11)

### Full Name:

John

### Affiliation:

N/A

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have
Query on Arrow views fail with specific order of filters
### What happens?

When executing a valid SQL query against an arrow registered table, a weird error pops up: 

```Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result
Error: Not implemented Error: DatetimeType not recognized in ConvertTimestampUnit: 4```

Changing the order of the filters or query the arrow table as a variable directly does not result in an error - given the GH format limitation we uploaded a reproducible code with all the options and the original arrow file from the taxi dataset we use.

Please note that in our system we rely on explicit registering the python objects as views so this bug is actually blocking us.

### To Reproduce

* download the arrow file [here](https://drive.google.com/file/d/1svr_up221yTLWTWzjWyOkwDuBbfZWJ8G/view?usp=sharing)
* install a virtual env with the
[requirements.txt](https://github.com/user-attachments/files/15568772/requirements.txt)
* run the following code

```python
import pyarrow as pa
import pyarrow.ipc as ipc
import duckdb
import pyarrow.compute as pc
from datetime import datetime


# Read the table from the Arrow file

def read_arrow_file(file_path):
    with pa.memory_map(file_path, 'r') as source:
        reader = ipc.RecordBatchFileReader(source)
        return reader.read_all()

taxi_fhvhv_arrow = read_arrow_file('arrow_table')
print("Arrow table loaded")
print(taxi_fhvhv_arrow.schema, taxi_fhvhv_arrow.num_rows)

print('\n---------- Pyarrow version  ----------\n')

# query with pyarrow directly to have an arrow only baseline as a gold standard
expr = (pc.field("pickup_datetime") >= pa.scalar(datetime(2023, 1, 1, 5, 0, 0), type=pa.timestamp('us', tz='America/New_York'))) & \
    (pc.field("pickup_datetime") < pa.scalar(datetime(2023, 1, 1, 15, 0, 0), type=pa.timestamp('us', tz='America/New_York'))) & \
    (pc.field("PULocationID") == 244)
table = taxi_fhvhv_arrow.filter(expr)
print(table.schema, table.num_rows)

# double check the number of rows
# table = taxi_fhvhv_arrow.filter(pc.field("PULocationID") == 244)
# print(table.schema, table.num_rows)

print('\n----------\n')

# instantiate a DuckDB connection
con = duckdb.connect(database=':memory:')
con.execute("SET TimeZone='UTC';")

try:
    # query with replacement scan
    rows = con.execute("SELECT PULocationID, pickup_datetime FROM taxi_fhvhv_arrow WHERE pickup_datetime >= '2023-01-01T00:00:00-05:00' AND pickup_datetime < '2023-01-01T10:00:00-05:00' AND PULocationID = 244").arrow()
    print(rows.schema, rows.num_rows)
except Exception as e:
    print('!!!! Query error !!!!')
    print(e)

print('\n----------\n')

try:
    # query with register view
    con.register('taxi_fhvhv', taxi_fhvhv_arrow)
    rows = con.execute("SELECT PULocationID, pickup_datetime FROM taxi_fhvhv WHERE PULocationID = 244 AND pickup_datetime >= '2023-01-01T00:00:00-05:00' AND pickup_datetime < '2023-01-01T10:00:00-05:00'").arrow()
    print(rows.schema, rows.num_rows)
except Exception as e:
    print('!!!! Query error !!!!')
    print(e)

print('\n----------\n')
    
try:
    # query with register view but different filters
    con.register('taxi_fhvhv', taxi_fhvhv_arrow)
    rows = con.execute("SELECT PULocationID, pickup_datetime FROM taxi_fhvhv WHERE pickup_datetime >= '2023-01-01T00:00:00-05:00' AND pickup_datetime < '2023-01-01T10:00:00-05:00' AND PULocationID = 244").arrow()
    print(rows.schema, rows.num_rows)
except Exception as e:
    print('!!!! Query error !!!!')
    print(e)
    
print('\n----------\n')
    
try:
    # query with register view but only the equality filter
    con.register('taxi_fhvhv', taxi_fhvhv_arrow)
    rows = con.execute("SELECT PULocationID, pickup_datetime FROM taxi_fhvhv WHERE PULocationID = 244").arrow()
    print(rows.schema, rows.num_rows)
except Exception as e:
    print('!!!! Query error !!!!')
    print(e)
    
print('\n----------\n')
    
try:
    # query with register view but only the datetime filter
    con.register('taxi_fhvhv', taxi_fhvhv_arrow)
    rows = con.execute("SELECT PULocationID, pickup_datetime FROM taxi_fhvhv WHERE pickup_datetime >= '2023-01-01T00:00:00-05:00' AND pickup_datetime < '2023-01-01T10:00:00-05:00'").arrow()
    print(rows.schema, rows.num_rows)
except Exception as e:
    print('!!!! Query error !!!!')
    print(e)
```

You will notice that the order in which filters are used in the query will produce (or not) an error - we also have a pure arrow implementation to begin with to make sure there is no corruption etc. In particular, this `pickup_datetime >= '2023-01-01T00:00:00-05:00' AND pickup_datetime < '2023-01-01T10:00:00-05:00' AND PULocationID = 244` generates an error but  moving the last condition first won't.


### OS:

Mac

### DuckDB Version:

1.0.0

### DuckDB Client:

Python

### Full Name:

Jacopo Tagliabue

### Affiliation:

Bauplan

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

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

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

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

- [X] Yes, I have


Discussion / hints:
Hey thanks for raising the issue, did you also raise a corresponding issue on Arrow?
As I mentioned this is caused by pushdown of the filter into Arrow, they are causing this error

All we can do is handle the error more gracefully :)
I can open an issue on `arrow`, but did want to come back and mention one finding before that. 

It appears that, in my testing, the issue only happens when using the `LIKE` statement. If you use `ILIKE` instead, it works just fine. 

Do you still suspect this is an `arrow` issue?
That likely doesn't get pushed down, so yes, please make an issue in Arrow 👍 
This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.
```
# Read the table from the Arrow file

def read_arrow_file(file_path):
    with pa.memory_map(file_path, 'r') as source:
        reader = ipc.RecordBatchFileReader(source)
        return reader.read_all()

taxi_fhvhv_arrow = read_arrow_file('arrow_table')
```

You did not provide this file
thanks for the reply @Tishj  - I actually did >

```
* download the arrow file [here](https://drive.google.com/file/d/1svr_up221yTLWTWzjWyOkwDuBbfZWJ8G/view?usp=sharing)
```

let me know if you cant download it - it's an open link.
Hi, I'm experiencing the same issue described in this thread.
As of today, it appears that this problem has not yet been addressed. 

Has there been any progress or are there any updates on this?

Thanks

Fix patch:
diff --git a/tools/pythonpkg/src/arrow/arrow_array_stream.cpp b/tools/pythonpkg/src/arrow/arrow_array_stream.cpp
index 7becadea3205..8a78927aeea2 100644
--- a/tools/pythonpkg/src/arrow/arrow_array_stream.cpp
+++ b/tools/pythonpkg/src/arrow/arrow_array_stream.cpp
@@ -391,16 +391,17 @@ py::object PythonTableArrowArrayStreamFactory::TransformFilter(TableFilterSet &f
 	auto filters_map = &filter_collection.filters;
 	auto it = filters_map->begin();
 	D_ASSERT(columns.find(it->first) != columns.end());
-	auto &arrow_type = *arrow_table.GetColumns().at(filter_to_col.at(it->first));
+	auto arrow_type = &arrow_table.GetColumns().at(filter_to_col.at(it->first));
 
 	vector<string> column_ref;
 	column_ref.push_back(columns[it->first]);
-	py::object expression = TransformFilterRecursive(it->second.get(), column_ref, config.time_zone, arrow_type);
+	py::object expression = TransformFilterRecursive(it->second.get(), column_ref, config.time_zone, **arrow_type);
 	while (it != filters_map->end()) {
+		arrow_type = &arrow_table.GetColumns().at(filter_to_col.at(it->first));
 		column_ref.clear();
 		column_ref.push_back(columns[it->first]);
 		py::object child_expression =
-		    TransformFilterRecursive(it->second.get(), column_ref, config.time_zone, arrow_type);
+		    TransformFilterRecursive(it->second.get(), column_ref, config.time_zone, **arrow_type);
 		expression = expression.attr("__and__")(child_expression);
 		it++;
 	}


Test patch:
diff --git a/tools/pythonpkg/tests/fast/arrow/data/arrow_table b/tools/pythonpkg/tests/fast/arrow/data/arrow_table
new file mode 100644
index 000000000000..695946c12ee8
Binary files /dev/null and b/tools/pythonpkg/tests/fast/arrow/data/arrow_table differ
diff --git a/tools/pythonpkg/tests/fast/arrow/test_12384.py b/tools/pythonpkg/tests/fast/arrow/test_12384.py
new file mode 100644
index 000000000000..af9c8ed2478c
--- /dev/null
+++ b/tools/pythonpkg/tests/fast/arrow/test_12384.py
@@ -0,0 +1,20 @@
+import duckdb
+import pytest
+import os
+
+pa = pytest.importorskip('pyarrow')
+
+
+def test_10795():
+    arrow_filename = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'data', 'arrow_table')
+    with pa.memory_map(arrow_filename, 'r') as source:
+        reader = pa.ipc.RecordBatchFileReader(source)
+        taxi_fhvhv_arrow = reader.read_all()
+        con = duckdb.connect(database=':memory:')
+        con.execute("SET TimeZone='UTC';")
+        con.register('taxi_fhvhv', taxi_fhvhv_arrow)
+        res = con.execute(
+            "SELECT PULocationID, pickup_datetime FROM taxi_fhvhv WHERE pickup_datetime >= '2023-01-01T00:00:00-05:00' AND PULocationID = 244"
+        ).fetchall()
+
+        assert len(res) == 3685