Corrects tests regarding coercion, indexing, bag operators, and missing attributes

Description

Corrects 80+ incorrect tests.
The following sections break down each test and why they're wrong according to the PartiQL Specification.

FROM RHS Coercion

The excerpt below comes from the PartiQL Specification and states that, in strict mode, the query will FAIL when the RHS of a FROM is a scalar. In permissive, it'll be coerced to a bag holding the scalar.

In the following cases the expression in the FROM clause item has the wrong type. Under the type checking option, all of these cases raise an error and the query fails. Under the permissive option, the cases proceed as follows:

Iteration over a scalar value: Consider the query: FROM s AS v AT p or the query: FROM s AS v

where s is a scalar value. Then s coerces into the bag \<\< s >>, i.e., the bag that has a single element, the s. The rest of the semantics is identical to what happens when the lhs of the FROM item is a bag. -- PartiQL Specification Section 5.1.1

This is applicable to the following 11 tests:

dateTimePartsAsVariableNames
various types in from clause
rangeTwiceOverScalar
single source FROM with scalar
single source FROM with scalar and AT clause
single source FROM with tuple
single source FROM with tuple and AT clause
single source FROM with absent value null
single source FROM with absent value null and AT clause
single source FROM with absent value missing
single source FROM with absent value missing and AT clause

Negative Indexing

From the PartiQL Specification:

In the permissive mode, an array navigation evaluation a[i] will result into missing in each of the following cases: • a does not evaluate into an array, or • i does not evaluate into a positive integer within the array’s bounds. ... In type checking mode, the query will fail in each one of the cases above.

This is applicable to the following 2 tests:

ordinalAccessWithNegativeIndex
ordinalAccessWithNegativeIndexAndBindings

SFW Coercion

The excerpt below comes from the PartiQL Specification and states that SELECT queries shall be coerced.

In each of the following cases a SFW subquery coerces into a scalar... if it is an SFW subquery expression that (a) is not the collection expression of a FROM clause item and (b) is not the rhs of an IN. (If it is the rhs of an IN then it should not be coerced; see note on semantics of IN, Section ??.)

This is applicable to the following 4 tests:

SELECT VALUE with nested aggregates : SELECT VALUE (SELECT SUM(outerFromSource.col1) AS the_sum FROM <<1>>) FROM simple_1_col_1_group as outerFromSource
selectListMultipleAggregatesNestedQuery
Expression with multiple subqueriees containing aggregates : CAST((SELECT COUNT(1) FROM products) AS LIST)[0]._1 / CAST((SELECT COUNT(1) FROM suppliers) AS LIST)[0]._1
Aggregates with subquery containing another aggregate : SELECT COUNT(1) + CAST((SELECT SUM(numInStock) FROM products) AS LIST)[0]._1 as a_number FROM products

Note: I had to rename some of these due to the inclusion of the queries in their names.

EXCEPT DISTINCT Bug

For more information, please see partiql/partiql-lang#75

This is applicable to the following 1 test:

outerExceptDistinct.

Tuple Path Navigation Failures

From the PartiQL Specification:

In the case of tuple paths, since PartiQL does not assume a schema, the semantics must also specify the return value when:

t is not a tuple (i.e., when the expression t does not evaluate into a tuple), or

t is a tuple that does not have an a attribute. ... PartiQL can operate in a permissive mode or in a conventional type checking mode, where the query fails once typing errors (such as the above mentioned ones) happen. In the permissive mode, typing errors are typically neglected by using the semantics outlined next... In all of the above cases PartiQL returns the special value missing.

Notably, it seems that, in type checking mode, grouping does NOT coerce any type checking errors to NULL/MISSING. However, if a MISSING is encountered (not an error), then it will be coerced to NULL.

If a grouping expression evaluates to MISSING, it is first coerced into NULL, thus bringing MISSING and NULL in the same group.

