[jira] [Commented] (FLINK-18440) ROW_NUMBER function: ROW/RANGE not allowed with RANK, DENSE_RANK or ROW_NUMBER functions

[Jark Wu (Jira)]

    [ 
https://issues.apache.org/jira/browse/FLINK-18440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17147222#comment-17147222
 ] 

Jark Wu commented on FLINK-18440:
---------------------------------

I reproduced this problem with adding the following test in 
{{org.apache.flink.table.planner.plan.stream.sql.TableScanTest}}


{code:scala}
 @Test
  def test(): Unit = {
    util.addTable(
      """
        |CREATE TABLE test_source (
        |  name STRING,
        |  eat STRING,
        |  age BIGINT
        |) WITH (
        |  'connector' = 'values'
        |)
      """.stripMargin)
    util.tableEnv.executeSql("create view test as select name, eat ,sum(age) as 
cnt from test_source group by name,eat")
    util.tableEnv.executeSql("create view resultsssss as select *, ROW_NUMBER() 
OVER (PARTITION BY name ORDER BY cnt DESC) as row_num from test")
    util.addTable(
      s"""
         |create table sink (
         |name varchar,
         |eat varchar,
         |cnt bigint
         |)
         |with(
         |'connector' = 'print'
         |)
         |""".stripMargin
    )
    util.verifyPlanInsert("insert into sink select name,eat , cnt from 
resultsssss where row_num <= 3")
  }
{code}

I guess this might be a problem related to Calcite. cc [~danny0405], could you 
help to have a look at this?


> ROW_NUMBER function: ROW/RANGE not allowed with RANK, DENSE_RANK or 
> ROW_NUMBER functions
> ----------------------------------------------------------------------------------------
>
>                 Key: FLINK-18440
>                 URL: https://issues.apache.org/jira/browse/FLINK-18440
>             Project: Flink
>          Issue Type: Bug
>          Components: Table SQL / Planner
>    Affects Versions: 1.11.0
>            Reporter: LakeShen
>            Priority: Major
>             Fix For: 1.11.1
>
>
> When I run flink sql ,the flink sql like this:
> create view test as select  name, eat ,sum(age) as cnt from test_source group 
> by  name,eat;
> create view resultsssss as select *, ROW_NUMBER() OVER (PARTITION BY name 
> ORDER BY cnt DESC) as row_num from test;
> create table sink (
> name varchar,
> eat varchar,
> cnt bigint
> )
> with(
> 'connector' = 'print'
> );
> insert into sink select name,eat , cnt from resultsssss where  row_num <= 3 ;
> The same sql code I could run success in flink 1.10, now I change the flink 
> version into flink 1.11, it throw the exception.
> Exception in thread "main" org.apache.flink.table.api.ValidationException: 
> SQL validation failed. From line 1, column 124 to line 1, column 127: 
> ROW/RANGE not allowed with RANK, DENSE_RANK or ROW_NUMBER functions
>       at 
> org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:146)
>       at 
> org.apache.flink.table.planner.calcite.FlinkPlannerImpl$ToRelContextImpl.expandView(FlinkPlannerImpl.scala:204)
>       at 
> org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
>       at 
> org.apache.flink.table.planner.catalog.SqlCatalogViewTable.convertToRel(SqlCatalogViewTable.java:58)
>       at 
> org.apache.flink.table.planner.plan.schema.ExpandingPreparingTable.expand(ExpandingPreparingTable.java:59)
>       at 
> org.apache.flink.table.planner.plan.schema.ExpandingPreparingTable.toRel(ExpandingPreparingTable.java:55)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3492)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2415)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2102)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2051)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:661)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:642)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3345)
>       at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:568)
>       at 
> org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$rel(FlinkPlannerImpl.scala:164)
>       at 
> org.apache.flink.table.planner.calcite.FlinkPlannerImpl.rel(FlinkPlannerImpl.scala:151)
>       at 
> org.apache.flink.table.planner.operations.SqlToOperationConverter.toQueryOperation(SqlToOperationConverter.java:773)
>       at 
> org.apache.flink.table.planner.operations.SqlToOperationConverter.convertSqlQuery(SqlToOperationConverter.java:745)
>       at 
> org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:238)
>       at 
> org.apache.flink.table.planner.operations.SqlToOperationConverter.convertSqlInsert(SqlToOperationConverter.java:527)



--
This message was sent by Atlassian Jira
(v8.3.4#803005)