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

On 6 20 , 12 03 , "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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- -
>
> - -

hi Vladimir:

           It's really a good idea to capture the plans on 9i and 10g ! Thank you:) And I found something interesting:)

           On 9i, because of non of index on three tables, so oracle full scans all three tables.

           On 10g, store_product has a index named idx_store_product, so on 10g, Oracle using this index. then the result is wrong. After I delete this index, let oracle full scan all three tables, I get the right figure!! Something wrong with index using of 10g?

           Then, I create index on 9i and hint Oracle using this index and re-run this query on 9i, 9i still produce right result.

Havel Zhang Received on Wed Jun 20 2007 - 01:37:59 CDT