PartiQL Tutorial - Propagating MISSING in Result Tuples

General Feedback
1

Thanks for creating PartiQL project. I am excited to see that there is now a Rust implementation also in progress.

As I was working through the tutorial, in this section, following query is mentioned.

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

When I tried executing this query, I did not get the expected result. Instead I got,

PartiQL> SELECT e.id,
   | e.name AS employeeName,
   | UPPER(e.title) AS outputTitle
   | FROM hr.employeesWithMissing AS e
   | 
===' 
<<
  {
    'id': 3,
    'employeeName': 'Bob Smith'
  },
  {
    'id': 4,
    'employeeName': 'Susan Smith',
    'outputTitle': 'DEV MGR'
  },
  {
    'id': 6,
    'employeeName': 'Jane Smith',
    'outputTitle': 'SOFTWARE ENG 2'
  }
>>
--- 
OK!

Further, when I attempted to check the semantics of UPPER

PartiQL> UPPER('hello')
   | 
===' 
'HELLO'
--- 
OK!
PartiQL> UPPER(NULL)                                                                         | 
===' 
NULL
--- 
OK!
PartiQL> UPPER(MISSING)
   | 
===' 
MISSING
--- 
OK!
PartiQL>

It looks like UPPER(MISSING) is returning MISSING instead of NULL., which seems to be unexpected based on what is mentioned here.

I was wondering what I could be missing? :slight_smile:

2

It looks like it is possible to use IS MISSING instead.

PartiQL> SELECT e.id,
   |        e.name AS employeeName,
   |        CASE WHEN (e.title IS MISSING) THEN NULL
   |        ELSE e.title END AS outputTitle
   | FROM hr.employeesWithMissing AS e
   | 
===' 
<<
  {
    'id': 3,
    'employeeName': 'Bob Smith',
    'outputTitle': NULL
  },
  {
    'id': 4,
    'employeeName': 'Susan Smith',
    'outputTitle': 'Dev Mgr'
  },
  {
    'id': 6,
    'employeeName': 'Jane Smith',
    'outputTitle': 'Software Eng 2'
  }
>>
--- 
OK!
3

@rajivr glad that you are interested in this project!

Considering the following, this is a possible bug:

➜  ~ 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!

I have cut the following GitHub issue to track this:

Please feel free to communicate further on the issue or here.

1 Like