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

0 weergaven
Naar het eerste ongelezen bericht

Bob Burgess

ongelezen,
6 jan 2004 11:42:5906-01-2004
aan
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

ongelezen,
6 jan 2004 12:00:5006-01-2004
aan
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

ongelezen,
6 jan 2004 12:45:3106-01-2004
aan
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

ongelezen,
6 jan 2004 13:26:0506-01-2004
aan
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

ongelezen,
6 jan 2004 13:38:5506-01-2004
aan

"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

ongelezen,
6 jan 2004 16:21:5606-01-2004
aan
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

ongelezen,
11 jan 2004 01:29:5211-01-2004
aan
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

ongelezen,
11 jan 2004 01:30:4711-01-2004
aan
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

ongelezen,
11 jan 2004 10:32:1411-01-2004
aan
Mladen,

Thank you.

VC

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

Mark D Powell

ongelezen,
12 jan 2004 09:25:3212-01-2004
aan
"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 --

Allen beantwoorden
Auteur beantwoorden
Doorsturen
0 nieuwe berichten