[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17493757#comment-17493757
]
Ruben Q L commented on CALCITE-4999:
bq. So I think the solution is to return an array of scalars (e.g. INTEGER
ARRAY) when the query returns 1 column, and continue to return an array of ROW
when the query has 2 or more columns. And do the same for MULTISET.
+1
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17493478#comment-17493478
]
Julian Hyde commented on CALCITE-4999:
--
Thanks for checking, [~rubenql]. That's as I suspected. So I think the solution
is to return an array of scalars (e.g. {{INTEGER ARRAY}}) when the query
returns 1 column, and continue to return an array of {{ROW}} when the query has
2 or more columns. And do the same for {{MULTISET}}.
[~dmsysolyatin] Can you change the subject (and, if necessary, description) of
this Jira now we know the underlying cause?
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17493206#comment-17493206
]
Ruben Q L commented on CALCITE-4999:
[~dmsysolyatin] you can find some draft versions of the standard in Wikipedia:
https://en.wikipedia.org/wiki/SQL#SQL_standards_documents
I have checked the standard (relevant parts: and
); and it would seem that, indeed, Calcite is
not following it:
{noformat}
6.38
Function
Specify construction of an array.
Format
::=
|
[...]
::=
ARRAY
Syntax Rules
[...]
3) If is specified, then
a) The QE simply contained in the shall be
of degree 1 (one). Let ET be the declared type of the column in the result of
.
b) The declared type of the is array with
element type ET and maximum cardinality equal to the implementation-defined
maximum cardinality IMDC for such array types.
{noformat}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17492925#comment-17492925
]
Julian Hyde commented on CALCITE-4999:
--
[~rubenql], I had that concern too. But if the SQL standard says that ARRAY
sub-query should have 1 column and the result type is an ARRAY of that column's
type, then we should comply with that standard. We can go beyond the standard,
and support queries with 2 or more columns, and return an ARRAY of ROW objects,
or whatever, but when we are within the parameters of the standard, we should
comply with the standard.
Hence we need to check what the standard says.
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17492473#comment-17492473
]
Ruben Q L commented on CALCITE-4999:
If I an not mistaken, the current Calcite implementation seems more powerful
(or more flexible) that e.g. the BigQuery one, because the ARRAY subquery
allows multiple columns.
For instance a single column ARRAY:
{code:sql}
SELECT r.r_name, ARRAY(
SELECT n.n_name FROM nation n WHERE n.n_regionkey = r.r_regionkey
)
FROM region r ORDER BY r.r_name
{code}
Returns a record type with one field for the ARRAY subquery (which seemed fine
so far, but it could potentially be a scalar: the point of this discussion).
But it also supports the case of a multi-column ARRAY:
{code:sql}
SELECT r.r_name, ARRAY(
SELECT n.n_nationkey, n.n_name FROM nation n WHERE n.n_regionkey =
r.r_regionkey
)
FROM region r ORDER BY r.r_name
{code}
It returns a record type with two fields for the ARRAY subquery (which is
perfectly fine).
If we changed the first case to return a scalar, wouldn't it be a misalignment
compared to the second? (not sure, just raising the question)
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17490793#comment-17490793
]
Dmitry Sysolyatin commented on CALCITE-4999:
I tried to read some standards from this page
[https://www.postgresql.org/docs/14/features.html] . But it turned out that it
costs money :) [https://www.iso.org/standard/67382.html]
But BigQuery
[https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries]
documentation has also the following lines:
{code:java}
The SELECT list in an ARRAY subquery must have exactly one column of any type,
which defines the element type of the array returned by the array subquery{code}
I think the best solution will be split this task to tasks:
# Fix current `ARRAY` implementation in order it return `scalar array` type in
case of subquery (At least BigQuery and PG `array` implementation work like
this)
# Extend `ARRAY` implementation for {{SELECT AS STRUCT}} and SELECT AS VALUE
(If someone really needs it)
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17490490#comment-17490490
]
Julian Hyde commented on CALCITE-4999:
--
I am much more inclined to believe that the problem is with the type derivation
of {{ARRAY (sub-query)}}.
If we change that, we should also change the type derivation for {{MULTISET
(sub-query)}}.
Do you know what the SQL standard says should be the type of {{ARRAY}} applied
to a sub-query of with 1 column?
What about other databases? Google BigQuery might be interesting, because it
has {{SELECT AS STRUCT}} and {{SELECT AS VALUE}} to control how single- and
multi-column sub-queries return results.
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17487510#comment-17487510
]
Dmitry Sysolyatin commented on CALCITE-4999:
[~julianhyde] Can you explain why it should not? I don't see any problem in
casting scalar type to row type with one field.
if really there is a problem with that then initial problem can be fixed if
`ARRAY` function will be modified in the way that return type of
`ARRAY(subquery)` will be [array of scalar] instead of [array of row]. Like
Postgres does -
https://www.postgresql.org/docs/14/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17487297#comment-17487297
]
Julian Hyde commented on CALCITE-4999:
--
{quote}`least restrictive` type inference does not work when one operand is
scalar and another is row with one field{quote}
Nor should it.
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
