duckdb / duckdb/duckdb-python

Crashes while handling non-select result set (DataFrame)

Open
#428 0 comments 1 reaction 0 assignees View on GitHub
needs triage
Dominant language
Python
Stars
187
Forks
112
Avg merge
13h 29m
Merged PRs (30d)
17

Description

### What happens?

Hi.

### Problem

As the result of a `sparl.sql("non-select")` where `non-select` is any SQL statement that is not a select, e.g., USE, INSERT, DROP, CREATE, ... the `sql()` function will correctly return an empty DataFrame, which is the behavior of the pyspark API.

However, that object crashes when using any of its APIs, because the internal `relation` object is None. The same applies when trying to create an empty DataFrame without columns. A

### Fix

I think the best fix would require fixing the underlying c++ Relation object from the duckdb C++ library to support an empty relation without columns. There are also a couple other fixes like allowing the underlying `duckdb.struct_type()` to have no fields. That would make the low-level API more robust and require less patching in the python layer.

Then the `DuckDBPyConnection::RunQuery` function needs to return an empty relation for non-select statement, instead of `nullptr`. All these fixes felt a bit overwhelming so I won't submit a patch.

### To Reproduce

Testcase. All this works with Spark.
```
@pytest.mark.parametrize("mode", ["pandas", "list", "non-select"])
def test_empty_sdf( spark_session_g, mode):
from pyspark.sql import functions as f
from pyspark.sql import types as t
import pandas as pd

spark = spark_session_g
if mode =="pandas":
sdf = spark.createDataFrame(pd.DataFrame(), t.StructType([]))
elif mode == "list":
sdf = spark.createDataFrame([], t.StructType([]))
else:
curr_db = spark.catalog.currentDatabase()
sdf = spark.sql(f"USE {curr_db}") # non-result set query

assert sdf.schema == t.StructType([])
assert sdf.columns == []
assert sdf.collect() == []
assert sdf.toPandas().empty
assert sdf.toArrow().shape == (0, 0)
sdf.createOrReplaceTempView("my_vv1")
assert spark.sql("SELECT * from my_vv1").toArrow().shape == (0, 0)
sdf.show() # no-op, no crash
assert sdf.withColumn("col1", f.lit(1)).columns == ["col1"]
assert sdf.withColumns({"col1": f.lit(1)}).columns == ["col1"]
assert sdf.drop("noop").columns == []
```

### OS:

Any

### DuckDB Package Version:

Main branch

### Python Version:

3.12

### Full Name:

João Eiras

### Affiliation:

private

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

I have tested with a source build

### 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 to reproduce the issue?

- [x] Yes, I have

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.