10g have group by bug?

From: Havel Zhang <havel.zhang_at_gmail.com>
Date: 17 Apr 2007 09:22:53 -0700
Message-ID: <1176826973.414330.97510@p77g2000hsh.googlegroups.com>

hi everyone:

          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. Received on Tue Apr 17 2007 - 11:22:53 CDT