here is another oracle 10g's bug?

From: Havel Zhang <havel.zhang_at_gmail.com>
Date: Tue, 19 Jun 2007 02:50:33 -0700
Message-ID: <1182246633.808051.253960@q69g2000hsb.googlegroups.com>

hi everyone:

on 20070418, i'd ask some question about oracle 10g group by bug(bug: 4604970)
thread address as follows:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/909cb7f22341249e/29e81ed7cf79c1ec?lnk=gst&q=havel&rnum=2#29e81ed7cf79c1ec.

today i may hit another oracle 10g bug which similar as bug:4604970 -- oracle 10g will missing records when insert into table. but now, my query have no more group by clauses.

may insert query is:

INSERT INTO debit_ne_grocery_04
SELECT

     A.YMD_TERM_TRXN,
     B.ITEM_KEY,
     B.STORE_CODE,
     A.QTY,
     C.PROD_SEQUENCE ITEM_CODE,
     A.PRICE,
     B.YYYYMMDD,
     A.PURCHASE_PRICE,
     A.MARGIN,
     B.NET_SALES YMDSALES,
     B.NET_COST YMDCOST,
     B.MARGIN YMDMARGIN,
     C.ENGLISH_NAME,
     C.CHINESE_NAME,
     a.sub_code,
     a.unit_code,
     c.active_status,
     b.sales_qty ymdqty
      FROM Day_Debit_Ne_Margin_grocery A
     INNER JOIN DAILY_SALES_0504_NE B ON A.ITEM_KEY = B.ITEM_KEY
                                     AND A.STORE_CODE = B.STORE_CODE
                                     AND A.YYYYMMDD = B.YYYYMMDD
                                     AND a.sub_code = b.sub_code
                                     AND a.unit_code = b.unit_code
     INNER JOIN store_PRODUCT C ON A.ITEM_KEY = C.ITEM_KEY
                               AND a.store_code = c.store_code

--------------------------------------------------------------------------------------------------------------------------

in above query:
table Day_Debit_Ne_Margin_grocery has 72276 records; table DAILY_SALES_0504_NE has 185850568 records; table store_product has 13564357 records;

when inserted records, we found debit_ne_grocery_04 table has 28827 records.
but the correct number is 72276, some records is missing!!

finally, I copy these table to 9i database, and re-run the query above, I get the correct number 72276.

also, i changed method for these action. first, we create a table do the first inner join. then, insert the final table do the second inner join, as follows:

CREATE TABLE DEBIT_TST
TABLESPACE FEES_CALC
NOLOGGING
AS

SELECT

     A.YMD_TERM_TRXN,
     B.ITEM_KEY,
     B.STORE_CODE,
     A.QTY,


    -- C.PROD_SEQUENCE ITEM_CODE,

     A.PRICE,
     B.YYYYMMDD,
     A.PURCHASE_PRICE,
     A.MARGIN,
     B.NET_SALES YMDSALES,
     B.NET_COST YMDCOST,
     B.MARGIN YMDMARGIN,

C.ENGLISH_NAME,
C.CHINESE_NAME, a.sub_code, a.unit_code,
c.active_status, b.sales_qty ymdqty FROM Day_Debit_Ne_Margin_grocery A INNER JOIN DAILY_SALES_0504_NE B ON A.ITEM_KEY = B.ITEM_KEY AND A.STORE_CODE = B.STORE_CODE AND A.YYYYMMDD = B.YYYYMMDD AND a.sub_code = b.sub_code AND a.unit_code = b.unit_code

insert into debit_ne_grocery_04
SELECT

     A.YMD_TERM_TRXN,
     a.ITEM_KEY,
     a.STORE_CODE,
     A.QTY,
     C.PROD_SEQUENCE ITEM_CODE,
     A.PRICE,
     a.YYYYMMDD,
     A.PURCHASE_PRICE,
     A.MARGIN,
     a.YMDSALES,
     a.YMDCOST,
     a.YMDMARGIN,
     c.ENGLISH_NAME,
     c.CHINESE_NAME,
     a.sub_code,
     a.unit_code,
     c.active_status,
     a.ymdqty
     from
     DEBIT_TST a inner join store_PRODUCT C ON A.ITEM_KEY = C.ITEM_KEY
                               AND a.store_code = c.store_code

-------------------------------------------------------------------------------------------------------

it also generate the accurate result:72276.

in my procedures, I have many insert action with three or more inner joins, are these procedures in dangers?
it's really a 10g's bug? Anyone can give me the answer?

Havel Received on Tue Jun 19 2007 - 04:50:33 CDT