Re: Strange behavior with union/order by

From: John K. Hinsdale <hin_at_alma.com>
Date: Thu, 19 Jul 2007 07:30:15 -0700
Message-ID: <1184855415.823786.177470@g12g2000prg.googlegroups.com>

On Jul 18, 12:05 pm, "Chris L." <diver..._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:
> ...
> 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;
> ...
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
> ...
> Any hints??

Chris,

Fails for me too on 10.2.0.1.0

Looks like a bug causing your session's server-side shadow process to crash (my log showed SIGSEGV - sure sign of a bug). Technically, the server-side process probably did not "disconnect the session" as you suggest, it likely just up and died. Your client (sqlplus?) disconnected things, w/ the ORA-03113, after it lost contact.

I'd look in your "bdump" dir for alert log, and also for a recently dated "orcl_xxxx_ora_NNNNN.trc" in your "udump" dir and send those to Oracle in a bug report.

In the meantime, it seems to work to do the ORDER BY "outside" the UNION:

    select * from (

        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;

That may get you through the day.

It was also pointed out that your 9.2 version (9.2.0.4.0) is out of date, and that an upgrade to 9.2.0.8 is needed after 7/31 of this year. However, it was not pointed out whether 9.2.0.8 fixes the specific misbehavior you experience. I don't run 9.2.0.8, so I would not know; but I would not assume one way or the other, either. Your posted example is quite complete so it should be a simple matter for anyone running 9.2.0.8 to see and advise.

HTH,         John Hinsdale Received on Thu Jul 19 2007 - 09:30:15 CDT