[
https://issues.apache.org/jira/browse/CALCITE-6454?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17862582#comment-17862582
]
Norman Jordan commented on CALCITE-6454:
----------------------------------------
The comparison is applied element by element. If the arrays are of different
length, the longer array is considered larger. In my testing, NULL is greater
than everything. If NULL is the first element in one of the arrays, then the
comparison fails with this error:
HINT: No operator matches the given name and argument types. You might need to
add explicit type casts.
Adding a cast does fix this.
I don't see how to use NULLS FIRST or NULLS LAST, since those are part of the
syntax for ORDER BY. It is an error to add them in the projection, such as:
select array[1] > array[cast(null as int)] NULLS FIRST;
> Implement array comparison operators
> ------------------------------------
>
> Key: CALCITE-6454
> URL: https://issues.apache.org/jira/browse/CALCITE-6454
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Norman Jordan
> Priority: Major
>
> The comparison operators <, <=, >, >=, =, <> are not implemented for arrays.
> Here is an example query:
>
> {code:java}
> SELECT array[2, 2] > array[1, 1]; {code}
> [This
> page|https://popsql.com/learn-sql/postgresql/how-to-compare-arrays-in-postgresql]
> describes how the comparisons work in PostgreSQL.
>
> Check if the comparison operators for arrays exist in other DB engines.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
