[
https://issues.apache.org/jira/browse/FLINK-6101?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16263755#comment-16263755
]
lincoln.lee commented on FLINK-6101:
------------------------------------
[~fhueske] Agree with you that SQL is not perfect. But in this case I think
it's reasonable that SQL do not support alias in groupBy clause.
Think about such a query similar to which Timo mentioned above:
{code}
.window(Tumble over 1.minute on 'showTime as 'w)
.groupBy('adId + 1 as 'adId, 'w)
.select('adId + 1, 'w.rowtime as 'wtime, 'clickEarn.sum as 'revenue)
{code}
What does `'adId + 1` represent in select clause ? Add alias support in
groupBy is handy but may encounter confusing cases. What do you think?
BTW, we're having another discussion about the limitation of column expression
in select clause after a groupBy (this issue had been partly discussed with
Julian, see https://issues.apache.org/jira/browse/CALCITE-1710), we found that
MySQL and SQL Server have different implementations, extend the select
expressions maybe more meaningful. I'll sync here once we have an preliminary
conclusion.
> GroupBy fields with arithmetic expression (include UDF) can not be selected
> ---------------------------------------------------------------------------
>
> Key: FLINK-6101
> URL: https://issues.apache.org/jira/browse/FLINK-6101
> Project: Flink
> Issue Type: Bug
> Components: Table API & SQL
> Reporter: lincoln.lee
> Assignee: lincoln.lee
> Priority: Minor
>
> currently the TableAPI do not support selecting GroupBy fields with
> expression either using original field name or the expression
> {code}
> val t = CollectionDataSets.get5TupleDataSet(env).toTable(tEnv, 'a, 'b, 'c,
> 'd, 'e)
> .groupBy('e, 'b % 3)
> .select('b, 'c.min, 'e, 'a.avg, 'd.count)
> {code}
> caused
> {code}
> org.apache.flink.table.api.ValidationException: Cannot resolve [b] given
> input [e, ('b % 3), TMP_0, TMP_1, TMP_2].
> {code}
> (BTW, this syntax is invalid in RDBMS which will indicate the selected column
> is invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause in SQL Server.)
> and
> {code}
> val t = CollectionDataSets.get5TupleDataSet(env).toTable(tEnv, 'a, 'b, 'c,
> 'd, 'e)
> .groupBy('e, 'b % 3)
> .select('b%3, 'c.min, 'e, 'a.avg, 'd.count)
> {code}
> will also cause
> {code}
> org.apache.flink.table.api.ValidationException: Cannot resolve [b] given
> input [e, ('b % 3), TMP_0, TMP_1, TMP_2].
> {code}
> and add an alias in groupBy clause "group(e, 'b%3 as 'b)" work without avail.
> and apply an UDF doesn’t work either
> {code}
> table.groupBy('a, Mod('b, 3)).select('a, Mod('b, 3), 'c.count, 'c.count,
> 'd.count, 'e.avg)
> org.apache.flink.table.api.ValidationException: Cannot resolve [b] given
> input [a, org.apache.flink.table.api.scala.batch.table.Mod$('b, 3), TMP_0,
> TMP_1, TMP_2].
> {code}
> the only way to get this work can be
> {code}
> val t = CollectionDataSets.get5TupleDataSet(env).toTable(tEnv, 'a, 'b, 'c,
> 'd, 'e)
> .select('a, 'b%3 as 'b, 'c, 'd, 'e)
> .groupBy('e, 'b)
> .select('b, 'c.min, 'e, 'a.avg, 'd.count)
> {code}
> One way to solve this is to add support alias in groupBy clause ( it seems a
> bit odd against SQL though TableAPI has a different groupBy grammar),
> and I prefer to support select original expressions and UDF in groupBy
> clause(make consistent with SQL).
> as thus:
> {code}
> // use expression
> val t = CollectionDataSets.get5TupleDataSet(env).toTable(tEnv, 'a, 'b, 'c,
> 'd, 'e)
> .groupBy('e, 'b % 3)
> .select('b % 3, 'c.min, 'e, 'a.avg, 'd.count)
> // use UDF
> val t = CollectionDataSets.get5TupleDataSet(env).toTable(tEnv, 'a, 'b, 'c,
> 'd, 'e)
> .groupBy('e, Mod('b,3))
> .select(Mod('b,3), 'c.min, 'e, 'a.avg, 'd.count)
> {code}
> After had a look into the code, found there was a problem in the groupBy
> implementation, validation hadn't considered the expressions in groupBy
> clause. it should be noted that a table has been actually changed after
> groupBy operation ( a new Table) and the groupBy keys replace the original
> field reference in essence.
>
> What do you think?
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
