UPPER(MISSING) return NULL in legacy mode and MISSING in permissive mode #1241
Open
Description
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:
toolbox install partiql- Ensure the version is
0.13.2by runningpartiql --version - 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