HASH GROUP BY can give wrong result in Oracle 10.2
Published by Alex Gorbachev May 4th, 2006 in OracleHit bug 4604970 in our DW environment. This is really bad one and you might not notice that your query returns wrong result until you get negative number on your payslip end of month.
Some one-offs are available and workaround is _GBY_HASH_AGGREGATION_ENABLED=FALSE. Good luck with 10.2!
I bumped into this bug today. It’s really insidious. If I hadn’t been doing some regression testing, comparing my new program’s results to my old program’s results, I never would have noticed that a boring “insert into mytable (this, that) select this, sum(that) from blah group by this” was returning a slightly wrong answer.
Imaging that bug in your monthly billing procedure. That’s where we hit it.
Yep, in our P&L system… imagine having to restate earnings.
FYI this is corrected in patch set 3 (10.2.0.3)
I have a question on this. Recently we upgraded to oracle 10.2.0.1. We noticed that some of our tests were failing. Specifically a quite simple test whereby we entered 8 values, with names and numbers, then selected name and number, grouping by num and then name. However after the upgrade, the results were coming out incorrectly, as follows:
name,number
better things,1
karmacoma,1
light my fire,2
protection,1
eurochild,2
unfinished sympathy,3
heat miser,2
weather storm,1
Now, having read here, I surmised that Patch 10.2.0.3 would correct this, and got it installed onto our machine here. However, the issue still has not gone away. The upgrade appeared to go smoothly, everything points to the fact that our DB is on Oracle 10.2.0.3 now, yet our group by still appears to function incorrectly. Any help greatly appreciated!
I suspect you are expecting them sorted. Right?
In this case, the problem is with your assumptions and not with Oracle. GROUP BY without ORDER BY is not supposed to guarantee ordered results and it never was.
Sorted results, was a side effect until 10g Release 2 because Oracle could only use “SORT GROUP BY” operation. See Metalink Note 361549.1.
You should fix your code. A workaround would be to disable ‘hash group by” but this leaves you with really buggy code.
When ever you give a group by in Oracle, as far as I remember Oracle used to do a aggregated Sort operation.
I am not sure why it was stopped all of a sudden.
A group by and then a sort by on the “same columns” was not a sensible way of doing it.
Dinesh,
10GR2 added a new group by algorithm — hash group by, that’s why you have your observation.
That being said, group by was never documented to return rows in a sorted order in any version of Oracle, try execute it in parallel for example (that’s it — altering the default degree for a table will make your code which relies on a group by doing a sort to break because your code was based on a wrong assumption).