Re: 10g have group by bug?

On Apr 17, 11:22 am, Havel Zhang <havel.zh..._at_gmail.com> wrote:
> 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.

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 Received on Tue Apr 17 2007 - 12:48:01 CDT