Happened to run across this little oddity today when working on some stuff with expressions in a GROUP BY. The following queries appear to be semantically equivalent but #1 works and #2 fails using Oracle:
- SELECT value * 2 * 2 FROM table GROUP BY value * 2
- SELECT value * 4 FROM table GROUP BY value * 2
The trick here is that the grouping is occurring on an expression in the GROUP BY and apparently the Oracle planner is smart enough to pick up that the same expression is used as is in the SELECT clause but not smart enough to decompose the 4 such that this query can be executed. Can’t say I blame them as this is tricky (and expensive) to do generically as it is basically some form of algebraic theorem solver.
I tried a few other databases and found a range of behavior:
| Database | Result for #1 | Result #2 |
|---|---|---|
| Oracle 8 | Worked | Error: “ORA-00979: not a GROUP BY expression” |
| Oracle 9 | Worked | Error: “ORA-00979: not a GROUP BY expression” |
| SQL Server 7.0 | Error: “Column ‘value’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.” | Error: “Column ‘value’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.” |
| DB2 7.2 | Worked | Error: “COLUMN IN HAVING CLAUSE NOT INCLUDED IN GROUP BY CLAUSE” |
| Sybase 11.5 | Worked | Worked! |
Amazingly, #2 worked on Sybase! Guess they’re doing something cool. I think DB2’s error message is pretty confusing for #2 given that I don’t have a HAVING clause.