LEFT JOIN does not padd NULLs correctly

[RCHowell]

Description

The following query produces incorrect results in the evaluating compiler

SELECT t1.a, t2.a FROM <<{ 'a': 1 }>> AS t1 LEFT JOIN <<{ 'a': 2.0 }>> AS t2 ON t1.a = t2.a

-- incorrect
<<
  {
    'a': 1
  }
>>

The correct results should be

<<
  {
    'a': 1,
    'a': NULL,
  }
>>

The SELECT clause is normalized (via spec rule 6.3.1) to SELECT VALUE { 'a': t1.a, 'a': t2.a } and the output binding tuples of the JOIN should be

<<
     < 't1': { 'a': 1 }, 't2': { 'a': NULL } >
>>

In practice, the LEFT JOIN is actually producing < 't1': { 'a': 1 }, 't2': NULL > hence why t2.a -> MISSING and is dropped from the result.

In SQL terms, our LEFT JOIN is producing

(v_1 , ... , v_n ) || NULL when it should be (v_1, ..., v_n) || (NULL, ..., NULL)

To Reproduce

Run

SELECT t1.a, t2.a FROM <<{ 'a': 1 }>> AS t1 LEFT JOIN <<{ 'a': 2.0 }>> AS t2 ON t1.a = t2.a

Expected Behavior

We expected left JOIN to pad the right-hand-side with a tuple of NULL values rather than replacing the tuple with a single NULL.

Additional Context

• Java version: XXX
• PartiQL version: 0.13.2
• Add any other context about the problem here.