Re: DISTINCT not being handled correctly in RelToSqlConverter
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)
GROUP BY sum(x) OVER (PARTITION BY y)
is not. For example, given the query
select sum(deptno) over (partition by loc)
group by sum(deptno) over (partition by loc);
ORA-00934: group function is not allowed here
Therefore we should generate a sub-query, something like this:
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> wrote:
> 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 as
> 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) over
> (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.