10g have group by bug?
Havel Zhang
I met a strange thing when i do a group by summarize. The
base table have nearly 4 million records. When I do group by without
add order by clause, some records will be lost!! When I add order by
clause, then I get the accurate answer.
the query as follows:
--------------------------
INSERT INTO tmp_NSAPP_YTD_ITEM_PURCHASE
(
YYYY,from_mm, MM,store_code,dept_code,supplier_code, item_key,
net_purchase, job_number,
vat_purchase,gross_purchase
)
SELECT
2007, 1, 3, store_code, dept_code, trim(supplier_code), item_key,
NVL(SUM(DECODE(trxn_type,'11',amount)),0) -
NVL(SUM(DECODE(trxn_type,'22',amount)),0) as net_purchase,11
in_job_number,
nvl(sum(decode(trxn_type,'11',vat_amount)),0)-
nvl(sum(decode(trxn_type,'22',vat_amount)),0) as vat_amount,
nvl(sum(decode(trxn_type,'11',gross_amount)),0)-
nvl(sum(decodetrxn_type,'22',gross_amount)),0) as gross_amount
FROM
nsapload.monthly_item_purchase_calc
WHERE
yyyy = 2007 and mm = 3
GROUP BY
store_code, dept_code, trim(supplier_code), item_key
-------------------------------------------------------------
after inserted, some records lost, so the "net_purchase" is wrong,
because net_purchase is result of group by summarized.
for example:
store:hz,supplier_code:F111,mm=3,yyyy=2007,item_key = 43113, in base
table monthly_item_purchase_calc, it have 3 records:
rec1:
store:hz,supplier_code:F111,mm=3,yyyy=2007,item_key = 43113,trxn_type:
11,amount:4321.77
rec2:
store:hz,supplier_code:F111,mm=3,yyyy=2007,item_key = 43113,trxn_type:
11,amount:23321.77
rec3:
store:hz,supplier_code:F111,mm=3,yyyy=2007,item_key = 43113,trxn_type:
22,amount:3321.77
the formula is: trxn_type 11-trxn_type 22,so net purchase is :
4321.77+23321.77-3321.77=24321.77
so, the correct result shall be:
store:hz,supplier_code:F111,mm=3,yyyy=2007,item_key =
43113,net_purchase:24321.77
but, when u using group by not following order by, the result is:
store:hz,supplier_code:F111,mm=3,yyyy=2007,item_key =
43113,net_purchase:-3321.77
the record 1 and record 2 missed!!
I found when u add order by clause followed group by, we will get the
right result. And when we using hint: /*+no_merge*/, we also get right
result. But if we not using order by followed by group by, it will go
wrong.
Why!! any one have answer?
thank you in advanced.
fitzj...@cox.net
There is more 'wrong' here than you originally thought:
NVL(SUM(DECODE(trxn_type,'11',amount)),0) -
NVL(SUM(DECODE(trxn_type,'22',amount)),0) as net_purchase,
nvl(sum(decode(trxn_type,'11',vat_amount)),0)-
nvl(sum(decode(trxn_type,'22',vat_amount)),0) as vat_amount,
nvl(sum(decode(trxn_type,'11',gross_amount)),0)-
nvl(sum(decode(trxn_type,'22',gross_amount)),0) as gross_amount
I understand what you're trying to do, but consider this:
if amount is NULL even once in any of those sum() calculations the
entire result will be NULL and thus defaults to 0, not what you
wanted, I think. These should have been written:
SUM(DECODE(trxn_type,'11',nvl(amount, 0))) -
SUM(DECODE(trxn_type,'22',nvl(amount,0))) as net_purchase,
sum(decode(trxn_type,'11',nvl(vat_amount,0)))-
sum(decode(trxn_type,'22',nvl(vat_amount,0))) as vat_amount,
sum(decode(trxn_type,'11',nvl(gross_amount,0)))-
sum(decode(trxn_type,'22',nvl(gross_amount,0))) as gross_amount
As to your issue, WHICH release of 10g are you using, and what patches
have you applied? On which O/S? This is information you failed to
provide and is essential to 'solving' your problem. Until you provide
this information any answer regarding your difficulty is speculation.
David Fitzjarrell
K Gopalakrishnan
(If you are on 10g )I guess you are hitting bug:4604970. Try setting
_gby_hash_aggregation_enabled to false to disable the hash aggregation
or apply the patch..
-Gopal
Havel Zhang
hi all:
Thank you all mention my post.
Thank you Gopal, as u said, it does a bug:4604970. and when i
issued ' alter system set "_gby_hash_aggregation_enabled" = false ;',
then I noticed execution explain from :(hash) group by
to (sort) group by , and this problem solved. I've ask my DBA to
apply patch 10.2.0.3.
Thank u David, it's a real problem on decode(...) expression on
my aggregation query, thank for your advice about the way of my asking
question, when i review my oracle / OS version, i notice my Oracle
version is 64bit 10g 10.2.0.2 on 64bit windows 2003 server, and
consult Gopal's advice, finally i found out where the problem is.
Thank u all again!
Havel Zhang