Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
-
None
Description
Add a UNIQUE_VALUE(x) aggregate function, that throws if x is not unique.
UNIQUE_VALUE(x) would throw if x has values [1, 2], or has values [1, NULL]; but would not throw if x has values [1, 1, 1] or [] or [NULL, NULL]. Like ANY_VALUE it behaves as if RESPECT NULLS is specified.
There are similar functions:
- ANY_VALUE(x) non-deterministically picks a value. (It is present in BigQuery, MySQL, Snowflake, MSSQL and perhaps others.)
- SINGLE_VALUE(x) returns the value of x if there is just one value (e.g. [1] or [NULL]), NULL if there are no values, throws if there is more than one value (e.g. [NULL, NULL] or [1, 1, 1] or [1, 2]). SINGLE_VALUE is in Calcite, no other DBs that I am aware of, not documented, but available through SQL. Calcite uses it internally to enforce scalar sub-queries.
BigQuery has an internal function "$ANY_AND_CHECK(x)" that is equivalent to UNIQUE_VALUE(x).
Attachments
Issue Links
- is depended upon by
-
CALCITE-4483 WITHIN DISTINCT clause for aggregate functions (experimental)
- Closed