Bug? ORA-03113 on ORDER BY DESC (9iR2)

[Bob Burgess]

We're running 9iR2 on Win2k and it seems to have a strange bug which I
can't find reference to in Metalink or Google.

When I execute this:
SELECT 'A' x FROM dual ORDER BY 1 DESC
or this:
SELECT 'A' FROM dual ORDER BY 1 DESC
...I get an immediate crash of the server thread: ORA-03113
End-of-file on communication channel.

But this works:
SELECT dummy FROM dual ORDER BY 1 DESC
and this works also:
SELECT 'A' x FROM dual ORDER BY x DESC

Does this happen to everyone? The crash seems to happen when sorting,
descending, by column position, for an un-named column, whether or not
it has an alias.

Thanks for any ideas.

Bob Burgess
Montreal

[Turkbear]

bur...@sympatico.ca (Bob Burgess) wrote:

Can't reproduce the error ( at least on the 8.1.5 and 9.2 I tested it on)
----------------------------------------------------------------------------------------------------
1* select 'A' x from dual order by 1 desc
SQL> /

X
-
A

SQL>
-------------------------------------------------------------------------------------------------------
Does your DUAL table have more than 1 row?

Jg

[VC]

Hello Bob,

We discovered the bug when we upgraded to 9.2.0.4. Oracle have been
notified but there is no fix yet.

VC

"Bob Burgess" <bur...@sympatico.ca> wrote in message
news:df35b276.04010...@posting.google.com...

[Daniel Morgan]

Bob Burgess wrote:

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 6 10:17:43 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> SELECT 'A' x FROM dual ORDER BY 1 DESC;

SELECT 'A' x FROM dual ORDER BY 1 DESC

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL>

What causes it is the ORDER BY DESC. with a single literal value. ORDER
BY and ORDER BY ASC do not cause the problem. I created a test table
with two records and it did the same thing.

SQL> select testcol from test order by 1 desc;

TESTCOL
--------------------
Y
Y
Y

SQL> select 'Y' from test order by 1 desc;
select 'Y' from test order by 1 desc
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)

[Michel Cadot]

"Bob Burgess" <bur...@sympatico.ca> a écrit dans le message de
news:df35b276.04010...@posting.google.com...

That's a known bug on 9.2.0.4: n°3110923
Workaround: try with hint NOPARALLEL (or do not use a constant column in order by).

Regards
Michel Cadot

[Frank van Bortel]

Bob Burgess wrote:

On RH9:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> SELECT 'A' FROM dual ORDER BY 1 DESC;

SELECT 'A' FROM dual ORDER BY 1 DESC

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

--

Regards,
Frank van Bortel

[Mladen Gogala]

On Tue, 06 Jan 2004 08:42:59 -0800, Bob Burgess wrote:

> We're running 9iR2 on Win2k and it seems to have a strange bug which I
> can't find reference to in Metalink or Google.
>
> When I execute this:
> SELECT 'A' x FROM dual ORDER BY 1 DESC
> or this:
> SELECT 'A' FROM dual ORDER BY 1 DESC
> ...I get an immediate crash of the server thread: ORA-03113
> End-of-file on communication channel.

This is from the metalink:

3110923 RDBMS Server: Patch
CRASH WITH ''ORDER BY DESC'' ON A CONSTANT COLUMN ORA-3113 9.2.0.4
11-DEC-2003

It looks exactly like your problem.
--
None of us is as dumb as all of us.
(http://www.despair.com/meetings.html)

[Mladen Gogala]

On Tue, 06 Jan 2004 17:45:31 +0000, VC wrote:

> Hello Bob,
>
> We discovered the bug when we upgraded to 9.2.0.4. Oracle have been
> notified but there is no fix yet.

There is:

3110923 RDBMS Server: Patch
CRASH WITH ''ORDER BY DESC'' ON A CONSTANT COLUMN ORA-3113 9.2.0.4
11-DEC-2003

>
>

> VC
>
> "Bob Burgess" <bur...@sympatico.ca> wrote in message
> news:df35b276.04010...@posting.google.com...
>> We're running 9iR2 on Win2k and it seems to have a strange bug which I
>> can't find reference to in Metalink or Google.
>>
>> When I execute this:
>> SELECT 'A' x FROM dual ORDER BY 1 DESC
>> or this:
>> SELECT 'A' FROM dual ORDER BY 1 DESC
>> ...I get an immediate crash of the server thread: ORA-03113
>> End-of-file on communication channel.
>>
>> But this works:
>> SELECT dummy FROM dual ORDER BY 1 DESC
>> and this works also:
>> SELECT 'A' x FROM dual ORDER BY x DESC
>>
>>
>> Does this happen to everyone? The crash seems to happen when sorting,
>> descending, by column position, for an un-named column, whether or not
>> it has an alias.
>>
>> Thanks for any ideas.
>>
>> Bob Burgess
>> Montreal

--

[VC]

Mladen,

Thank you.

VC

"Mladen Gogala" <mgo...@adelphia.net> wrote in message
news:pan.2004.01.11...@adelphia.net...

[Mark D Powell]

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:<3ffb0064$0$19284$626a...@news.free.fr>...

Thanks for posting the bug number, 3110923. The metalink report is
visible to the public, is dated Dec 2003, and there is no fix as of
yet.

HTH -- Mark D Powell --