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