Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: here is another oracle 10g's bug?

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

From: Havel Zhang <havel.zhang_at_gmail.com>
Date: Tue, 19 Jun 2007 02:52:25 -0700
Message-ID: <1182246745.921166.180000@k79g2000hse.googlegroups.com>


On 6 19 , 5 50 , Havel Zhang <havel.zh..._at_gmail.com> 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_th....
>
> 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

PS. my Oracle version is 64bit 10g 10.2.0.2 on 64bit windows 2003 server Received on Tue Jun 19 2007 - 04:52:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US