Re: Bug in Oracle 9i?

Hi Keith,

Keith Sauvant schrieb:
> 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
>

I have the same result as You using Oracle 9.2.0.1.0 under Linux.

-- 
Mit freundlichen Grüßen / Best regards
Michael Gast
SEPP MED GmbH

All emails sent to this address are never read and never will be
answered. Sorry, but until someone cleans up the spam mess, that's the
way it has to be.

E-Mails, die direkt an diese Adresse geschickt werden, lese und
beantworte ich nicht. Ich bedauere diesen Umstand sehr, kenne derzeit
aber keine bessere Möglichkeit, um die Spam-Flut abzustellen.