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

On Jun 20, 7:28 am, Havel Zhang <havel.zh..._at_gmail.com> wrote:
> 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

Capture the plans on 9i and 10g and see if there is any difference that may explain why 10g inserts wrong amount of rows. Try the insert on 10.2.0.3 - this may be a known defect fixed in that patchset.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Jun 19 2007 - 23:03:38 CDT