[jira] [Comment Edited] (CALCITE-4354) ITEM operator does not support synthetic struct type

["Danny Chen (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4354?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17221917#comment-17221917 ] 

Danny Chen edited comment on CALCITE-4354 at 10/28/20, 2:42 AM:
----------------------------------------------------------------

I tried this SQL in PostgreSQL 9.6


{code:sql}
create table t1(
  f0 integer[],
  f1 varchar(20)
);

insert into t1 values('{1, 2, 3}', 'abc');

select f0[1] from t1; -- outputs 1
{code}

I also found this access rules in SQL standard 2011 6.24 <array element reference>

{code:java}

1) If the value of <array value expression> or <numeric value expression> is the null value, then the result of <array element reference> is the null value.
2) Let i be the value of <numeric value expression>. Case:
a) If i is greater than zero and less than or equal to the cardinality of <array value expression>, then the result of <array element reference> is the value of the i-th element of the value of <array value expression>.
b) Otherwise, an exception condition is raised: data exception — array element error.
{code}


So the array type is index 1-based. We can follow this for row type.



was (Author: danny0405):
I tried this SQL in PostgreSQL 9.6


{code:sql}
create table t1(
  f0 integer[],
  f1 varchar(20)
);

insert into t1 values('{1, 2, 3}', 'abc');

select f0[1] from t1; -- outputs 1
{code}

I also found this access rules in SQL standard 2011 6.24 <array element reference>

{code:noformat}

1) If the value of <array value expression> or <numeric value expression> is the null value, then the result of <array element reference> is the null value.
2) Let i be the value of <numeric value expression>. Case:
a) If i is greater than zero and less than or equal to the cardinality of <array value expression>, then the result of <array element reference> is the value of the i-th element of the value of <array value expression>.
b) Otherwise, an exception condition is raised: data exception — array element error.
{code}


So the array type is index 1-based. We can follow this for row type.


> ITEM operator does not support synthetic struct type
> ----------------------------------------------------
>
>                 Key: CALCITE-4354
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4354
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 1.27.0
>            Reporter: Alessandro Solimando
>            Assignee: Alessandro Solimando
>            Priority: Minor
>              Labels: pull-request-available
>          Time Spent: 2h 20m
>  Remaining Estimate: 0h
>
> The current implementation of the "ITEM" operator only supports struct/row type when this type if based on a class with named fields (that is, a non-synthetic type).  
> For those non-synthetic struct types, the "ITEM" operator can be used to access named fields via a "string"-typed argument ([SqlItemOperator#96|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java#L96]).
> The type checker accepts to apply "ITEM" on "ANY" type ([SqlItemOperator#49|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java#L49]), and the operand checker only accepts "string"-based values ([SqlItemOperator.java#105|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java#L105]).
> [SqlValidatorTest.java#L11933|https://github.com/apache/calcite/blob/master/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java#L11933] is an example of application of the "ITEM" operator over a struct with named fields. 
> However, the “getAllowedSignatures” method ([SqlItemOperator.java#116|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/fun/SqlItemOperator.java#L116]) does not reflect this, and asserts a signature as follows: "<ARRAY>[<INTEGER>]" or "<MAP>[<VALUE>]".
> "ITEM" operator could be enriched with the support of synthetic struct/row type, with a positional access to the fields with a signature "<ROW>[<INTEGER>]".



--
This message was sent by Atlassian Jira
(v8.3.4#803005)