planner: eliminate aggregation with distinct

[SeaRise]

Description

for table t (a int, b int, key int)

(10, 20, 10)
(10, 10, 10)
(20, 10, 13)

select count(distinct a), sum(b) from t group by key

==>

select count(a), sum(sum_b)
from (
	select a, sum(b) as sum_b, gid from (
		Expand(
			 projections = 
					[(a, null, key, 0),
                                         (null, b, key, 1)
					], 
			 t)
	)
	group by a, gid, key
)
group by key

Expand is extand t to t_extend(a int, b int, key int, gid int)

(10, null, 10, 0)
(null, 20, 10, 1)

(10, null, 10, 0)
(null, 10, 10, 1)

(20, null, 13, 0)
(null, 10, 13, 1)

spark sql use a operator Extend to extend t to t_extend.
we can use sql as follows to extend

	mem_table(gid int)
        (0)
	(1)
	
select if((gid = 0) a else null), if ((gid = 1) b else null), gid from t join mem_table

or implement a more efficient operator like Extend to extend t to t_extend.

Score

1200

SIG slack channel（must）:

Contact us in channel #sig-planner of TiDB Community

Mentor(must)

@winoros

Recommended Skills：

• Relational algebra

[iontang]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@william0423 You did not submit PR within 7 days, so give up automatically.

[iontang]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@william0423 You did not submit PR within 7 days, so give up automatically.

[pingyu]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@pingyu You did not submit PR within 7 days, so give up automatically.

[pingyu]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@pingyu You did not submit PR within 7 days, so give up automatically.

[hidehalo]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@hidehalo You did not submit PR within 7 days, so give up automatically.

[Enochack]

Sorry I didn't get the point. Why expand the table? I found the following sql

select key, count(tmp.a), sum(tmp.sum_b)
from (
  select key, a, sum(b) as sum_b from t
  group by key, a
) as tmp
group by key

has an equivalent semantic and is more likely to have better performance.
However, if we add an expand operator like Spark SQL does, we could use it to implement with rollup and with cube clauses.

[Tangruilin]

I'm interested in this problem, was it solved by others? /cc @winoros

[Tangruilin]

/assign