Spatial Index Causing a "Failed to bind column reference" Crash

[youngpm]

Hello! In using duckdb 1.2.0, I just encountered the a crash when mixing a spatial predicate with with other columns in the where clause.

To reproduce:

CREATE TABLE land AS SELECT * FROM read_parquet('s3://overturemaps-us-west-2/release/2024-09-18.0/theme=base/type=land/*') WHERE bbox.xmin < -73.9654541015625 AND bbox.xmax > -73.970947265625 AND bbox.ymin < 40.772221877329024 AND bbox.ymax > 40.768061709366116;
SELECT id FROM land WHERE subtype = 'tree' AND ST_Intersects(geometry, ST_MakeEnvelope(-73.970947265625, 40.768061709366116, -73.9654541015625, 40.772221877329024));

will report 49 ids.

Now add a spatial index:

CREATE INDEX land_geom_idx ON land USING RTREE (geometry);

and running the same SELECT will crash like so:

INTERNAL Error:
Failed to bind column reference "id" [0.2] (bindings: {#[0.1]})

Stack Trace:

0        duckdb::Exception::ToJSON(duckdb::ExceptionType, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) + 40
1        duckdb::Exception::Exception(duckdb::ExceptionType, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) + 36
2        duckdb::InternalException::InternalException(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) + 20
3        duckdb::InternalException::InternalException<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, unsigned long long, unsigned long long, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, unsigned long long, unsigned long long, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>) + 164
4        duckdb::ColumnBindingResolver::VisitReplace(duckdb::BoundColumnRefExpression&, duckdb::unique_ptr<duckdb::Expression, std::__1::default_delete<duckdb::Expression>, true>*) + 324
5        duckdb::LogicalOperatorVisitor::VisitExpression(duckdb::unique_ptr<duckdb::Expression, std::__1::default_delete<duckdb::Expression>, true>*) + 476
6        duckdb::LogicalOperatorVisitor::EnumerateExpressions(duckdb::LogicalOperator&, std::__1::function<void (duckdb::unique_ptr<duckdb::Expression, std::__1::default_delete<duckdb::Expression>, true>*)> const&) + 852
7        duckdb::LogicalOperatorVisitor::VisitOperatorExpressions(duckdb::LogicalOperator&) + 68
8        duckdb::ColumnBindingResolver::VisitOperator(duckdb::LogicalOperator&) + 1000
9        duckdb::PhysicalPlanGenerator::CreatePlan(duckdb::unique_ptr<duckdb::LogicalOperator, std::__1::default_delete<duckdb::LogicalOperator>, true>) + 96
10       duckdb::ClientContext::CreatePreparedStatementInternal(duckdb::ClientContextLock&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, duckdb::optional_ptr<std::__1::unordered_map<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, duckdb::BoundParameterData, duckdb::CaseInsensitiveStringHashFunction, duckdb::CaseInsensitiveStringEquality, std::__1::allocator<std::__1::pair<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const, duckdb::BoundParameterData>>>, true>) + 1112
11       duckdb::ClientContext::CreatePreparedStatement(duckdb::ClientContextLock&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, duckdb::optional_ptr<std::__1::unordered_map<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, duckdb::BoundParameterData, duckdb::CaseInsensitiveStringHashFunction, duckdb::CaseInsensitiveStringEquality, std::__1::allocator<std::__1::pair<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const, duckdb::BoundParameterData>>>, true>, duckdb::PreparedStatementMode) + 476
12       duckdb::ClientContext::PendingStatementInternal(duckdb::ClientContextLock&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, duckdb::PendingQueryParameters const&) + 128
13       duckdb::ClientContext::PendingStatementOrPreparedStatement(duckdb::ClientContextLock&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, duckdb::shared_ptr<duckdb::PreparedStatementData, true>&, duckdb::PendingQueryParameters const&) + 256
14       duckdb::ClientContext::PendingStatementOrPreparedStatementInternal(duckdb::ClientContextLock&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, duckdb::shared_ptr<duckdb::PreparedStatementData, true>&, duckdb::PendingQueryParameters const&) + 1184
15       duckdb::ClientContext::PendingQueryInternal(duckdb::ClientContextLock&, duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, duckdb::PendingQueryParameters const&, bool) + 152
16       duckdb::ClientContext::PendingQuery(duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, std::__1::unordered_map<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, duckdb::BoundParameterData, duckdb::CaseInsensitiveStringHashFunction, duckdb::CaseInsensitiveStringEquality, std::__1::allocator<std::__1::pair<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const, duckdb::BoundParameterData>>>&, bool) + 196
17       duckdb::ClientContext::PendingQuery(duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, bool) + 60
18       duckdb::Connection::PendingQuery(duckdb::unique_ptr<duckdb::SQLStatement, std::__1::default_delete<duckdb::SQLStatement>, true>, bool) + 64
19       duckdb_shell_sqlite3_prepare_v2 + 1060
20       duckdb_shell::ShellState::ExecuteSQL(char const*, char**) + 128
21       duckdb_shell::ShellState::RunOneSqlLine(char*) + 104
22       duckdb_shell::ShellState::ProcessInput() + 916
23       main + 3140
24       start + 2840

If you return subtype in addition to id, everything works as expected.

[youngpm]

I should say duckdb 1.1.3 was fine, so seems to be a new error.

[Maxxen]

Hello! I think this was fixed in #509. Could you try with the nightly build of the v1.2.0 extension? FORCE INSTALL spatial FROM core_nightly

[Maxxen]

Nope, turns out this is another issue!

[Maxxen]

This has now been fixed