While continuing the review of this, I realized that everything except grade_up can be implemented with list lambdas and their new index functionality. Before merging this PR, can you benchmark its performance gain against list lambdas? If the difference is insignificant, I suggest we add rewrites instead of new code that must be maintained.

This is the PR: #8851. Here are the rewrites with our list_extract 1-based-indexing behavior:

D CREATE TABLE tbl (l INTEGER[], sel INTEGER[], where_l BOOLEAN[], zip VARCHAR[]);
D INSERT INTO tbl VALUES ([11, 22, NULL, 33], [1, -1, NULL, 1, 3, 4], [true, false, true, false], ['a', 'b', 'c', 'd']);               ^
D SELECT list_filter(l, (x, x_i) -> where_l[x_i]) FROM tbl; 
┌────────────────────────────────────────────────────┐
│ list_filter(l, (main.row(x, x_i) -> where_l[x_i])) │
│                      int32[]                       │
├────────────────────────────────────────────────────┤
│ [11, NULL]                                         │
└────────────────────────────────────────────────────┘
D SELECT list_transform(sel, (x) -> l[x]) FROM tbl;
┌──────────────────────────────────┐
│ list_transform(sel, (x -> l[x])) │
│             int32[]              │
├──────────────────────────────────┤
│ [11, 33, NULL, 11, NULL, 33]     │
└──────────────────────────────────┘                               
D SELECT list_transform(l, (x, x_i) -> (x, zip[x_i])) FROM tbl;
┌────────────────────────────────────────────────────────────────────────────────────┐
│           list_transform(l, (main.row(x, x_i) -> main.row(x, zip[x_i])))           │
│                          struct(x integer, v2 varchar)[]                           │
├────────────────────────────────────────────────────────────────────────────────────┤
│ [{'x': 11, 'v2': a}, {'x': 22, 'v2': b}, {'x': NULL, 'v2': c}, {'x': 33, 'v2': d}] │
└────────────────────────────────────────────────────────────────────────────────────┘

Either way (rewrites or new code), we should keep the tests. Can you change all statement error to have an explicit error message check? We introduced explicit error messages recently and used them for all new tests. They work like this:

statement error
MY_FAILING_SQL;
----
my error message

EDIT: I confused list_slice and list_extract.

I tried benchmarking the queries on my machine and got these time measurements.
Implementation with Lambdas:

D CREATE TABLE tbl (l INTEGER[], sel INTEGER[], where_l BOOLEAN[], zip VARCHAR[]);
D INSERT INTO tbl VALUES (range(1000), range(1000), [true for x in range(1000)], ['a' for x in range(1000)]);
D SELECT list_filter(l, (x, x_i) -> where_l[x_i]) FROM tbl;
...
Run Time (s): real 0.225 user 0.222456 sys 0.003220
D SELECT list_transform(sel, (x) -> l[x]) FROM tbl;
...
Run Time (s): real 0.227 user 0.431249 sys 0.003793
D SELECT list_transform(l, (x, x_i) -> (x, zip[x_i])) FROM tbl;
...
Run Time (s): real 0.630 user 1.223273 sys 0.008493

For the scaling proposal, if we change 1000 to 10000.

D SELECT list_filter(l, (x, x_i) -> where_l[x_i]) FROM tb2;
...
Run Time (s): real 25.817 user 25.462564 sys 0.350333
D SELECT list_transform(sel, (x) -> l[x]) FROM tb2;
...
Run Time (s): real 25.932 user 25.539946 sys 0.386648
D SELECT list_transform(l, (x, x_i) -> (x, zip[x_i])) FROM tb2;
...
Run Time (s): real 64.306 user 63.449924 sys 0.834758

Implementation in my branch:

D SELECT list_where(l, where_l) FROM tbl;
...
Run Time (s): real 0.027 user 0.026074 sys 0.001398
D SELECT list_select(l, sel) FROM tbl;
...
Run Time (s): real 0.028 user 0.027922 sys 0.000636
D SELECT list_zip(l, zip) FROM tbl;
...
Run Time (s): real 0.039 user 0.045414 sys 0.001267

Change 1000 to 10000:

D SELECT list_where(l, where_l) FROM tbl;
...
Run Time (s): real 0.056 user 0.055299 sys 0.001724
D SELECT list_select(l, sel) FROM tbl;
...
Run Time (s): real 0.055 user 0.055458 sys 0.000807
D SELECT list_zip(l, zip) FROM tbl;
...
Run Time (s): real 0.120 user 0.163200 sys 0.001673

Should the functions still be implemented by means of lambdas, or is the implementation in my branch all right?

Edit: Change table header

@cryoEncryp, thanks for running the benchmarks! It's reassuring to see that your implementation executes much faster. 😄 The list lambdas still need a lot of performance refactoring, so even though they might become more compatible in the following months, your implementations are still significantly faster.

Also, this might interest you in general (or for your thesis). We have a PR up that we'll eventually merge to support fixed-size lists (#8983). Especially for zip, this would allow us to reference the list of child vectors in the resulting struct, making it highly efficient!

We discussed if adding this optimization would benefit this PR (you'd have to check if all lists are the same length for each row first) but decided it was not worth the overhead for the non-equal lists.

We just merged #9288, so you can now add PRAGMA enable_verification to your list_select tests.

@cryoEncryp, can you please merge feature into this PR, as the failing tests are unrelated and fixed on that branch? Then, if CI passes, I believe that we can merge this.

Thanks!