[CALCITE-5634] Enable GREATEST, LEAST functions in PostgreSQL library

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.38.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Norman Jordan
• Reporter: Dmitry Sysolyatin
• Created: 06/Apr/23 08:44
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

The LEAST and GREATEST functions have been implemented for BigQuery and Oracle, but haven't been added for PostgreSQL. PostgreSQL supports LEAST, GREATEST as well [1].

Also PostgreSQL's LEAST, GREATEST functions behave differently with NULL values than ORACLE or BigQuery.

From PostgreSQL documentation [1]:
The result will be NULL only if all the expressions evaluate to NULL.

From BigQuery documentation [2]:
They return NULL if any of the input parameters is NULL.

[1] https://www.postgresql.org/docs/15/functions-conditional.html#FUNCTIONS-GREATEST-LEAST
[2] https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions

Issue Links

• relates to: CALCITE-6425 Attributes
• relates to: CALCITE-6454 Implement array comparison operators
• links to: GitHub Pull Request #3142
• links to: GitHub Pull Request #3828