[
https://issues.apache.org/jira/browse/CALCITE-7110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18010018#comment-18010018
]
Yu Xu commented on CALCITE-7110:
--------------------------------
There is no sufficient evidence yet, but the tested statements in related pr
can work well. Maybe need to consult the PostgreSQL source code, but the
converted statements in the current version cannot be executed on PostgreSQL.
In addition, postgresql does not support TINYINT and DOUBLE types. Once
converted to array types, they cannot be correctly generated to SMALLINT ARRAY
and DOUBLE PRECISION ARRAY. This is obviously wrong.
> Invalid unparse for cast to nested type in PostgreSQL
> -----------------------------------------------------
>
> Key: CALCITE-7110
> URL: https://issues.apache.org/jira/browse/CALCITE-7110
> Project: Calcite
> Issue Type: Bug
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Labels: pull-request-available
>
> There 2 problem in PostgreSQL adapter.
> 1. TINYINT and DOUBLE can not work correctly in nested type such as
> ARRAY(from https://issues.apache.org/jira/browse/CALCITE-2305):
> {code:java}
> select cast(array[1,2,3] as TINYINT array) {code}
> shoud convert to:
> {code:java}
> SELECT CAST(ARRAY[1,2,3] AS SMALLINT ARRAY) {code}
> result is:
> array
> ---------------
> \{1, 2, 3}
> (1 row)
> but currently convert to：
> {code:java}
> SELECT CAST(ARRAY[1, 2, 3] AS TINYINT ARRAY) {code}
> this would error out：
> {code:java}
> psql:commands.sql:18: ERROR: type "tinyint[]" does not exist
> LINE 1: SELECT CAST(ARRAY[1, 2, 3] AS TINYINT ARRAY); {code}
> 2.The multi-layer nested ARRAY needs to be converted into a single layer
> ARRAY:
> such as:
> {code:java}
> select cast(array[array[1],array[2],array[3]] as int array array) {code}
> should convert to:
> {code:java}
> select cast(array[array[1],array[2],array[3]] as int array) {code}
> result is:
> {code:java}
> array
> ---------------
> {{1},{2},{3}}
> (1 row) {code}
> but currently convert to:
> {code:java}
> select cast(array[array[1],array[2],array[3]] as int array array) {code}
> which would error out:
> {code:java}
> psql:commands.sql:20: ERROR: syntax error at or near "array"
> LINE 1: ...(array[array[1],array[2],array[3]] as int array array); {code}
> additional PostgreSQL only support array and not support map/multiset types
> (https://www.postgresql.org/docs/current/datatype.html), the compatibility
> issues need to be fixed.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
