[CALCITE-6116] Add EXISTS function (enabled in Spark library)

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.36.0
• Fix Version/s: 1.37.0
• Component/s: core
• Labels: pull-request-available
• Assignee: hongyu guo
• Reporter: hongyu guo
• Created: 13/Nov/23 10:42
• Updated: 06/May/24 22:19
• Resolved: 28/Dec/23 07:02

Description

exists(expr, pred) - Tests whether a predicate holds for one or more elements in the array.

> SELECT `EXISTS`(array(1, 2, 3), x -> x % 2 == 0);
 true
> SELECT `EXISTS`(array(1, 2, 3), x -> x % 2 == 10);
 false
> SELECT `EXISTS`(array(1, null, 3), x -> x % 2 == 0);
 NULL
> SELECT `EXISTS`(array(0, null, 2, 3, null), x -> x IS NULL);
 true
> SELECT `EXISTS`(array(1, 2, 3), x -> x IS NULL);
 false

In Calcite, EXISTS is a keyword, so we need to specify the function with back quotes.
Moreover, `EXISTS` is a higher-order function, and if we want to support higher-order functions in Calcite, we must first support lambda expressions

Issue Links

• is blocked by: CALCITE-3679 Allow lambda expressions in SQL queries
• relates to: CALCITE-6242 The "exists" library function throws a "param not found" error when a column is used in lambda evaluation logic.
• links to: GitHub Pull Request #3587