[CALCITE-4484] Add UNIQUE_VALUE(x) aggregate function, that throws if x is not unique

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 02/Feb/21 22:15
• Updated: 11/Feb/21 07:14

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).

Issue Links

• is depended upon by: CALCITE-4483 WITHIN DISTINCT clause for aggregate functions (experimental)