You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Dmitry Sysolyatin <dm...@gmail.com> on 2022/04/29 15:51:12 UTC
Column 'x' not found in any table
Hi!
Maybe someone knows what can be the cause of the following issue:
I tried to execute two queries:
val query1 = "SELECT x FROM unnest(array[1]) x"
val query2 = "SELECT x FROM unnest(array(select 1)) x"
The first one works, but the second one throws an exception:
Column 'x' not found in any table
Re: Column 'x' not found in any table
Posted by Julian Hyde <jh...@gmail.com>.
No, I didn’t forget. Array query and array values are different cases, because values are anonymous whereas query columns are named.
If you think we should name the columns from array queries you should specify the feature, especially the cases where we would not rename columns. Read the SQL standard and experiment with databases such as Postgres and BigQuery.
Julian
> On Apr 30, 2022, at 08:29, Dmitry Sysolyatin <dm...@gmail.com> wrote:
>
> Maybe for ARRAY_QUERY_CONSTRUCTOR it doesn't make sense to do it, but for
> queries with unnest(column_of_array_type) it makes sense. Unfortunately,
> this doesn't work at the moment either.
>
> For example:
> ""SELECT ARRAY_CONCAT(c.reloptions, array(select 'toast.' || x from
> unnest(c.reloptions) x)) FROM pg_catalog.pg_class c"". At the moment it
> works only with scalar array and multisets
>
>
>> On Sat, Apr 30, 2022 at 5:35 PM Dmitry Sysolyatin <dm...@gmail.com>
>> wrote:
>>
>> Yes, but I think these limited circumstances should work in both cases.
>>
>> I think the reason why it does not work is that when
>> https://issues.apache.org/jira/browse/CALCITE-4305 was being implemented,
>> just forgot to check that unnest argument can be also
>> ARRAY_QUERY_CONSTRUCTOR, not only ARRAY_VALUE_CONSTRUCTOR:
>> https://github.com/apache/calcite/commit/c2527ccf440f7750bfbabd2063c402440c5b32a0
>>
>>
>>> On Fri, Apr 29, 2022 at 9:56 PM Julian Hyde <jh...@apache.org> wrote:
>>>
>>> 'x' is a table alias, not a column alias.
>>>
>>> There are some very limited circumstances in which a table alias
>>> applied to a table with an anonymous column causes the anonymous
>>> column to be renamed. Evidently those circumstances apply to case 1
>>> but not case 2.
>>>
>>> On Fri, Apr 29, 2022 at 8:51 AM Dmitry Sysolyatin
>>> <dm...@gmail.com> wrote:
>>>>
>>>> Hi!
>>>> Maybe someone knows what can be the cause of the following issue:
>>>>
>>>> I tried to execute two queries:
>>>> val query1 = "SELECT x FROM unnest(array[1]) x"
>>>> val query2 = "SELECT x FROM unnest(array(select 1)) x"
>>>>
>>>> The first one works, but the second one throws an exception:
>>>> Column 'x' not found in any table
>>>
>>
Re: Column 'x' not found in any table
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
Maybe for ARRAY_QUERY_CONSTRUCTOR it doesn't make sense to do it, but for
queries with unnest(column_of_array_type) it makes sense. Unfortunately,
this doesn't work at the moment either.
For example:
""SELECT ARRAY_CONCAT(c.reloptions, array(select 'toast.' || x from
unnest(c.reloptions) x)) FROM pg_catalog.pg_class c"". At the moment it
works only with scalar array and multisets
On Sat, Apr 30, 2022 at 5:35 PM Dmitry Sysolyatin <dm...@gmail.com>
wrote:
> Yes, but I think these limited circumstances should work in both cases.
>
> I think the reason why it does not work is that when
> https://issues.apache.org/jira/browse/CALCITE-4305 was being implemented,
> just forgot to check that unnest argument can be also
> ARRAY_QUERY_CONSTRUCTOR, not only ARRAY_VALUE_CONSTRUCTOR:
> https://github.com/apache/calcite/commit/c2527ccf440f7750bfbabd2063c402440c5b32a0
>
>
> On Fri, Apr 29, 2022 at 9:56 PM Julian Hyde <jh...@apache.org> wrote:
>
>> 'x' is a table alias, not a column alias.
>>
>> There are some very limited circumstances in which a table alias
>> applied to a table with an anonymous column causes the anonymous
>> column to be renamed. Evidently those circumstances apply to case 1
>> but not case 2.
>>
>> On Fri, Apr 29, 2022 at 8:51 AM Dmitry Sysolyatin
>> <dm...@gmail.com> wrote:
>> >
>> > Hi!
>> > Maybe someone knows what can be the cause of the following issue:
>> >
>> > I tried to execute two queries:
>> > val query1 = "SELECT x FROM unnest(array[1]) x"
>> > val query2 = "SELECT x FROM unnest(array(select 1)) x"
>> >
>> > The first one works, but the second one throws an exception:
>> > Column 'x' not found in any table
>>
>
Re: Column 'x' not found in any table
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
Yes, but I think these limited circumstances should work in both cases.
I think the reason why it does not work is that when
https://issues.apache.org/jira/browse/CALCITE-4305 was being implemented,
just forgot to check that unnest argument can be also
ARRAY_QUERY_CONSTRUCTOR, not only ARRAY_VALUE_CONSTRUCTOR:
https://github.com/apache/calcite/commit/c2527ccf440f7750bfbabd2063c402440c5b32a0
On Fri, Apr 29, 2022 at 9:56 PM Julian Hyde <jh...@apache.org> wrote:
> 'x' is a table alias, not a column alias.
>
> There are some very limited circumstances in which a table alias
> applied to a table with an anonymous column causes the anonymous
> column to be renamed. Evidently those circumstances apply to case 1
> but not case 2.
>
> On Fri, Apr 29, 2022 at 8:51 AM Dmitry Sysolyatin
> <dm...@gmail.com> wrote:
> >
> > Hi!
> > Maybe someone knows what can be the cause of the following issue:
> >
> > I tried to execute two queries:
> > val query1 = "SELECT x FROM unnest(array[1]) x"
> > val query2 = "SELECT x FROM unnest(array(select 1)) x"
> >
> > The first one works, but the second one throws an exception:
> > Column 'x' not found in any table
>
Re: Column 'x' not found in any table
Posted by Julian Hyde <jh...@apache.org>.
'x' is a table alias, not a column alias.
There are some very limited circumstances in which a table alias
applied to a table with an anonymous column causes the anonymous
column to be renamed. Evidently those circumstances apply to case 1
but not case 2.
On Fri, Apr 29, 2022 at 8:51 AM Dmitry Sysolyatin
<dm...@gmail.com> wrote:
>
> Hi!
> Maybe someone knows what can be the cause of the following issue:
>
> I tried to execute two queries:
> val query1 = "SELECT x FROM unnest(array[1]) x"
> val query2 = "SELECT x FROM unnest(array(select 1)) x"
>
> The first one works, but the second one throws an exception:
> Column 'x' not found in any table