[
https://issues.apache.org/jira/browse/CALCITE-7110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18010012#comment-18010012
]
Yu Xu commented on CALCITE-7110:
--------------------------------
The document does not contain any information about this syntax conversion, but
the actual test fails. This problem can be found in this online test. Users
cannot convert the array type normally.
https://onecompiler.com/postgresql/43rq6nkgm
> 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)
