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

On Jun 20, 7:28 am, Havel Zhang <havel.zh...@xxxxxxxxx> wrote:
On 6 19 , 11 31 , DA Morgan <damor...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx (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

.