Index operations on NULL values

[philrz]

There's some inconsistency and mismatch with SQL behaviors in the area of index operations on null values.

$ super -version &&
echo 'null' | super -S -c "
values {
  array_index: this[0],
  slice_index: this[0:1],
  record_index: this.x,
  map_index: this['key']
}" -

Version: v0.3.0-129-g1b7ad2acf

{
  array_index: error("missing"),
  slice_index: null,
  record_index: error("missing"),
  map_index: error("missing")
}

Details

Repro is with super commit 1b7ad2a.

Here's the equivalents we can show in Postgres:

$ psql postgres
psql (17.10 (Homebrew), server 17.9 (Homebrew))
Type "help" for help.

postgres=# \pset null 'NULL'
Null display is "NULL".

postgres=# CREATE TYPE _test_struct AS (x int);
CREATE TYPE

postgres=# SELECT
  (NULL::int[])[1] AS array_index,
  (NULL::int[])[1:2] AS slice_index,
  (NULL::_test_struct).x AS null_composite_field;

 array_index | slice_index | null_composite_field 
-------------+-------------+----------------------
        NULL | NULL        |                 NULL
(1 row)

i.e., Postgres doesn't seem to have an equivalent "map" type, but for all others it follows the typical "NULL begets NULL" pattern seen across most of SQL. Meanwhile the SuperSQL repro showed that behavior for slices but error values for the others.

Like was noted for put, there may be a case to made for maintaining the error approach, e.g, if we think a consistent behavior of "attempted indexing of a complex type in the presence of an input value not of that complex type gives missing" would be considered more beneficial to users than being consistently SQL-like in the presence of any NULL input.

Note: Casting

You may notice how I had to add some extra casting on the Postgres side, e.g., this is a syntax error:

$ psql postgres -c "SELECT NULL[1] AS array_index;"
ERROR:  syntax error at or near "["
LINE 1: SELECT NULL[1] AS array_index;
                   ^

Likewise, the syntax we used in Postgres is a syntax error in SuperSQL.

$ super -c "SELECT (NULL::int[])[0] AS array_index;"
parse error at line 1, column 18:
SELECT (NULL::int[])[0] AS array_index;
             === ^ ===

It's orthogonal, but for compatibility reasons we may want to consider supporting the casting syntax that was used in Postgres. I'll discuss maybe opening a separate issue when we discuss this issue as a team.