UPPER(MISSING) return NULL in legacy mode and MISSING in permissive mode

[am357]

This may not be a bug

Description

UPPER (or LOWER, and perhaps similar behavior in other functions) returns NULL in legacy mode but MISSING in permissive mode. Shouldn't the function return error in legacy mode? Shouldn't we promote the MISSING to NULL in permissive mode for SQL compatibility?

From https://partiql.org/tutorial.html#_evaluating_functions_and_conditions_with_missing:

The same treatment of MISSING would happen if, say, we had this query that converts titles to capital letters:

SELECT e.id,
       e.name AS employeeName,
       UPPER(e.title) AS outputTitle
FROM hr.employeesWithMissing AS e

Again, the e.title will evaluate to MISSING for 'Bob Smith', the UPPER(e.title) is then UPPER(MISSING) and also evaluates to NULL. Thus the result will be:

<<
  {
    'id': 3,
    'employeeName': 'Bob Smith',
    'outputTitle': NULL
  },
  {
    'id': 4,
    'employeeName': 'Susan Smith',
    'outputTitle': 'DEV MGR'
  },
  {
    'id': 6,
    'employeeName': 'Jane Smith',
    'outputTitle': 'SOFTWARE ENG 2'
  }
>>

More details: https://community.partiql.org/t/partiql-tutorial-propagating-missing-in-result-tuples/114

To Reproduce

Steps to reproduce the behavior:

1. toolbox install partiql
2. Ensure the version is 0.13.2 by running partiql --version
3. Run the following:

➜  ~ partiql --typing-mode PERMISSIVE
Welcome to the PartiQL shell!
Typing mode: PERMISSIVE
Using version: 0.13.2-c6bf4aec
PartiQL> UPPER(MISSING);
==='
MISSING
---
OK!

➜  ~ partiql --typing-mode LEGACY
Welcome to the PartiQL shell!
Typing mode: LEGACY
Using version: 0.13.2-c6bf4aec
PartiQL> UPPER(MISSING);
==='
NULL
---
OK!

Expected Behavior

• We still need to investigate but perhaps the evaluator should error out in legacy mode.

Additional Context

• PartiQL version: 0.13.2