SQL: NULL propagation with LIKE, SUBSTRING, and LENGTH

[philrz]

In super, operations on NULL values with LIKE, SUBSTRING, and LENGTH currently produce non-NULL values, which is not typical SQL behavior.

$ super -version && super -S -c "
SELECT
  NULL LIKE '%a%' AS like_result,
  SUBSTRING(NULL FROM 1 FOR 3) AS sustring_result,
  LENGTH(NULL) AS length_result;"

Version: v0.3.0-122-g9ba80d768

{
  like_result: false,
  sustring_result: error({
    message: "SUBSTRING: string value required",
    on: null
  }),
  length_result: 0
}

Details

Repro is with super commit 9ba80d7.

By comparison, Postgres propagates NULL in all these.

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

postgres=# \pset null 'NULL'
Null display is "NULL".
postgres=# SELECT
  NULL LIKE '%a%' AS like_result,
  SUBSTRING(NULL FROM 1 FOR 3) AS sustring_result,
  LENGTH(NULL) AS length_result;
 like_result | sustring_result | length_result 
-------------+-----------------+---------------
 NULL        | NULL            |          NULL
(1 row)

The LENGTH (aka len) function in SuperSQL deserves an extra note because the return of 0 on null input is currently disclosed in its user-facing docs. However, as noted in #6193, that detail was included to disclose behaviors that were previously not documented, so the effort was focused on black box testing, not SQL compatibility. In light of our recent consensus on conforming to SQL behaviors in regards to NULL handling, I've included it with the others here since there's a clear apples-to-apples SQL comparison. That said, the current behavior dates back well before our SQL compatibility efforts, so if we choose to keep this one as it is we should just make sure to disclose it as a known divergence in our SQL compatibility docs.