You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Haisheng Yuan (JIRA)" <ji...@apache.org> on 2019/03/20 23:18:00 UTC
[jira] [Commented] (CALCITE-2935) Support ANY, SOME, EVERY (also
known as BOOL_OR, BOOL_AND) aggregate functions
[ https://issues.apache.org/jira/browse/CALCITE-2935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16797645#comment-16797645 ]
Haisheng Yuan commented on CALCITE-2935:
----------------------------------------
Yes, makes sense.
Unfortunately, there is no consistency between different vendors.
MySQL supports BIT_AND, BIT_OR.
Postgres has EVERY, BOOL_AND, BOOL_OR.
RedShift has BOOL_AND, BOOL_OR.
BigQuery has LOGICAL_AND, LOGICAL_OR.
H2 has ANY, SOME, EVERY.
Oracle, DB2, Sql Server don't have these aggregate functions.
:(
> Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) 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)