List slicing syntax and behaviour

[taniabogatsch]

Slicing currently uses NULL as the unbounded marker for both list and string slicing, e.g. SELECT l[NULL:NULL] FROM (SELECT array[1, 2, 3] AS l).

This is a workaround(?) for arr[:] (Python), which then inserts a None object. Although DuckDB also supports the syntax SELECT l[:] FROM (SELECT array[1, 2, 3] AS l...

For Postgres compatibility [NULL:NULL] would have to return a NULL.

Additionally, we return NULL instead of an empty list (Postgres) if both indexes are less than zero and their absolute values are outside of the list length.

Below are some examples.

Data creation.

CREATE TABLE lists (s varchar[], off integer, length integer);
INSERT INTO lists VALUES (array['h', 'e', 'l', 'l', 'o'], 1, 2),
      (array['w', 'o', 'r', 'l', 'd'], 2, 3), 
      (array['b'], 0, 1), 
      (NULL, 0, 2);
SELECT * FROM lists;
┌─────────────────┬─────┬────────┐
│        s        │ off │ length │
├─────────────────┼─────┼────────┤
│ [h, e, l, l, o] │ 1   │ 2      │
│ [w, o, r, l, d] │ 2   │ 3      │
│ [b]             │ 0   │ 1      │
│                 │ 0   │ 2      │
└─────────────────┴─────┴────────┘
CREATE VIEW hello AS SELECT s AS hello FROM lists WHERE off = 1 AND length = 2;

DuckDB examples.

SELECT hello[NULL:length+NULL] FROM lists, hello;
┌─────────────────────────────┐
│ hello[NULL:(length + NULL)] │
├─────────────────────────────┤
│ [h, e, l, l, o]             │
│ [h, e, l, l, o]             │
│ [h, e, l, l, o]             │
│ [h, e, l, l, o]             │
└─────────────────────────────┘

SELECT s[(-2147):-2147] IS NULL FROM lists;
┌──────────────────────────┐
│ (s[-2147:-2147] IS NULL) │
├──────────────────────────┤
│ true                     │
│ true                     │
│ true                     │
│ true                     │
└──────────────────────────┘

Postgres examples.

SELECT hello[NULL:length+NULL] IS NULL FROM lists, hello;
 ?column? 
----------
 t
 t
 t
 t
(4 rows)

SELECT s[(-2147):-2147] FROM lists;
 s  
----
 {}
 {}
 {}
 
(4 rows)

[Mytherin]

I'm in favor of following Postgres here when possible. We should be able to support the : syntax without inserting NULLs. In fact, Postgres does so as well, as this is also valid in Postgres:

postgres=# select i[:] from arrays;
           i            
------------------------
 {1,2,3,4,5,6,7,8,9,10}
(1 row)

postgres=# select i[NULL:NULL] from arrays;
 i 
---
 
(1 row)

[Tishj]

Feels like this is the right issue to post this:
I thought we also supported providing a step amount to list slicing, but it appears we don't?

D select '12345'[3:5:1];
Error: Parser Error: syntax error at or near ":"
LINE 1: select '12345'[3:5:1];

1 is implicit, so this would be the default already, but things like [3:5:2] would be more interesting

[maiadegraaf]

I'm interested in working on this

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.