You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Rui Wang (Jira)" <ji...@apache.org> on 2021/02/02 23:08:00 UTC

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

    [ https://issues.apache.org/jira/browse/CALCITE-4484?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17277518#comment-17277518 ] 

Rui Wang edited comment on CALCITE-4484 at 2/2/21, 11:07 PM:
-------------------------------------------------------------

+1 thanks for creating this JIRA [~julianhyde]


was (Author: amaliujia):
+1 thanks for creating this bug [~julianhyde]

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