Re: here is another oracle 10g's bug?

Havel Zhang wrote:
> 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

Take each of your queries and modify them to this form:

    SELECT COUNT(*)  <======================================
      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

I am suspicious your data does not support your premise and there seems to be no way we can validate it on one of our servers.

PS: Do you truly have a column named YYYYMMDD? If so do not let Joe Celko see it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org