[FRG-76] Optimizer should eliminate GROUP BY constant

[reporter="jhyde", created="Fri, 17 Mar 2006 15:02:20 -0500 (GMT-05:00)"] Example #1:

  SELECT deptno, count() FROM Emp GROUP BY deptno, 'foo'

is equivalent to

  SELECT deptno, count() FROM Emp GROUP BY deptno

Example #2:

  SELECT 'bar' FROM Emp GROUP BY 'foo'

is equivalent to

  SELECT DISTINCT 'bar' FROM Emp

but not

  SELECT 'bar' FROM Emp

Example #3:

  SELECT deptno, count() FROM Emp WHERE gender = 'F' GROUP BY deptno, gender

is equivalent to

  SELECT deptno, count() FROM Emp WHERE gender = 'F' GROUP BY deptno

Two rules will achieve this:

RemoveConstantGroupKeyRule notices 'group by ' and eliminates the
constant (but carries on aggregating).
InduceConstantFromFilterRule notices notices 'column = value' conditions
and converts later uses of that column to the constant. The condition has to
be ANDed of course. And if the condition is used in an outer join, the column
can be the constant or null.
------- Comment #1 From Julian Hyde 2004-09-27 15:46 [reply] -------
The constant-propagation should also recognize IS NULL (and IS TRUE, IS FALSE,
IS UNKNOWN, but not IS NOT NULL). And remember that NULL doesn't have 'NULL
semantics' when it is serving as a GROUP or ORDER key.

Example #4:

  SELECT deptno, count() FROM Emp WHERE horoscope IS NULL GROUP BY deptno,
horoscope

is equivalent to

  SELECT deptno, count() FROM Emp WHERE horoscope IS NULL GROUP BY deptno

Constants should be also eliminated from ORDER BY.

Example #5:

  SELECT * FROM Emp WHERE gender = 'F' ORDER BY gender, 'foo', sal

is equivalent to

  SELECT * FROM Emp WHERE gender = 'F' ORDER BY sal

LucidDB / luciddb

[FRG-76] Optimizer should eliminate GROUP BY constant #794