You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Krishnakant Agrawal <kk...@gmail.com> on 2018/09/03 16:41:08 UTC
Ordering By Projection Alias in RelToSqlConverter
Hi All,
I am trying to convert a Simple RelNode to SQL Text with Hive as Dialect
using RelToSqlConverter.
Problem is if the Order By Key is a Projection which was Aliased, the
Output query contains the original column name instead of the Alias, which
is not allowed in Hive as valid Order By keys are projections only.
For Instance,
Select first_name as n1 from emp order by first_name; (Failing in hive!)
Expected SQL,
Select first_name as n1 from emp order by n1;
I create the TableScan, Projection(with the alias) & Sort in the mentioned
order.
Any leads would be greatly appreciated.
Thanks & Regards,
Krishnakant
Re: Ordering By Projection Alias in RelToSqlConverter
Posted by Krishnakant Agrawal <kk...@gmail.com>.
Okay, will try and come up with a PR then.
On Wed, Sep 12, 2018 at 1:43 AM Julian Hyde <jh...@apache.org> wrote:
> Thanks for logging a case. It’s not high on my priorities to fix this (or
> anyone else’s AFAIK) so contributions are welcome.
>
> > On Sep 11, 2018, at 3:37 AM, Krishnakant Agrawal <kk...@gmail.com>
> wrote:
> >
> > Hi Julian,
> >
> > Sorry for the late Reply.
> >
> > I have created the JIRA
> > <https://issues.apache.org/jira/browse/CALCITE-2552> for
> > this issue.
> >
> > I think Order by Ordinal if decided as the default behavior for this
> > resolution should also ask the Dialect before taking decisions.
> >
> > Hive 1.2 shows following message for query:-
> >
> > *hive> select id from s.emp order by 1;*
> > *Warning: Using constant number 1 in order by. If you try to use position
> > alias when hive.groupby.orderby.position.alias is false, the position
> alias
> > will be ignored.*
> >
> > Regards,
> > Krishnakant
> >
> >
> >
> > On Mon, Sep 3, 2018 at 10:48 PM Julian Hyde <jh...@gmail.com>
> wrote:
> >
> >> It sounds like a bug. Can you please log a JIRA case.
> >>
> >> We should consult the target dialect and generate sql according to the
> >> rules for what is allowed in ORDER BY.
> >>
> >> In calcite, and probably also in hive, “ORDER BY emp.first_name” would
> be
> >> valid. But probably “ORDER BY <ordinal>” is the best solution on
> dialects
> >> that support it (and most do).
> >>
> >> Julian
> >>
> >>> On Sep 3, 2018, at 09:41, Krishnakant Agrawal <kk...@gmail.com>
> >> wrote:
> >>>
> >>> Hi All,
> >>>
> >>> I am trying to convert a Simple RelNode to SQL Text with Hive as
> Dialect
> >>> using RelToSqlConverter.
> >>>
> >>> Problem is if the Order By Key is a Projection which was Aliased, the
> >>> Output query contains the original column name instead of the Alias,
> >> which
> >>> is not allowed in Hive as valid Order By keys are projections only.
> >>>
> >>> For Instance,
> >>> Select first_name as n1 from emp order by first_name; (Failing in
> hive!)
> >>>
> >>> Expected SQL,
> >>> Select first_name as n1 from emp order by n1;
> >>>
> >>> I create the TableScan, Projection(with the alias) & Sort in the
> >> mentioned
> >>> order.
> >>>
> >>> Any leads would be greatly appreciated.
> >>>
> >>> Thanks & Regards,
> >>> Krishnakant
> >>
>
>
Re: Ordering By Projection Alias in RelToSqlConverter
Posted by Julian Hyde <jh...@apache.org>.
Thanks for logging a case. It’s not high on my priorities to fix this (or anyone else’s AFAIK) so contributions are welcome.
> On Sep 11, 2018, at 3:37 AM, Krishnakant Agrawal <kk...@gmail.com> wrote:
>
> Hi Julian,
>
> Sorry for the late Reply.
>
> I have created the JIRA
> <https://issues.apache.org/jira/browse/CALCITE-2552> for
> this issue.
>
> I think Order by Ordinal if decided as the default behavior for this
> resolution should also ask the Dialect before taking decisions.
>
> Hive 1.2 shows following message for query:-
>
> *hive> select id from s.emp order by 1;*
> *Warning: Using constant number 1 in order by. If you try to use position
> alias when hive.groupby.orderby.position.alias is false, the position alias
> will be ignored.*
>
> Regards,
> Krishnakant
>
>
>
> On Mon, Sep 3, 2018 at 10:48 PM Julian Hyde <jh...@gmail.com> wrote:
>
>> It sounds like a bug. Can you please log a JIRA case.
>>
>> We should consult the target dialect and generate sql according to the
>> rules for what is allowed in ORDER BY.
>>
>> In calcite, and probably also in hive, “ORDER BY emp.first_name” would be
>> valid. But probably “ORDER BY <ordinal>” is the best solution on dialects
>> that support it (and most do).
>>
>> Julian
>>
>>> On Sep 3, 2018, at 09:41, Krishnakant Agrawal <kk...@gmail.com>
>> wrote:
>>>
>>> Hi All,
>>>
>>> I am trying to convert a Simple RelNode to SQL Text with Hive as Dialect
>>> using RelToSqlConverter.
>>>
>>> Problem is if the Order By Key is a Projection which was Aliased, the
>>> Output query contains the original column name instead of the Alias,
>> which
>>> is not allowed in Hive as valid Order By keys are projections only.
>>>
>>> For Instance,
>>> Select first_name as n1 from emp order by first_name; (Failing in hive!)
>>>
>>> Expected SQL,
>>> Select first_name as n1 from emp order by n1;
>>>
>>> I create the TableScan, Projection(with the alias) & Sort in the
>> mentioned
>>> order.
>>>
>>> Any leads would be greatly appreciated.
>>>
>>> Thanks & Regards,
>>> Krishnakant
>>
Re: Ordering By Projection Alias in RelToSqlConverter
Posted by Krishnakant Agrawal <kk...@gmail.com>.
Hi Julian,
Sorry for the late Reply.
I have created the JIRA
<https://issues.apache.org/jira/browse/CALCITE-2552> for
this issue.
I think Order by Ordinal if decided as the default behavior for this
resolution should also ask the Dialect before taking decisions.
Hive 1.2 shows following message for query:-
*hive> select id from s.emp order by 1;*
*Warning: Using constant number 1 in order by. If you try to use position
alias when hive.groupby.orderby.position.alias is false, the position alias
will be ignored.*
Regards,
Krishnakant
On Mon, Sep 3, 2018 at 10:48 PM Julian Hyde <jh...@gmail.com> wrote:
> It sounds like a bug. Can you please log a JIRA case.
>
> We should consult the target dialect and generate sql according to the
> rules for what is allowed in ORDER BY.
>
> In calcite, and probably also in hive, “ORDER BY emp.first_name” would be
> valid. But probably “ORDER BY <ordinal>” is the best solution on dialects
> that support it (and most do).
>
> Julian
>
> > On Sep 3, 2018, at 09:41, Krishnakant Agrawal <kk...@gmail.com>
> wrote:
> >
> > Hi All,
> >
> > I am trying to convert a Simple RelNode to SQL Text with Hive as Dialect
> > using RelToSqlConverter.
> >
> > Problem is if the Order By Key is a Projection which was Aliased, the
> > Output query contains the original column name instead of the Alias,
> which
> > is not allowed in Hive as valid Order By keys are projections only.
> >
> > For Instance,
> > Select first_name as n1 from emp order by first_name; (Failing in hive!)
> >
> > Expected SQL,
> > Select first_name as n1 from emp order by n1;
> >
> > I create the TableScan, Projection(with the alias) & Sort in the
> mentioned
> > order.
> >
> > Any leads would be greatly appreciated.
> >
> > Thanks & Regards,
> > Krishnakant
>
Re: Ordering By Projection Alias in RelToSqlConverter
Posted by Julian Hyde <jh...@gmail.com>.
It sounds like a bug. Can you please log a JIRA case.
We should consult the target dialect and generate sql according to the rules for what is allowed in ORDER BY.
In calcite, and probably also in hive, “ORDER BY emp.first_name” would be valid. But probably “ORDER BY <ordinal>” is the best solution on dialects that support it (and most do).
Julian
> On Sep 3, 2018, at 09:41, Krishnakant Agrawal <kk...@gmail.com> wrote:
>
> Hi All,
>
> I am trying to convert a Simple RelNode to SQL Text with Hive as Dialect
> using RelToSqlConverter.
>
> Problem is if the Order By Key is a Projection which was Aliased, the
> Output query contains the original column name instead of the Alias, which
> is not allowed in Hive as valid Order By keys are projections only.
>
> For Instance,
> Select first_name as n1 from emp order by first_name; (Failing in hive!)
>
> Expected SQL,
> Select first_name as n1 from emp order by n1;
>
> I create the TableScan, Projection(with the alias) & Sort in the mentioned
> order.
>
> Any leads would be greatly appreciated.
>
> Thanks & Regards,
> Krishnakant