[FRG-427] Change SQL style on agg push down rule

[dynamobi-build]

[reporter="ngoodman", created="Sat, 25 Jun 2011 11:05:59 -0500 (GMT-05:00)"] Currently the push down rule for the foreign wrapper generates a functional, but less widely accepted SQL as the outcome of it's rule rewrite creates this type of SQL for the foreign data source:

select sum("c1"), "c2" from (select c1, c2 from t1 where "c1" >100) t group by "c2"

Change the rewrite rule to properly change the original select to include the aggs and the group by clause in the original SQL.

select sum("c1"), "c2" from t1 where "c1" > 100 group by "c2"

[dynamobi-build]

[author="ksecretan", created="Tue, 2 Aug 2011 17:03:13 -0500 (GMT-05:00)"] I alluded to an issue I was having in my work-logs, but I'll comment on it here too... I had a fix, but it broke some tests. Specifically, one for count(distinct).

e.g.

data:
0: jdbc:farrago:> select deptno, name from sales.emps;
+---------+--------+
| DEPTNO | NAME |
+---------+--------+
| 20 | Eric |
| 10 | Fred |
| 40 | John |
| 20 | Wilma |
+---------+--------+

select count(deptno) from sales.emps; ==> 4
select count(distinct deptno) from sales.emps; ==> 3

but the second query is actually rewritten to:

select count(deptno) from (select deptno from sales.emps group by deptno);

So I'm not seeing a simple way to flatten that. By the time the AggRule gets called the distinct has already been optimized in this way as well, so it's also not straight forward to "ignore it". What I could do is ignore rewriting if the sub-query has groupings?