Re: Strange behavior with union/order by

On Jul 19, 11:30 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
> 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

My client is sqlTools (www.sqltools.net). The upgrading decision can't be taken by me (just a developer ;) but if the behavior exists on 10.2.0.1.0, I think there's zero chance of it working correctly on 9.2.0.8 (it's clear to me however that an upgrade is needed, independently of this particular issue)

Thanks John for your time and recommendations. Chris Received on Thu Jul 19 2007 - 16:00:34 CDT