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

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

.