dynamobi/dynamobi — Issue #794: [FRG-76] Optimizer should eliminate GROUP BY constant
Author: dynamobi-build
Created: 2011-12-30 03:49:17+00:00
State: open
Labels: FRG, Planner Framework

[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:

1. RemoveConstantGroupKeyRule notices 'group by ' and eliminates the

constant (but carries on aggregating).

2. 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