Bug in Oracle 9i?

Hi Experts,

when sub-selecting a column of a select-result that contains
- a ROW_NUMBER() OVER() and

• a LEFT JOIN and
• a GROUP BY

I get an ORA-03113 (end-of-file on communication channel).

To reproduce:

CREATE TABLE test1 (test1_id int);
CREATE TABLE test2 (test2_id int, fk_test1_id int);

INSERT INTO test1 VALUES (1);
INSERT INTO test1 VALUES (2);

SELECT test1_id FROM
(

     SELECT ROW_NUMBER() OVER( ORDER BY test1.test1_id asc) row_num,
     test1.test1_id
     FROM test1
     LEFT JOIN test2 ON test2.fk_test1_id=test1.test1_id
     GROUP BY test1.test1_id

The query works, if
- using the inner select only

• including subselect, but without selecting the ROW_NUMBER()
• including subselect, but without the LEFT JOIN (!?)
• including subselect, but without the GROUP BY
• including subselect, but sub-selecting "*" instead of "test1_id"

Can anybody explain that behaviour?

Thank you in advance and best regards from Aachen, Germany Keith Received on Thu Dec 18 2003 - 07:11:43 CST