the query result of intersect can be aggregated before intersect

[windtalker]

Enhancement

consider the case that

select id, value from a intersect select id, value from b

From mysql's doc(https://dev.mysql.com/doc/refman/9.0/en/intersect.html), if intersect means intersect distinct by default, so the above sql is the same as

select id, value from a group by id, value intersect select id, value from b group by id, value

if there is a lots of duplicate entries in table a and table b, this aggregation can help to improve the query performance a lot, and also save the memory usage a lot.

Even if user's query is

select id, value from a intersect all select id, value from b

The query can still be rewriten as

select id, value from a intersect all select id, value from b group by id, value

[winoros]

This is just part of the semi-join to inner-join rewrite.
It is trivial, but it's cost based. (The rewrite is bad when the source is something like 1m rows with 0.9 million NDV)
So it's not done yet.

[winoros]

And another thing we need to notify is that the intersect is generating the join whose join key is nulleq not eq.
It also needs to be specially handled.