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 <[email protected]> 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 <[email protected]> > 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 >
