Further clarification GROUP BY grouping key for MISSING values exclusively

[alancai98]

PartiQL spec defines the behavior when a grouping key results in NULL and MISSING. See spec section 11.1.1:

Notice that PartiQL will group together the NULL and the MISSING grouping expressions, since any grouping
expression resulting to MISSING has been coerced into NULL before eqg does comparisons for grouping.

That would seem to imply that in the output binding tuple created by GROUP BY, NULL and MISSING would be grouped together as illustrated in example 41.

However in the case in which a group key is only MISSING, the spec could be more clear on this behavior.

E.g.

SELECT a FROM
[
    {'a': 1},
    {'a': 2},
    {'a': MISSING}
] AS tbl
GROUP BY tbl.a AS a

Should the output binding tuple from GROUP BY be

-- case 1
<<
    < a: 1 >,
    < a: 2 >,
    < a: NULL>
>>

or

-- case 2
<<
    < a: 1 >,
    < a: 2 >,
    < a: MISSING>
>>

Because of the coercion that's mentioned in spec section 11.1.1, it would make more sense for case 1 to be the defined behavior (i.e. group key in binding would be NULL). Also considering the order in which the tuples are processed affecting the group key value (e.g. MISSING then NULL), case 2 would add additional implementation complexities.

After discussing with @almann, we believe that case 1 should be the spec behavior. In any case, additional spec clarification would be helpful.

[alancai98]

For reference, the Kotlin implementation preserves the attribute as MISSING (case 2) rather than doing the coercion to NULL (case 1):

-- CLI run with v0.9.3 including the `GROUP AS` grouping variable
PartiQL> SELECT a, g FROM
   | [
   |    {'a': 1},
   |    {'a': 2},
   |    {'a': MISSING}
   | ] AS tbl
   | GROUP BY tbl.a AS a GROUP AS g;
==='
<<
  {
    'g': <<
      {
        'tbl': {}
      }
    >>
},
  {
    'a': 1,
    'g': <<
      {
        'tbl': {
          'a': 1
        }
      }
    >>
  },
  {
    'a': 2,
    'g': <<
      {
        'tbl': {
          'a': 2
        }
      }
    >>
  }
>>