| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: here is another oracle 10g's bug?
On 6 19 , 11 31 , DA Morgan <damor..._at_psoug.org> wrote:
> 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_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
>
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- -
>
> - -
hi Morgan:
Thank you. The problem is: when you just select data, all things right, but when u insert data into a table, the problem emerged:) just as bug:4604970 without group by version:)
I run the query u given me, totally right.
field yyyymmdd is a number(8), stand for date like this:
20070619
Havel Zhang Received on Tue Jun 19 2007 - 22:28:45 CDT
 |
 |