[DERBY-681] Eliminate the parser's rewriting of the abstract syntax tree for queries with GROUP BY and/or HAVING clauses

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.3.1.4
• Component/s: SQL
• Assignee: Manish Khettry
• Reporter: Richard N. Hillegas
• Created: 04/Nov/05 05:52
• Updated: 21/Jan/11 17:48
• Resolved: 28/May/07 06:55

Description

If a query contains a GROUP BY or HAVING clause, the parser rewrites the abstract syntax tree, putting aggregates into a subselect and treating the HAVING clause as the WHERE clause of a fabricated outer select from the subquery. This allows the compiler to re-use some machinery since the HAVING clause operates on the grouped result the way that the WHERE clause operates on the from list. Unfortunately, this rewriting creates an explosion of special cases in the compiler after parsing is done. The rewriting is not systematically handled later on in the compiler. This gives rise to defects like bug 280. We need to eliminate this special rewriting and handle the HAVING clause in a straightforward way. This is not a small bugfix but is a medium sized project.

Issue Links

• incorporates: DERBY-280 Wrong result from select when aliasing to same name as used in group by
• is related to: DERBY-4063 Constraint causes wrong query result when using exists
• is related to: DERBY-4071 AssertFailure when selecting rows from a table with CHARACTER and VARCHAR columns
• is related to: DERBY-1861 Column ordering ASSERT when combining column references and expressions in same ORDER BY
• is related to: DERBY-1624 use of direct column name rather than alias make aggregation fail (Hibernate depends on that)
• relates to: DERBY-3303 ArrayIndexOutOfBoundsException at MergeSort.compare
• relates to: DERBY-3231 Sorting on COUNT with OR and GROUP BY delivers wrong results.
• relates to: DERBY-3257 SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB
• relates to: DERBY-3764 Union Query fail on Derby 10.4.1.3
• relates to: DERBY-3872 NullPoinerException thrown when INTEGER function used as a predicate in a WHERE clause of a SELECT .. GROUP BY .. HAVING statement
• relates to: DERBY-4230 DatabaseMetaData.getColumns() returns extra column from view with group by and expression in SELECT list
• relates to: DERBY-4450 GROUP BY in an IN-subquery inside HAVING clause whose select list is subset of group by columns, gives NPE
• relates to: DERBY-3880 NPE on a query with having clause involving a join
• relates to: DERBY-1624 use of direct column name rather than alias make aggregation fail (Hibernate depends on that)