INTERNAL Error: Attempted to access index 2 within vector of size 2 in WHERE when using spatial index

[youngpm]

Hello,

I am getting the following error when my WHERE has a spatial query and another condition, when I don't return the value used in the other condition. I'm using v1.1.1 af39bd0dcf of the CLI here, on a Mac running Sonoma 14.7:

SELECT id FROM segment WHERE subtype='road' AND ST_Intersects(geometry, ST_Buffer(ST_GeomFromText('POINT (-8476562 4795814)'), 100));

INTERNAL Error: Attempted to access index 2 within vector of size 2

If I do a SELECT id, subtype FROM ... it works as expected.

To reproduce:

create table segment as select * from 's3://overturemaps-us-west-2/release/2024-09-18.0/theme=transportation/type=segment/part-00014-64fa5d4b-991b-493d-ad9c-41edcfbd4776-c000.zstd.parquet';
UPDATE segment SET geometry = ST_TRANSFORM(geometry, 'EPSG:4326', 'EPSG:3857', always_xy := true);
create index segment_geom_idx on segment USING RTREE (geometry);

Then, this will work:

SELECT id, subtype FROM segment WHERE subtype='road' AND ST_Intersects(geometry, ST_Buffer(ST_GeomFromText('POINT (-8476562 4795814)'), 100));
┌──────────────────────────────────┬─────────┐
│                id                │ subtype │
│             varchar              │ varchar │
├──────────────────────────────────┼─────────┤
│ 0862aac667ffffff047ffedcbc2db0f8 │ road    │
│ 0862aac667ffffff047de7f2111f86ad │ road    │
│ 0862aac667ffffff043df7e4c6756d14 │ road    │
└──────────────────────────────────┴─────────┘

but this will fail:

D SELECT id FROM segment WHERE subtype='road' AND ST_Intersects(geometry, ST_Buffer(ST_GeomFromText('POINT (-8476562 4795814)'), 100));
INTERNAL Error: Attempted to access index 2 within vector of size 2
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

Without creating the spatial index, it works, so some interaction there.

[youngpm]

Further strangeness, some of the filters appear to be ignored, e.g.:

SELECT id, subtype, class FROM segment WHERE subtype='road' AND class='residential' AND ST_Intersects(geometry, ST_Buffer(ST_GeomFromText('POINT (-8476562 4795814)'), 100));
┌──────────────────────────────────┬─────────┬─────────────┐
│                id                │ subtype │    class    │
│             varchar              │ varchar │   varchar   │
├──────────────────────────────────┼─────────┼─────────────┤
│ 0862aac667ffffff047ffedcbc2db0f8 │ road    │ residential │
│ 0862aac667ffffff047de7f2111f86ad │ road    │ residential │
│ 0862aac667ffffff043df7e4c6756d14 │ road    │ secondary   │
└──────────────────────────────────┴─────────┴─────────────┘

shouldn't have that class = secondary in there...

[Maxxen]

Hello! Im unable to reproduce this myself... does this behavior occur 100% of the time or is it spurious? What platform are you on?

[Maxxen]

Ah, no wait, I think I got it, hold on

[youngpm]

On a Mac running Sonoma 14.7

[Maxxen]

Ok, I think I've figured out the issue. The extra AND subtype='road' predicate is expected to get "pushed down" and fully evaluate within the table scan, and the subtype columns is therefore removed from the plan. But we don't realize this when we swap the table scan with the index scan, which outputs all columns, expecting that subtype will be used in a filter further up. That's why the index-out-of-bounds error gets triggered, we're trying to write out more columns than expected. If you omit the subtype=road predicate it works as expected, even if you don't SELECT the geometry column itself.

It's pretty late over here so I'll get started on a fix when I get into the office tomorrow. Thanks a lot for reporting this issue!