[CALCITE-2935] Add BOOL_OR, BOOL_AND, LOGICAL_OR, LOGICAL_AND aggregate functions

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Haisheng Yuan
• Created: 20/Mar/19 01:54
• Updated: 17/Jun/24 23:41
• Resolved: 16/Oct/20 01:28

Description

Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate functions.

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

Issue Links

• depends upon: CALCITE-3815 Add missing SQL standard aggregate functions: EVERY, SOME, INTERSECTION
• is depended upon by: CALCITE-4335 Aggregate functions for BigQuery
• is duplicated by: CALCITE-3616 Add BOOL_AND Aggregate Function
• is duplicated by: CALCITE-3617 Add BOOL_OR Aggregate Function
• is related to: CALCITE-6441 Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library)
• relates to: CALCITE-2770 Add bitwise aggregate functions BIT_AND, BIT_OR
• relates to: CALCITE-6220 Rewrite MIN/MAX(BOOLEAN) as BOOL_AND/BOOL_OR for Postgres, Redshift
• links to: GitHub Pull Request #1694