[
https://issues.apache.org/jira/browse/FLINK-18440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17147217#comment-17147217
]
dalongliu edited comment on FLINK-18440 at 6/28/20, 5:34 AM:
-------------------------------------------------------------
[~shenlang]，I also encountered this problem when create view use row_number
function, I have study calcite source code, it 'SqlWindow#validate' method
throw this error, so I guess calcite don't support create view use row_ number
over partition by order by desc grammar, because there's no explicit lower
bound and upper bound. if you use following sql:
{code:java}
create view order_source as select sum(amount) over (partition by order_id,
order_goods_id order by proctime ROWS BETWEEN unbounded PRECEDING AND CURRENT
ROW) from dm_trd_order_goods
{code}
it will not throw exception, I think this is calcite's problem.
CC [~jark], what do you think about it?
was (Author: lsy):
[~shenlang]，I also encountered this problem when create view use row_number
function, I have study calcite source code, it 'SqlWindow#validate' method
throw this error, so I guess calcite don't support create view use row_ number
over partition by order by desc grammar, because there's no explicit lower
bound and upper bound. if you use following sql:
{code:java}
create view order_source as select sum(amount) over (partition by order_id,
order_goods_id order by proctime ROWS BETWEEN unbounded PRECEDING AND CURRENT
ROW) from dm_trd_order_goods
{code}
it will not throw exception, I think this is calcite's problem.
> 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)
