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

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

What are the index and indexed columns definitions? Can you post the plans (those captured with DBMS_XPLAN so that information about filters is there?) Did you try to recreate the index in 10g and confirm that you get wrong results with the index and correct without?

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Jun 20 2007 - 05:10:38 CDT