Specify whether MAX/MIN (and COLL_MAX/COLL_MIN) with non-comparable types should data type mismatch

[alancai98]

The PartiQL spec doesn’t mention whether MAX/MIN aggregation with different, non-comparable types will result in an error. The SQL-92 spec says “If MAX or MIN is specified, then the result is respectively the maximum or minimum value in TXA. These results are determined using the comparison rules specified in Subclause 8.2, ““.” — which seems to rely on the comparison operators which would error in strict mode and return missing in permissive mode.

Thereby the following queries should result in returning MISSING in permissive mode and error in strict mode:

SELECT MIN(a) AS result FROM <<{'a': 1}, {'a': 'non-number'}>>
SELECT MAX(a) AS result FROM <<{'a': 1}, {'a': 'non-number'}>>
COLL_MIN(<<1, 'non-number'>>)
COLL_MAX(<<1, 'non_number'>>)

This would contradict the current behavior defined in partiql-lang-kotlin.

[alancai98]

It may be helpful to have a different aggregate or builtin function that takes in data that contains non-comparable types
rather than having a CASE WHEN ... statement or some other workaround.