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/11 15:58:42 UTC
Function Specific Sql Window Unparsing where Framing Not Allowed
Hi,
Unparsing of SqlWindow related to SqlRankFunction & SqlLeadLagFunction type
of SQL Operators should consider whether the Boundary Specifications should
be printed or not(where Framing is not allowed!)
For Instance,
*SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY
hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)*
*FROM foodmart.employee;*
throws the following Exception in Hive 1.2:-
*Expecting right window frame boundary for function ROW_NUMBER() Window
Spec=[PartitioningSpec=[partitionColumns=[(TOK_TABLE_OR_COL
department_id)]orderColumns=[(TOK_TABLE_OR_COL hire_date)
ASC]]window(start=range(Unbounded PRECEDING), end=currentRow)] as
ROW_NUMBER_window_0 to be unbounded. Found : 2147483647*
In my opinion, the best Approach would be not to print any ROW or RANGE
specification as ROW_NUMBER is a fairly generic function and if Dialect
specific changes are required, then we can ask the dialect for syntax.
Re: Function Specific Sql Window Unparsing where Framing Not Allowed
Posted by Julian Hyde <jh...@apache.org>.
You are correct. The SQL standard says
> If <ntile function>, <lead or lag function>, <rank function type> or ROW_NUMBER is specified, then: … The window framing clause of WDX shall not be present.
So, Calcite should not generate it. Please log a JIRA case.
Julian
> On Sep 11, 2018, at 2:03 PM, Krishnakant Agrawal <kk...@gmail.com> wrote:
>
> Hi Julian,
>
> Yes, I am using Calcite to generate the Output SQL.
>
> I am currently creating RelNodes and using RelToSqlConverter to generate
> SqlNode which I then convert to SQL String.
> I am using HiveSqlDialect because of my familiarity with Hive.
>
> The printing of Boundary Specification when unparsing a Analytical Function
> which does not allow Framing(Boundary Specification) is in itself a problem.
> I saw some validation code which restricts specifying Boundary
> Specification on such functions.
>
> My point is that is if such functions have a certain implicit Boundary
> Specification assumption, then the unparisng of SqlNode of such functions
> should not print the Boundaries as well.
>
> Does that seem like valid point?
> Can it be included in Calcite?
>
> Kudos to the things that have already been achieved though. Calcite is
> Awesome!
>
> On Wed, Sep 12, 2018 at 1:39 AM Julian Hyde <jh...@apache.org> wrote:
>
>> That error message seems to come from Hive, not Calcite. How does Calcite
>> fit into this? Are you using Calcite to generate the SQL?
>>
>>> On Sep 11, 2018, at 8:58 AM, Krishnakant Agrawal <kk...@gmail.com>
>> wrote:
>>>
>>> Hi,
>>>
>>> Unparsing of SqlWindow related to SqlRankFunction & SqlLeadLagFunction
>> type
>>> of SQL Operators should consider whether the Boundary Specifications
>> should
>>> be printed or not(where Framing is not allowed!)
>>>
>>> For Instance,
>>>
>>> *SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER
>> BY
>>> hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)*
>>> *FROM foodmart.employee;*
>>>
>>> throws the following Exception in Hive 1.2:-
>>>
>>> *Expecting right window frame boundary for function ROW_NUMBER() Window
>>> Spec=[PartitioningSpec=[partitionColumns=[(TOK_TABLE_OR_COL
>>> department_id)]orderColumns=[(TOK_TABLE_OR_COL hire_date)
>>> ASC]]window(start=range(Unbounded PRECEDING), end=currentRow)] as
>>> ROW_NUMBER_window_0 to be unbounded. Found : 2147483647*
>>>
>>> In my opinion, the best Approach would be not to print any ROW or RANGE
>>> specification as ROW_NUMBER is a fairly generic function and if Dialect
>>> specific changes are required, then we can ask the dialect for syntax.
>>
>>
Re: Function Specific Sql Window Unparsing where Framing Not Allowed
Posted by Krishnakant Agrawal <kk...@gmail.com>.
Hi Julian,
Yes, I am using Calcite to generate the Output SQL.
I am currently creating RelNodes and using RelToSqlConverter to generate
SqlNode which I then convert to SQL String.
I am using HiveSqlDialect because of my familiarity with Hive.
The printing of Boundary Specification when unparsing a Analytical Function
which does not allow Framing(Boundary Specification) is in itself a problem.
I saw some validation code which restricts specifying Boundary
Specification on such functions.
My point is that is if such functions have a certain implicit Boundary
Specification assumption, then the unparisng of SqlNode of such functions
should not print the Boundaries as well.
Does that seem like valid point?
Can it be included in Calcite?
Kudos to the things that have already been achieved though. Calcite is
Awesome!
On Wed, Sep 12, 2018 at 1:39 AM Julian Hyde <jh...@apache.org> wrote:
> That error message seems to come from Hive, not Calcite. How does Calcite
> fit into this? Are you using Calcite to generate the SQL?
>
> > On Sep 11, 2018, at 8:58 AM, Krishnakant Agrawal <kk...@gmail.com>
> wrote:
> >
> > Hi,
> >
> > Unparsing of SqlWindow related to SqlRankFunction & SqlLeadLagFunction
> type
> > of SQL Operators should consider whether the Boundary Specifications
> should
> > be printed or not(where Framing is not allowed!)
> >
> > For Instance,
> >
> > *SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER
> BY
> > hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)*
> > *FROM foodmart.employee;*
> >
> > throws the following Exception in Hive 1.2:-
> >
> > *Expecting right window frame boundary for function ROW_NUMBER() Window
> > Spec=[PartitioningSpec=[partitionColumns=[(TOK_TABLE_OR_COL
> > department_id)]orderColumns=[(TOK_TABLE_OR_COL hire_date)
> > ASC]]window(start=range(Unbounded PRECEDING), end=currentRow)] as
> > ROW_NUMBER_window_0 to be unbounded. Found : 2147483647*
> >
> > In my opinion, the best Approach would be not to print any ROW or RANGE
> > specification as ROW_NUMBER is a fairly generic function and if Dialect
> > specific changes are required, then we can ask the dialect for syntax.
>
>
Re: Function Specific Sql Window Unparsing where Framing Not Allowed
Posted by Julian Hyde <jh...@apache.org>.
That error message seems to come from Hive, not Calcite. How does Calcite fit into this? Are you using Calcite to generate the SQL?
> On Sep 11, 2018, at 8:58 AM, Krishnakant Agrawal <kk...@gmail.com> wrote:
>
> Hi,
>
> Unparsing of SqlWindow related to SqlRankFunction & SqlLeadLagFunction type
> of SQL Operators should consider whether the Boundary Specifications should
> be printed or not(where Framing is not allowed!)
>
> For Instance,
>
> *SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY
> hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)*
> *FROM foodmart.employee;*
>
> throws the following Exception in Hive 1.2:-
>
> *Expecting right window frame boundary for function ROW_NUMBER() Window
> Spec=[PartitioningSpec=[partitionColumns=[(TOK_TABLE_OR_COL
> department_id)]orderColumns=[(TOK_TABLE_OR_COL hire_date)
> ASC]]window(start=range(Unbounded PRECEDING), end=currentRow)] as
> ROW_NUMBER_window_0 to be unbounded. Found : 2147483647*
>
> In my opinion, the best Approach would be not to print any ROW or RANGE
> specification as ROW_NUMBER is a fairly generic function and if Dialect
> specific changes are required, then we can ask the dialect for syntax.