[CALCITE-5634] Add LEAST, GREATEST for PostgreSQL

[dssysolyatin]

[sonarqubecloud[bot]]

Kudos, SonarCloud Quality Gate passed!   

0 Bugs
0 Vulnerabilities
0 Security Hotspots
0 Code Smells

No Coverage information
0.0% Duplication

[tanclary]

I think it could be a good idea to add Postgres to the list of libraries these functions are tested for in SqlOperatorTest.

[zoudan]

+1 for what @tanclary said, we should improve the test for these functions to ensure they works well for PostgreSQL

[dssysolyatin]

It turned out that it is not enough to just enable this function. PostgreSQL's LEAST, GREATEST functions behave differently with NULL values than ORACLE or BigQuery:

From BigQuery documentation [1]:

They return NULL if any of the input parameters is NULL.

From PostgreSQL documentation [2]:

The result will be NULL only if all the expressions evaluate to NULL.

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

[julianhyde]

I was going to suggest changing the commit message to 'Enable GREATEST, LEAST functions in Postgres library' for consistency with other commits.

But it seems that you need two new functions. (Similar to SUBSTR_POSTGRESQL.)