Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4484

Add UNIQUE_VALUE(x) aggregate function, that throws if x is not unique

    XMLWordPrintableJSON

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

          Activity

            People

              Unassigned Unassigned
              julianhyde Julian Hyde
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated: