[
https://issues.apache.org/jira/browse/CALCITE-4484?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17277641#comment-17277641
]
Julian Hyde commented on CALCITE-4484:
--------------------------------------
An appeal on twitter yielded some similar functions:
* Joel Wikström [pointed
out|https://twitter.com/WikstromJoel/status/1356743135393689609] that QlikView
has an
[ONLY|https://help.qlik.com/en-US/sense/November2020/Subsystems/Hub/Content/Sense_Hub/Scripting/AggregationFunctions/only.htm]
function
* Torsten Grust [referred
me|https://twitter.com/Teggy/status/1356721978560942080] to the THE\(x)
function in [Wadler & Peyton Jones' paper proposing extensions to Haskell
comprehensions|https://www.microsoft.com/en-us/research/wp-content/uploads/2007/09/list-comp.pdf]
* Eirik Bakke [pointed
out|https://twitter.com/eirikbakke/status/1356728561403650060] the SINGLE\(x)
aggregate function in his Ultorg language
There are some slight differences: ONLY and SINGLE seem to return null, rather
than throwing; SINGLE ignores null values.
> Add UNIQUE_VALUE(x) aggregate function, that throws if x is not unique
> ----------------------------------------------------------------------
>
> Key: CALCITE-4484
> URL: https://issues.apache.org/jira/browse/CALCITE-4484
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> 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)}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
