You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Sean Broeder <se...@dremio.com> on 2022/08/19 21:22:50 UTC
Is column aliasing supported?
Hi,
Calcite is capable of handling a simple column alias query such as
SELECT city as c
FROM (values('PARIS')) as t(city)
However, I add a simple predicate
SELECT city as c
FROM (values('PARIS')) as t(city)
WHERE c is not null
the query fails in validation indicating "Column 'C' not found in any table”
Caused by: org.apache.calcite.runtime.CalciteContextException: At line 1, column 58: Column 'C' not found in any table
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5266)
at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:273)
I’m curious if this is the intended Calcite behavior?
This looks might be related to https://issues.apache.org/jira/browse/CALCITE-4305, but I am not sure.
Thanks,
Sean
Re: Is column aliasing supported?
Posted by Jay Narale <ja...@gmail.com>.
Got it, Thank you!
On Mon, Aug 22, 2022 at 1:45 PM Julian Hyde <jh...@gmail.com> wrote:
> Yes, there are more semantics to be defined. Think of the tricky edge
> cases, try them on Teradata and any other databases with these semantics,
> design error messages, and write tests.
>
> > On Aug 22, 2022, at 1:37 PM, Jay Narale <ja...@gmail.com> wrote:
> >
> > Can we use the semantics defined by Teradata [1]? Are there any more
> > semantics to be defined?
> >
> >
> > [1] -
> >
> https://docs.teradata.com/r/e79ET77~NzPDz~Ykinj44w/MKSYuTyx2UJWXzdHJf3~sQ
> >
> > On Sat, Aug 20, 2022 at 11:53 AM Julian Hyde <jh...@gmail.com>
> wrote:
> >
> >> Agreed. Aliases-in-where (enabled by a compliance flag) is a valid
> feature
> >> request but you’d have to fully define the semantics.
> >>
> >> Julian
> >>
> >>> On Aug 19, 2022, at 22:46, Jiajun Xie <ji...@gmail.com>
> wrote:
> >>>
> >>> Hi, Broeder:
> >>> There's a tricky situation: alias may be the same as column name. Some
> >>> users often make mistakes in company that I worked, so I have to point
> >> out
> >>> their problems.
> >>>
> >>> If you want to support alias in the WHERE clause, I hope you can
> consider
> >>> how to handle this situation.
> >>>
> >>>> On Sat, 20 Aug 2022 at 07:31, Sean Broeder <se...@dremio.com> wrote:
> >>>>
> >>>>
> >>>>
> >>>>>> On Aug 19, 2022, at 3:57 PM, Sean Broeder <se...@dremio.com> wrote:
> >>>>>
> >>>>> Thanks for the clarification
> >>>>>
> >>>>>> On Aug 19, 2022, at 3:23 PM, Julian Hyde <jhyde.apache@gmail.com
> >>>> <ma...@gmail.com>> wrote:
> >>>>>>
> >>>>>> Column aliases are only available in the ORDER BY clause. In some
> >>>> dialect-compliance settings, they are also available in the GROUP BY
> and
> >>>> HAVING clauses. But not in the WHERE clause.
> >>>>>
> >>>> Hi Julian,
> >>>> It looks like some databases do support column aliasing in where
> >> clauses,
> >>>> for example, Terradata documentation indicates it does.
> >>>>
> >>>>
> >>
> https://docs.teradata.com/r/Teradata-Database-SQL-Fundamentals/June-2017/Basic-SQL-Syntax/Referencing-Object-Names-in-a-Request/Using-a-Column-Alias
> >>>>
> >>>> I am interested in this feature and would contribute an enhancement to
> >>>> Calcite if it’s possible.
> >>>>
> >>>> Do you happen to know if the current lack of support is due to
> technical
> >>>> difficulties/limitations or it simply hasn’t been implemented yet?
> >>>>
> >>>> Also, if the latter, do you have a pointer to where I might start
> >> looking?
> >>>>
> >>>> Thanks,
> >>>> Sen
> >>
> >
> >
> > --
> > Warm Regards,
> >
> > Jay Narale
>
>
--
Warm Regards,
Jay Narale
Re: Is column aliasing supported?
Posted by Julian Hyde <jh...@gmail.com>.
Yes, there are more semantics to be defined. Think of the tricky edge cases, try them on Teradata and any other databases with these semantics, design error messages, and write tests.
> On Aug 22, 2022, at 1:37 PM, Jay Narale <ja...@gmail.com> wrote:
>
> Can we use the semantics defined by Teradata [1]? Are there any more
> semantics to be defined?
>
>
> [1] -
> https://docs.teradata.com/r/e79ET77~NzPDz~Ykinj44w/MKSYuTyx2UJWXzdHJf3~sQ
>
> On Sat, Aug 20, 2022 at 11:53 AM Julian Hyde <jh...@gmail.com> wrote:
>
>> Agreed. Aliases-in-where (enabled by a compliance flag) is a valid feature
>> request but you’d have to fully define the semantics.
>>
>> Julian
>>
>>> On Aug 19, 2022, at 22:46, Jiajun Xie <ji...@gmail.com> wrote:
>>>
>>> Hi, Broeder:
>>> There's a tricky situation: alias may be the same as column name. Some
>>> users often make mistakes in company that I worked, so I have to point
>> out
>>> their problems.
>>>
>>> If you want to support alias in the WHERE clause, I hope you can consider
>>> how to handle this situation.
>>>
>>>> On Sat, 20 Aug 2022 at 07:31, Sean Broeder <se...@dremio.com> wrote:
>>>>
>>>>
>>>>
>>>>>> On Aug 19, 2022, at 3:57 PM, Sean Broeder <se...@dremio.com> wrote:
>>>>>
>>>>> Thanks for the clarification
>>>>>
>>>>>> On Aug 19, 2022, at 3:23 PM, Julian Hyde <jhyde.apache@gmail.com
>>>> <ma...@gmail.com>> wrote:
>>>>>>
>>>>>> Column aliases are only available in the ORDER BY clause. In some
>>>> dialect-compliance settings, they are also available in the GROUP BY and
>>>> HAVING clauses. But not in the WHERE clause.
>>>>>
>>>> Hi Julian,
>>>> It looks like some databases do support column aliasing in where
>> clauses,
>>>> for example, Terradata documentation indicates it does.
>>>>
>>>>
>> https://docs.teradata.com/r/Teradata-Database-SQL-Fundamentals/June-2017/Basic-SQL-Syntax/Referencing-Object-Names-in-a-Request/Using-a-Column-Alias
>>>>
>>>> I am interested in this feature and would contribute an enhancement to
>>>> Calcite if it’s possible.
>>>>
>>>> Do you happen to know if the current lack of support is due to technical
>>>> difficulties/limitations or it simply hasn’t been implemented yet?
>>>>
>>>> Also, if the latter, do you have a pointer to where I might start
>> looking?
>>>>
>>>> Thanks,
>>>> Sen
>>
>
>
> --
> Warm Regards,
>
> Jay Narale
Re: Is column aliasing supported?
Posted by Jay Narale <ja...@gmail.com>.
Can we use the semantics defined by Teradata [1]? Are there any more
semantics to be defined?
[1] -
https://docs.teradata.com/r/e79ET77~NzPDz~Ykinj44w/MKSYuTyx2UJWXzdHJf3~sQ
On Sat, Aug 20, 2022 at 11:53 AM Julian Hyde <jh...@gmail.com> wrote:
> Agreed. Aliases-in-where (enabled by a compliance flag) is a valid feature
> request but you’d have to fully define the semantics.
>
> Julian
>
> > On Aug 19, 2022, at 22:46, Jiajun Xie <ji...@gmail.com> wrote:
> >
> > Hi, Broeder:
> > There's a tricky situation: alias may be the same as column name. Some
> > users often make mistakes in company that I worked, so I have to point
> out
> > their problems.
> >
> > If you want to support alias in the WHERE clause, I hope you can consider
> > how to handle this situation.
> >
> >> On Sat, 20 Aug 2022 at 07:31, Sean Broeder <se...@dremio.com> wrote:
> >>
> >>
> >>
> >>>> On Aug 19, 2022, at 3:57 PM, Sean Broeder <se...@dremio.com> wrote:
> >>>
> >>> Thanks for the clarification
> >>>
> >>>> On Aug 19, 2022, at 3:23 PM, Julian Hyde <jhyde.apache@gmail.com
> >> <ma...@gmail.com>> wrote:
> >>>>
> >>>> Column aliases are only available in the ORDER BY clause. In some
> >> dialect-compliance settings, they are also available in the GROUP BY and
> >> HAVING clauses. But not in the WHERE clause.
> >>>
> >> Hi Julian,
> >> It looks like some databases do support column aliasing in where
> clauses,
> >> for example, Terradata documentation indicates it does.
> >>
> >>
> https://docs.teradata.com/r/Teradata-Database-SQL-Fundamentals/June-2017/Basic-SQL-Syntax/Referencing-Object-Names-in-a-Request/Using-a-Column-Alias
> >>
> >> I am interested in this feature and would contribute an enhancement to
> >> Calcite if it’s possible.
> >>
> >> Do you happen to know if the current lack of support is due to technical
> >> difficulties/limitations or it simply hasn’t been implemented yet?
> >>
> >> Also, if the latter, do you have a pointer to where I might start
> looking?
> >>
> >> Thanks,
> >> Sen
>
--
Warm Regards,
Jay Narale
Re: Is column aliasing supported?
Posted by Julian Hyde <jh...@gmail.com>.
Agreed. Aliases-in-where (enabled by a compliance flag) is a valid feature request but you’d have to fully define the semantics.
Julian
> On Aug 19, 2022, at 22:46, Jiajun Xie <ji...@gmail.com> wrote:
>
> Hi, Broeder:
> There's a tricky situation: alias may be the same as column name. Some
> users often make mistakes in company that I worked, so I have to point out
> their problems.
>
> If you want to support alias in the WHERE clause, I hope you can consider
> how to handle this situation.
>
>> On Sat, 20 Aug 2022 at 07:31, Sean Broeder <se...@dremio.com> wrote:
>>
>>
>>
>>>> On Aug 19, 2022, at 3:57 PM, Sean Broeder <se...@dremio.com> wrote:
>>>
>>> Thanks for the clarification
>>>
>>>> On Aug 19, 2022, at 3:23 PM, Julian Hyde <jhyde.apache@gmail.com
>> <ma...@gmail.com>> wrote:
>>>>
>>>> Column aliases are only available in the ORDER BY clause. In some
>> dialect-compliance settings, they are also available in the GROUP BY and
>> HAVING clauses. But not in the WHERE clause.
>>>
>> Hi Julian,
>> It looks like some databases do support column aliasing in where clauses,
>> for example, Terradata documentation indicates it does.
>>
>> https://docs.teradata.com/r/Teradata-Database-SQL-Fundamentals/June-2017/Basic-SQL-Syntax/Referencing-Object-Names-in-a-Request/Using-a-Column-Alias
>>
>> I am interested in this feature and would contribute an enhancement to
>> Calcite if it’s possible.
>>
>> Do you happen to know if the current lack of support is due to technical
>> difficulties/limitations or it simply hasn’t been implemented yet?
>>
>> Also, if the latter, do you have a pointer to where I might start looking?
>>
>> Thanks,
>> Sen
Re: Is column aliasing supported?
Posted by Jiajun Xie <ji...@gmail.com>.
Hi, Broeder:
There's a tricky situation: alias may be the same as column name. Some
users often make mistakes in company that I worked, so I have to point out
their problems.
If you want to support alias in the WHERE clause, I hope you can consider
how to handle this situation.
On Sat, 20 Aug 2022 at 07:31, Sean Broeder <se...@dremio.com> wrote:
>
>
> > On Aug 19, 2022, at 3:57 PM, Sean Broeder <se...@dremio.com> wrote:
> >
> > Thanks for the clarification
> >
> >> On Aug 19, 2022, at 3:23 PM, Julian Hyde <jhyde.apache@gmail.com
> <ma...@gmail.com>> wrote:
> >>
> >> Column aliases are only available in the ORDER BY clause. In some
> dialect-compliance settings, they are also available in the GROUP BY and
> HAVING clauses. But not in the WHERE clause.
> >
> Hi Julian,
> It looks like some databases do support column aliasing in where clauses,
> for example, Terradata documentation indicates it does.
>
> https://docs.teradata.com/r/Teradata-Database-SQL-Fundamentals/June-2017/Basic-SQL-Syntax/Referencing-Object-Names-in-a-Request/Using-a-Column-Alias
>
> I am interested in this feature and would contribute an enhancement to
> Calcite if it’s possible.
>
> Do you happen to know if the current lack of support is due to technical
> difficulties/limitations or it simply hasn’t been implemented yet?
>
> Also, if the latter, do you have a pointer to where I might start looking?
>
> Thanks,
> Sen
Re: Is column aliasing supported?
Posted by Sean Broeder <se...@dremio.com>.
> On Aug 19, 2022, at 3:57 PM, Sean Broeder <se...@dremio.com> wrote:
>
> Thanks for the clarification
>
>> On Aug 19, 2022, at 3:23 PM, Julian Hyde <jhyde.apache@gmail.com <ma...@gmail.com>> wrote:
>>
>> Column aliases are only available in the ORDER BY clause. In some dialect-compliance settings, they are also available in the GROUP BY and HAVING clauses. But not in the WHERE clause.
>
Hi Julian,
It looks like some databases do support column aliasing in where clauses, for example, Terradata documentation indicates it does.
https://docs.teradata.com/r/Teradata-Database-SQL-Fundamentals/June-2017/Basic-SQL-Syntax/Referencing-Object-Names-in-a-Request/Using-a-Column-Alias
I am interested in this feature and would contribute an enhancement to Calcite if it’s possible.
Do you happen to know if the current lack of support is due to technical difficulties/limitations or it simply hasn’t been implemented yet?
Also, if the latter, do you have a pointer to where I might start looking?
Thanks,
Sen
Re: Is column aliasing supported?
Posted by Sean Broeder <se...@dremio.com>.
Thanks for the clarification
> On Aug 19, 2022, at 3:23 PM, Julian Hyde <jh...@gmail.com> wrote:
>
> Column aliases are only available in the ORDER BY clause. In some dialect-compliance settings, they are also available in the GROUP BY and HAVING clauses. But not in the WHERE clause.
Re: Is column aliasing supported?
Posted by Julian Hyde <jh...@gmail.com>.
Column aliases are only available in the ORDER BY clause. In some dialect-compliance settings, they are also available in the GROUP BY and HAVING clauses. But not in the WHERE clause.
> On Aug 19, 2022, at 2:22 PM, Sean Broeder <se...@dremio.com> wrote:
>
>
> Hi,
> Calcite is capable of handling a simple column alias query such as
> SELECT city as c
> FROM (values('PARIS')) as t(city)
> However, I add a simple predicate
>
> SELECT city as c
> FROM (values('PARIS')) as t(city)
> WHERE c is not null
> the query fails in validation indicating "Column 'C' not found in any table”
> Caused by: org.apache.calcite.runtime.CalciteContextException: At line 1, column 58: Column 'C' not found in any table
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5266)
> at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:273)
>
> I’m curious if this is the intended Calcite behavior?
>
> This looks might be related to https://issues.apache.org/jira/browse/CALCITE-4305, but I am not sure.
>
> Thanks,
> Sean
>
>