Typing stubs are too strict about arguments of type `Expression`

[frankier]

What happens?

It seems that many types are convertible to Expression. It looks like the casting is done in C++. Currently Python typecheckers will complain because the stubs say many methods can only take Expression when it's fine to pass any of the types convertable to Expression.

To Reproduce

db = duckdb.connect(":memory")
db.execute("CREATE TABLE ducks AS SELECT 3 AS age, 'mandarin' AS breed;")
db.table("ducks").sort("age")
db.table("ducks").sort(1)

If you type check with pyright, you'll get something like:

Argument of type "Literal['age']" cannot be assigned to parameter "args" of type "Expression" in function "sort"

OS:

Linux

DuckDB Package Version:

1.4.4

Python Version:

3.13

Full Name:

Frankie Robertson

Affiliation:

University of Helsinki

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?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[OutSquareCapital]

Creating type aliases and using them across the stubs would be a good solution.
I've been working a lot on this currently in my current project and would be happy to make a PR since I have already existing code that could help solve this (see here for example: https://github.com/OutSquareCapital/pql/blob/master/src/pql/sql/typing.py)
However where it gets tricky is at runtime for Iterable types:

• Sometimes they are strictly checked vs a precise expected object, for example in ConstantExpression, who won't accept a custom Iterable, only tuple/list/dict
  

  duckdb-python/src/duckdb_py/pyexpression.cpp

  Line 348 in 89ed9a1

  shared_ptr<DuckDBPyExpression> DuckDBPyExpression::ConstantExpression(const py::object &value) {

• Sometimes they are casted and hence can accept corresponding collections.abc compliant objects , for example the exclude argument in StarExpression who can accept any Iterable
  

  duckdb-python/src/duckdb_py/pyexpression.cpp

  Line 288 in 89ed9a1

  static void PopulateExcludeList(qualified_column_set_t &exclude, py::object list_p) {

The second behavior is highly preferable to the first IMO (just gotta be careful with str inputs, since they are themselves Iterable of chars)

The first behavior seems to use this logic to get the actual type:

duckdb-python/src/duckdb_py/native/python_conversion.cpp

Line 398 in 89ed9a1

PythonObjectType GetPythonObjectType(py::handle &ele) {

Who then dispatch the handling logic to this:

duckdb-python/src/duckdb_py/native/python_conversion.cpp

Line 544 in 89ed9a1

static void HandleList(Value &result, const LogicalType &target_type, py::handle ele, idx_t list_size) {

Since the tuple path can also handles struct conversions, maintaining this separated make sense (even if I'm not a fan of this cause separating logic based on the container type instead of the elements types is suprising IMO).

However the HandleListcould accept any Collection who's not a tuple, since only iteration and length check are needed (https://docs.python.org/3/library/collections.abc.html)
My point is: prioritising collections.abc at all steps would both improve user experience(less runtime errors, better type hints) and performance (no need to manually convert in python when you pass a custom container)

I'm pretty sure there's other cases where the static types hints and/or the runtime type checks are too strict for containers types. Feel free to LMK. My goal would be to be as exhaustive as possible without resortign to Any type hints

[evertlammerts]

@OutSquareCapital I'm only reading your comment now, after putting the PR up. It looks like what I'm doing is the poor man's version of your approach to expressions. IntoExprColumn is cleaner than what we're doing in this PR and if you can put up a PR, it's very welcome.

Re iterables, nice job on the deep dive, there's definitely work to do there. I'm not very likely to get to that any time soon, so contributions on that are welcome as well.

[evertlammerts]

I've landed the expression-related fix PR, you can base your fixes on that, @OutSquareCapital. I'll close this ticket in the meantime, we can have more discussion on your PR.