Re: Strange behavior with union/order by

On Wed, 18 Jul 2007 09:05:54 -0700, "Chris L." <diversos_at_uol.com.ar> wrote:

>I've noticed something strange, the server disconnects the session
>(ORA-03113: end-of-file on communication channel) whenever I issue a
>query mixing union and order by dbms_random.value:
>
>CREATE TABLE dummytable AS
>SELECT 'MONDAY' AS THEDAY,'JANUARY' AS THEMONTH
>FROM dual UNION select
>'TUESDAY','FEBRUARY' FROM dual UNION SELECT
>'WEDNESDAY','MARCH' FROM dual UNION SELECT
>'THURSDAY','APRIL' FROM dual UNION SELECT
>'FRIDAY','MAY' FROM dual UNION SELECT
>'SATURDAY','JUNE' FROM dual UNION SELECT
>'SUNDAY','JULY' FROM dual;
>
>SELECT * FROM dummytable
>WHERE THEDAY LIKE 'T%'
>AND ROWNUM <=2
>ORDER BY Dbms_Random.Value;
>/* works fine */
>
>SELECT * FROM dummytable
>WHERE THEMONTH LIKE '%Y'
>AND ROWNUM <=2
>ORDER BY Dbms_Random.Value;
>/* works fine */
>
>SELECT * FROM dummytable
>WHERE THEDAY LIKE 'T%'
>AND ROWNUM <=2
>UNION
>SELECT * FROM dummytable
>WHERE THEMONTH LIKE '%Y'
>AND ROWNUM <=2;
>/* works fine */
>
>SELECT * FROM dummytable
>WHERE THEDAY LIKE 'T%'
>AND ROWNUM <=2
>UNION
>SELECT * FROM dummytable
>WHERE THEMONTH LIKE '%Y'
>AND ROWNUM <=2
>ORDER BY Dbms_Random.Value;
>
>/* after about 3 seconds:
>
>ORA-03113: end-of-file on communication channel
>
>*/
>
>"Order by random" by itself works, "union" without "order by random"
>works, but using order by random AND union causes a disconnect.
>
>SELECT * FROM v$version;
>
>BANNER
>Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
>PL/SQL Release 9.2.0.4.0 - Production
>CORE 9.2.0.3.0 Production
>TNS for Solaris: Version 9.2.0.4.0 - Production
>NLSRTL Version 9.2.0.4.0 - Production
>
>Any hints??
>Thanks in advance

Upgrade to 9.2.0.8.
This will be the only version supported after July 31.

-- 
Sybrand Bakker
Senior Oracle DBA