"INTERNAL Error: Unsupported table filter type" when combining limit and order by

The stack trace points to PostgresFilterPushdown::TransformFilter and TransformFilters; start there and reproduce the attached-PostgreSQL query in the Python snippet with DuckDB 1.2.0. Done means ORDER BY with LIMIT no longer raises “Unsupported table filter type” for this case, with the behavior checked against the provided reproduction.

What happens?

When using DuckDB 1.2.0, I receive an internal if I combine limit and order by in a query to an attached postgres (cloud SQL) database. The below code works on 1.1.3, and also works if I comment out either the ORDER BY or the LIMIT. If I use both, I get a stack trace.

duckdb.duckdb.InternalException: INTERNAL Error: Unsupported table filter type

Stack Trace:

0 _ZN6duckdb9ExceptionC2ENS_13ExceptionTypeERKNSt3__112basic_stringIcNS2_11char_traitsIcEENS2_9allocatorIcEEEE + 64
1 _ZN6duckdb17InternalExceptionC1ERKNSt3__112basic_stringIcNS1_11char_traitsIcEENS1_9allocatorIcEEEE + 20
2 _ZN6duckdb22PostgresFilterPushdown15TransformFilterERNSt3__112basic_stringIcNS1_11char_traitsIcEENS1_9allocatorIcEEEERNS_11TableFilterE + 1848
3 _ZN6duckdb22PostgresFilterPushdown16TransformFiltersERKNS_6vectorIyLb1EEENS_12optional_ptrINS_14TableFilterSetELb1EEERKNS1_INSt3__112basic_stringIcNS8_11char_traitsIcEENS8_9allocatorIcEEEELb1EEE + 212
4 _ZN6duckdbL20PostgresInitInternalERNS_13ClientContextEPKNS_16PostgresBindDataERNS_18PostgresLocalStateEyy + 620
5 _ZN6duckdbL13GetLocalStateERNS_13ClientContextERNS_22TableFunctionInitInputERNS_19PostgresGlobalStateE + 384
6 duckdb::TableScanLocalSourceState::TableScanLocalSourceState(duckdb::ExecutionContext&, duckdb::TableScanGlobalSourceState&, duckdb::PhysicalTableScan const&) + 188
7 duckdb::PhysicalTableScan::GetLocalSourceState(duckdb::ExecutionContext&, duckdb::GlobalSourceState&) const + 64
8 duckdb::PipelineExecutor::PipelineExecutor(duckdb::ClientContext&, duckdb::Pipeline&) + 508
9 duckdb::PipelineTask::ExecuteTask(duckdb::TaskExecutionMode) + 84
10 duckdb::ExecutorTask::Execute(duckdb::TaskExecutionMode) + 236
11 duckdb::Executor::ExecuteTask(bool) + 604
12 duckdb::ClientContext::ExecuteTaskInternal(duckdb::ClientContextLock&, duckdb::BaseQueryResult&, bool) + 64
13 duckdb::PendingQueryResult::ExecuteTask() + 64
14 duckdb::DuckDBPyConnection::CompletePendingQuery(duckdb::PendingQueryResult&) + 92
15 duckdb::DuckDBPyConnection::PrepareAndExecuteInternal(duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_deleteduckdb::SQLStatement, true>, pybind11::object) + 232
16 duckdb::DuckDBPyConnection::Execute(pybind11::object const&, pybind11::object) + 284
17 duckdb::shared_ptr<duckdb::DuckDBPyConnection, true> pybind11::detail::argument_loader<duckdb::DuckDBPyConnection*, pybind11::object const&, pybind11::object>::call_impl<duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>, pybind11::cpp_function::cpp_function<duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>, duckdb::DuckDBPyConnection, pybind11::object const&, pybind11::object, pybind11::name, pybind11::is_method, pybind11::sibling, char [86], pybind11::arg, pybind11::arg_v>(duckdb::shared_ptr<duckdb::DuckDBPyConnection, true> (duckdb::DuckDBPyConnection::)(pybind11::object const&, pybind11::object), pybind11::name const&, pybind11::is_method const&, pybind11::sibling const&, char const (&) [86], pybind11::arg const&, pybind11::arg_v const&)::'lambda'(duckdb::DuckDBPyConnection, pybind11::object const&, pybind11::object)&, 0ul, 1ul, 2ul, pybind11::detail::void_type>(duckdb::DuckDBPyConnection&&, pybind11::detail::index_sequence<0ul, 1ul, 2ul>, pybind11::detail::void_type&&) && + 68
18 void pybind11::cpp_function::initialize<pybind11::cpp_function::cpp_function<duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>, duckdb::DuckDBPyConnection, pybind11::object const&, pybind11::object, pybind11::name, pybind11::is_method, pybind11::sibling, char [86], pybind11::arg, pybind11::arg_v>(duckdb::shared_ptr<duckdb::DuckDBPyConnection, true> (duckdb::DuckDBPyConnection::)(pybind11::object const&, pybind11::object), pybind11::name const&, pybind11::is_method const&, pybind11::sibling const&, char const (&) [86], pybind11::arg const&, pybind11::arg_v const&)::'lambda'(duckdb::DuckDBPyConnection, pybind11::object const&, pybind11::object), duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>, duckdb::DuckDBPyConnection*, pybind11::object const&, pybind11::object, pybind11::name, pybind11::is_method, pybind11::sibling, char [86], pybind11::arg, pybind11::arg_v>(duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>&&, duckdb::DuckDBPyConnection ()(pybind11::object const&, pybind11::object), pybind11::name const&, pybind11::is_method const&, pybind11::sibling const&, char const (&) [86], pybind11::arg const&, pybind11::arg_v const&)::'lambda'(pybind11::detail::function_call&)::operator()(pybind11::detail::function_call&) const + 96
19 void pybind11::cpp_function::initialize<pybind11::cpp_function::cpp_function<duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>, duckdb::DuckDBPyConnection, pybind11::object const&, pybind11::object, pybind11::name, pybind11::is_method, pybind11::sibling, char [86], pybind11::arg, pybind11::arg_v>(duckdb::shared_ptr<duckdb::DuckDBPyConnection, true> (duckdb::DuckDBPyConnection::)(pybind11::object const&, pybind11::object), pybind11::name const&, pybind11::is_method const&, pybind11::sibling const&, char const (&) [86], pybind11::arg const&, pybind11::arg_v const&)::'lambda'(duckdb::DuckDBPyConnection*, pybind11::object const&, pybind11::object), duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>, duckdb::DuckDBPyConnection*, pybind11::object const&, pybind11::object, pybind11::name, pybind11::is_method, pybind11::sibling, char [86], pybind11::arg, pybind11::arg_v>(duckdb::shared_ptr<duckdb::DuckDBPyConnection, true>&&, duckdb::DuckDBPyConnection ()(pybind11::object const&, pybind11::object), pybind11::name const&, pybind11::is_method const&, pybind11::sibling const&, char const (&) [86], pybind11::arg const&, pybind11::arg_v const&)::'lambda'(pybind11::detail::function_call&)::__invoke(pybind11::detail::function_call&) + 24
20 pybind11::cpp_function::dispatcher(_object, _object*, _object*) + 3972
21 cfunction_call + 72
22 _PyObject_MakeTpCall + 128
23 _PyEval_EvalFrameDefault + 44144
24 PyEval_EvalCode + 184
25 run_eval_code_obj + 88
26 run_mod + 132
27 pyrun_file + 156
28 _PyRun_SimpleFileObject + 288
29 _PyRun_AnyFileObject + 80
30 pymain_run_file_obj + 164
31 pymain_run_file + 72
32 Py_RunMain + 760
33 pymain_main + 304
34 Py_BytesMain + 40
35 start + 2840

To Reproduce

conn = duckdb.connect()
conn.execute("INSTALL postgres; LOAD postgres; ")
conn.execute(f"ATTACH '{pg_cred}' AS db (TYPE POSTGRES, READ_ONLY, SCHEMA 'public');")

print(conn.execute(f"""
SELECT *
FROM db.public.alembic_version
ORDER BY 1
LIMIT 5
""").df())

OS:

macOS Sequoia 15.3.1

PostgreSQL Version:

14.13 (Google Cloud SQL)

DuckDB Version:

1.2.0

DuckDB Client:

Python 3.12

Full Name:

Joeri Rammelaere

Affiliation:

Froomle

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree