search

partiql / partiql-lang

The PartiQL language specification
https://partiql.org/partiql-lang
Other
8 stars 1 forks source link

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

Open alancai98 opened 1 year ago

alancai98 commented 1 year ago

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 commented 1 year ago

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.