Similarly, aggregate functions are modeled as scalar functions that take in BAGS and output scalars. It seems that a type check exception occurring within the arguments of an aggregate function will result in a query failure (it won't be caught) with type checking mode. In permissive, these failures are coerced to MISSING, and the aggregate functions handle them appropriately.

This is applicable to the following 58 tests:

selectValueStructConstructorWithMissing
SELECT supplierId_missings FROM products_sparse p GROUP BY p.supplierId_missings
SELECT p.supplierId_missings FROM products_sparse p GROUP BY p.supplierId_missings
SELECT VALUE { 'supplierId_missings' : p.supplierId_missings } FROM products_sparse p GROUP BY p.supplierId_missings
SELECT supplierId_mixed FROM products_sparse p GROUP BY p.supplierId_mixed
SELECT p.supplierId_mixed FROM products_sparse p GROUP BY p.supplierId_mixed
SELECT VALUE { 'supplierId_mixed' : p.supplierId_mixed } FROM products_sparse p GROUP BY p.supplierId_mixed
SELECT regionId, supplierId_missings FROM products_sparse p GROUP BY p.regionId, p.supplierId_missings
SELECT p.regionId, p.supplierId_missings FROM products_sparse p GROUP BY p.regionId, p.supplierId_missings
SELECT VALUE { 'regionId': p.regionId, 'supplierId_missings': p.supplierId_missings } FROM products_sparse p GROUP BY p.regionId, p.supplierId_missings
SELECT regionId, supplierId_mixed FROM products_sparse p GROUP BY p.regionId, p.supplierId_mixed
SELECT regionId, p.supplierId_mixed FROM products_sparse p GROUP BY p.regionId, p.supplierId_mixed
SELECT VALUE { 'regionId': p.regionId, 'supplierId_mixed': p.supplierId_mixed } FROM products_sparse p GROUP BY p.regionId, p.supplierId_mixed
SELECT COUNT(1) AS the_count, <ALL_AGGS>(p.price_missings) AS the_agg FROM products_sparse AS p
SELECT COUNT(1) AS the_count, <ALL_AGGS>(p.price_mixed) AS the_agg FROM products_sparse AS p
SELECT categoryId, COUNT(1) AS the_count, <ALL_AGGS>(p.price_missings) AS the_agg FROM products_sparse AS p GROUP BY categoryId
SELECT categoryId, COUNT(1) AS the_count, <ALL_AGGS>(p.price_mixed) AS the_agg FROM products_sparse AS p GROUP BY categoryId
SELECT p.categoryId, COUNT(1) AS the_count, <ALL_AGGS>( price_nulls) AS the_agg FROM products_sparse AS p GROUP BY p.categoryId
SELECT p.categoryId, COUNT(1) AS the_count, <ALL_AGGS>(p.price_mixed) AS the_agg FROM products_sparse AS p GROUP BY p.categoryId
undefinedUnqualifiedVariable_inSelect_withProjectionOption
selectListWithMissing
ANY with GROUP BY
ANY DISTINCT with GROUP BY
SOME with GROUP BY
SOME DISTINCT with GROUP BY
EVERY with GROUP BY
EVERY DISTINCT with GROUP BY
undefinedUnqualifiedVariableInSelectWithUndefinedVariableBehaviorMissing
array element evaluates to MISSING
bag element evaluates to MISSING
bag element evaluates to MISSING in bag constructor
cast and operations with missing argument
group by with absent values
group by with differenciated absent values

UNPIVOT Coercion

I fixed a handful of tests that do not handle the Unpivot mistyping scenarios. According to the PartiQL Specification:

In the following cases the expression in the FROM UNPIVOT clause item has the “wrong" type, i.e., it is not a tuple. Under the type checking option, all of these cases raise an error and the query fails. Under the permissive option, the cases proceed as follows: FROM UNPIVOT x AS v AT n whereas x is not a tuple and is not MISSING, is equivalent to: FROM UNPIVOT {'_1': x} AS v AT n

AT Alias When Unordered

I fixed some tests that previously contradicted the following excerpt from the PartiQL Specification:

In the following cases the expression in the FROM clause item has the wrong type. Under the type checking option, all of these cases raise an error and the query fails. Under the permissive option, the cases proceed as follows • Position variable on bags: Consider the clause: FROM b AS v AT p and assume that b is a bag << e0, . . . , en−1 >>. The output is a bag with binding tuples 〈v : ei, p : MISSING. The value MISSING for the variable p indicates that the order of elements in the bag was meaningless.

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.

partiql / partiql-tests