[
https://issues.apache.org/jira/browse/CALCITE-5634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17862559#comment-17862559
]
Julian Hyde commented on CALCITE-5634:
--------------------------------------
This is about comparison operations rather than just the {{GREATEST}} and
{{LEAST}} functions. Can someone please investigate? Check whether Postgres
allows comparisons like {{<}} on array values, and whether you can use array
values in {{ORDER BY}}. If Postgres allows some of these operations, file a new
Jira case.
I am also curious whether Postgres allows comparisons on {{ROW}} values. I
would hope that {{ROW (1, 'b') < ROW (2, 'a')}} is valid and returns {{TRUE}}
(due to lexicographic ordering). If so, Calcite should make {{ROW}} values
comparable also. In my work on attributes (CALCITE-6425) it would enable to
defining a composite {{SORT_KEY}} attribute.
> Enable GREATEST, LEAST functions in PostgreSQL library
> ------------------------------------------------------
>
> Key: CALCITE-5634
> URL: https://issues.apache.org/jira/browse/CALCITE-5634
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0
> Reporter: Dmitry Sysolyatin
> Assignee: Norman Jordan
> Priority: Major
> Labels: pull-request-available
> Time Spent: 1h
> Remaining Estimate: 0h
>
> 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]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
