PostgreSQL compatibility: negative array subscripts should not index from the end

Split out from #22247.

Mismatch

Negative array subscripts should not index from the end.

SELECT (array[10,20,30])[-1];

PostgreSQL:

NULL

DataFusion:

Expected behavior

For PostgreSQL-compatible SQL semantics, DataFusion should either match PostgreSQL's result or raise the same class of domain/semantic error.

take

DuckDB:

memory D SELECT (array[10,20,30])[-1];
┌─────────────────────────┐
│ (ARRAY[10, 20, 30])[-1] │
│          int32          │
├─────────────────────────┤
│                      30 │
└─────────────────────────┘

DuckDB does indeed work that way, so should we keep it as is? Returning 30 might be a better approach.

DuckDB intentionally implements Python-style negative indexing: arr[-1] = last element, arr[-k] = k-th from end, out-of-bounds → NULL. PG returns NULL for negative subscripts (no from-end semantics). DuckDB documents this through examples (['a','b','c'][-1] → 'c').

We can get consensus about which engine to follow for this.

I'm leaning towards the DuckDB approach and would like to get your thoughts.

I'm leaning towards the DuckDB approach and would like to get your thoughts.

I agree we should stick to the current approach unless we have some special use case for the change.

We can wait for Dan's suggestion, and if he agrees, I can close the PR at any time.

@Dandandan

HI @Dandandan , WDYT?

Spark also doesn't tolerate negative indices it seems (though it errors outright). Clickhouse seems to align with DuckDB.

I agree that negative indexing makes sense to me, since I'm also used to how Python treats it.

Another option is this PR:

fix: return NULL for negative array subscripts in PostgreSQL dialect #22294

Which ties it to the dialect setting

Another option is this PR:
* [fix: return NULL for negative array subscripts in PostgreSQL dialect #22294](https://github.com/apache/datafusion/pull/22294)
Which ties it to the dialect setting

Based on #22252, it seems like it should be tied to the dialect in cases where the behavior is conflicting, and where the postgres behavior subjective (unlike in cases like #22270).

Navigation Menu

Repository navigation

PostgreSQL compatibility: negative array subscripts should not index from the end #22254

Mismatch

Expected behavior

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

PostgreSQL compatibility: negative array subscripts should not index from the end #22254

Description

Mismatch

Expected behavior

Activity

xiedeyantu commented on May 16, 2026

kumarUjjawal commented on May 18, 2026

xiedeyantu commented on May 18, 2026

kumarUjjawal commented on May 18, 2026

xiedeyantu commented on May 19, 2026

kumarUjjawal commented on May 19, 2026

xiedeyantu commented on May 19, 2026

xiedeyantu commented on May 22, 2026

Jefffrey commented on May 26, 2026

Jefffrey commented on May 26, 2026

ngli-me commented on Jun 8, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions