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

[j-r77]

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

[rchui]

Encountered a similar error when using order by all on a postgres table

( select * exclude (updated_at) from postgres.<schema>.<table> )
except
( select * exclude (row_id, updated_at) from test.<schema>.<table> ) order by all

Traceback (most recent call last):
  ...
  File "...", line 799, in compare
    local.sql(comparison_sql).show()
duckdb.duckdb.InternalException: INTERNAL Error: Unsupported table filter type

Stack Trace:

/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(+0x691210) [0xffff3ce85210]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(+0x691298) [0xffff3ce85298]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(+0x693c6c) [0xffff3ce87c6c]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(_ZN6duckdb22PostgresFilterPushdown15TransformFilterERNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEERNS_11TableFilterE+0x1528) [0xffff3ca0ee90]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(_ZN6duckdb22PostgresFilterPushdown15TransformFilterERNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEERNS_11TableFilterE+0xb90) [0xffff3ca0e4f8]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(_ZN6duckdb22PostgresFilterPushdown16TransformFiltersERKNS_6vectorImLb1EEENS_12optional_ptrINS_14TableFilterSetELb1EEERKNS1_INSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEELb1EEE+0x114) [0xffff3ca0fac4]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(+0x225c48) [0xffff3ca19c48]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(+0x2273ec) [0xffff3ca1b3ec]
/root/.duckdb/extensions/v1.2.0/linux_arm64/postgres_scanner.duckdb_extension(+0x2274fc) [0xffff3ca1b4fc]
/root/.duckdb/extensions/v1.2.0/linux_arm64/motherduck.v1.2.0-2025-02-238.duckdb_extension(+0x11e3264) [0xffff660df264]
/usr/local/lib/python3.10/site-packages/duckdb/duckdb.cpython-310-aarch64-linux-gnu.so(duckdb::PipelineExecutor::PipelineExecutor(duckdb::ClientContext&, duckdb::Pipeline&)+0x2e4) [0xffff8065a468]
/usr/local/lib/python3.10/site-packages/duckdb/duckdb.cpython-310-aarch64-linux-gnu.so(duckdb::PipelineTask::ExecuteTask(duckdb::TaskExecutionMode)+0x1c4) [0xffff80662124]
/usr/local/lib/python3.10/site-packages/duckdb/duckdb.cpython-310-aarch64-linux-gnu.so(duckdb::ExecutorTask::Execute(duckdb::TaskExecutionMode)+0xa8) [0xffff80657438]
/usr/local/lib/python3.10/site-packages/duckdb/duckdb.cpython-310-aarch64-linux-gnu.so(duckdb::TaskScheduler::ExecuteForever(std::atomic<bool>*)+0x130) [0xffff80660560]
/usr/lib/aarch64-linux-gnu/libstdc++.so.6(+0xc9cac) [0xffff88f88cac]
/lib/aarch64-linux-gnu/libpthread.so.0(+0x7648) [0xffff98175648]
/lib/aarch64-linux-gnu/libc.so.6(+0xd1c9c) [0xffff98270c9c]

This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors