[jira] [Commented] (CALCITE-2935) Support ANY, SOME, EVERY aggregate functions

["Julian Hyde (JIRA)" <ji...@apache.org>]

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

Julian Hyde commented on CALCITE-2935:
--------------------------------------

SOME, ANY, ALL are already part of SQL, and are implemented in Calcite - but they are operators that you apply to a sub-query, similar to IN and EXISTS. Adding them as aggregate functions might make the language ambiguous for the parser. BOOL_OR and BOOL_AND would not have that problem.

Can someone survey major DBs to see what they call these aggregate functions?

> Support  ANY, SOME, EVERY aggregate functions
> ---------------------------------------------
>
>                 Key: CALCITE-2935
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2935
>             Project: Calcite
>          Issue Type: New Feature
>          Components: core
>            Reporter: Haisheng Yuan
>            Priority: Major
>
> ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. Parser needs to be changed to support these aggregate functions.
> https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/
> https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/
> Note that if ANY or SOME aggregate function is placed on the right side of comparison operation and argument of this function is a subquery additional parentheses around aggregate function are required, otherwise it will be parsed as quantified comparison predicate.
> Example:
> ANY(NAME LIKE 'W%')
> A = (ANY((SELECT B FROM T)))



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)