{"issue":{"milestone":null,"title":"[FRG-76] Optimizer should eliminate GROUP BY constant","created_at":"2011-12-30T03:49:17Z","user":{"login":"dynamobi-build","id":1123017,"url":"https://api.github.com/users/dynamobi-build","avatar_url":"https://secure.gravatar.com/avatar/cb215b3d786be20761071a562a56af64?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png","gravatar_id":"cb215b3d786be20761071a562a56af64"},"updated_at":"2011-12-30T03:49:18Z","state":"closed","labels":[{"name":"FRG","url":"https://api.github.com/repos/dynamobi/dynamobi/labels/FRG","color":"1f8f0d"},{"name":"Planner Framework","url":"https://api.github.com/repos/dynamobi/dynamobi/labels/Planner+Framework","color":"4d7cf0"}],"id":2686350,"pull_request":{"patch_url":null,"html_url":null,"diff_url":null},"closed_at":"2011-12-30T03:49:18Z","html_url":"https://github.com/dynamobi/dynamobi/issues/794","body":"<b><i>[reporter=\"jhyde\", created=\"Fri, 17 Mar 2006 15:02:20 -0500 (GMT-05:00)\"]</i></b>\nExample #1:<br/>\n<br/>\n  SELECT deptno, count(*) FROM Emp GROUP BY deptno, 'foo'<br/>\n<br/>\nis equivalent to<br/>\n<br/>\n  SELECT deptno, count(*) FROM Emp GROUP BY deptno<br/>\n<br/>\nExample #2:<br/>\n<br/>\n  SELECT 'bar' FROM Emp GROUP BY 'foo'<br/>\n<br/>\nis equivalent to<br/>\n<br/>\n  SELECT DISTINCT 'bar' FROM Emp<br/>\n<br/>\nbut not<br/>\n<br/>\n  SELECT 'bar' FROM Emp<br/>\n<br/>\nExample #3:<br/>\n<br/>\n  SELECT deptno, count(*) FROM Emp WHERE gender = 'F' GROUP BY deptno, gender<br/>\n<br/>\nis equivalent to<br/>\n<br/>\n  SELECT deptno, count(*) FROM Emp WHERE gender = 'F' GROUP BY deptno<br/>\n<br/>\nTwo rules will achieve this:<br/>\n<br/>\n1. RemoveConstantGroupKeyRule notices 'group by <constant>' and eliminates the <br/>\nconstant (but carries on aggregating).<br/>\n<br/>\n2. InduceConstantFromFilterRule notices notices 'column = value' conditions <br/>\nand converts later uses of that column to the constant. The condition has to <br/>\nbe ANDed of course. And if the condition is used in an outer join, the column <br/>\ncan be the constant or null.<br/>\n------- Comment #1 From Julian Hyde 2004-09-27 15:46 [reply] ------- <br/>\nThe constant-propagation should also recognize IS NULL (and IS TRUE, IS FALSE, <br/>\nIS UNKNOWN, but not IS NOT NULL). And remember that NULL doesn't have 'NULL <br/>\nsemantics' when it is serving as a GROUP or ORDER key.<br/>\n<br/>\nExample #4:<br/>\n<br/>\n  SELECT deptno, count(*) FROM Emp WHERE horoscope IS NULL GROUP BY deptno, <br/>\nhoroscope<br/>\n<br/>\nis equivalent to<br/>\n<br/>\n  SELECT deptno, count(*) FROM Emp WHERE horoscope IS NULL GROUP BY deptno<br/>\n<br/>\nConstants should be also eliminated from ORDER BY.<br/>\n<br/>\nExample #5:<br/>\n<br/>\n  SELECT * FROM Emp WHERE gender = 'F' ORDER BY gender, 'foo', sal<br/>\n<br/>\nis equivalent to<br/>\n<br/>\n  SELECT * FROM Emp WHERE gender = 'F' ORDER BY sal<br/>\n","number":794,"url":"https://api.github.com/repos/dynamobi/dynamobi/issues/794","comments":0,"assignee":null},"action":"closed"}