Re: DISTINCT not being handled correctly in RelToSqlConverter
You voiced my thoughts exactly.
I tried on several DB's too. Nowhere is an Agg function allowed in Group By
Probably, we need to Check for Agg function in Group By keys and set
needNew as true, if needed.
That code is not so straight-forward as RelNodes can get fairly complex and
the actual Expression for the Group Key can be buried several RelNodes down
Logged a JIRA:- https://issues.apache.org/jira/browse/CALCITE-2757
On Fri, Dec 28, 2018 at 1:28 AM Julian Hyde <jhyde@xxxxxxxxxx> wrote:
> Sounds like a bug. Please log it.
> Re-stating what you just said. My understanding is that
> SELECT DISTINCT sum(x) OVER (PARTITION BY y) FROM t
> is valid (per SQL standard) but
> SELECT sum(x) OVER (PARTITION BY y)
> FROM t
> GROUP BY sum(x) OVER (PARTITION BY y)
> is not. For example, given the query
> select sum(deptno) over (partition by loc)
> from dept
> group by sum(deptno) over (partition by loc);
> Oracle gives
> ORA-00934: group function is not allowed here
> Therefore we should generate a sub-query, something like this:
> SELECT c1
> FROM (
> SELECT sum(deptno) OVER (PARTITION BY loc)
> FROM dept) AS t
> GROUP BY c1;
> RelToSqlConverter has a mechanism to figure out whether a sub-select is
> necessary. See needNew. The fix is probably in that code.
> > On Dec 26, 2018, at 2:44 AM, Krishnakant Agrawal <kk.agrawal19@xxxxxxxxx>
> > Hi All,
> > When creating a RelNode for a Query with a DISTINCT keyword in it, I use
> > the relBuilder.distinct() api to apply distinct.
> > It creates a LogicalAggregate with all the Fields of the LogicalProject
> > the GroupKey.
> > This is a problem when one of those projections is a windowing function
> > (i.e SUM(col1) over (partition by col2) ).
> > The group by key now contains an aggregate function which is wrong.
> > The output is something like( SqlNode.toSqlString() ):-
> > SELECT sum(col1) OVER (partition by col2) from t1 group by sum(col1)
> > (partition by col2).
> > I have a fix ready for this.
> > Basically, sub-querying the projection containing the aggregate function
> > and apply the Group By(due to DISTINCT) outside the sub-query.
> > Please let me know if this is an actual bug or my assumptions are wrong.
> > Thanks,
> > KrishnaKant