*****************************( Source:
BUGDB )*******************************
Article-ID: <Bug:486696> Related: <BugMatrix:486696> Base: <Bug:396003>
Customer: Created: 30-APR-97
Component: RDBMS Comp Ver: 7.3.2.3.0 Rel St: P Updated:
08-JUL-97
Sub Comp: SQL EXECUTIONRDBMS Ver: 7.3.2.3.0 By: LSCOTTO
Status: 96,Closed, Duplicate Bug
Sup Pri: 2,Severe Loss of Service Fixed In Ver: 7.3.3
O/S: 87 Digital Unix
PL Group: UNIX Gen/Port: G Assigned: SBEDARKA Error #: - Pub: Y
Abstract: QUERIES AGAINST SOME VIEWS WITH UNION SUBQUERIES CAUSE ORA 3113
DISCONNECTS
*
- DTOW 04/30/97 04:06 pm ***
The following view, which is analogous to a much more complex view needed in
Applications, can be created without error, but selects against the view
cause ORA 3113: end-of-file on communication channel:
create or replace view dtow_test
as
select e.ename, d.dname, e2.ename mgrname
from emp e,
dept d,
emp e2
where e.deptno = d.deptno
and e.empno in
(select e2.empno from dual
union
select e3.empno
from emp e3
where e3.mgr = e2.empno);
.
select * from dtow_test; will get the database disconnect. It appears to be
key that there is a union in a correlated IN subquery, and that there is
also
a join in the outer query. (With dept removed from the query, the select *
from this view results in an error for sort key too long, I think, which is
also puzzling, but more benign.)
.
This bug is causing a P1 Applications bug, #479020.
.
I don't know if this is relevant to reproducing the problem, but the schema
where I reproduced the problem used the rule-based optimizer and had unique
indexes on dept.deptno and emp.empno and a non-unique index on emp.deptno.
Otherwise, emp and dept were in their usual shape with their usual data, so
I
hope this is no trouble to reproduce.
- KOKANISH 05/02/97 11:14 am *** (CHG: Asg->SNUTAKKI)
- KOKANISH 05/02/97 11:14 am ***
On Solaris release, this ends up with the termination of SQL*Plus/svrmgr.
dbx output and traces are in bug$.
- SBEDARKA 05/07/97 10:39 am *** (CHG: Sta->36 Asg->SBEDARKA)
- SBEDARKA 05/07/97 10:39 am *** (CHG: Fixed->7.3.3)
- SBEDARKA 05/07/97 10:39 am ***
This problem is not reproducible in V7.3.[3-4] on solaris. It is
reproducible
in V7.3.2(but not in V7.2.3 either). It also looks to be a duplicate of
396003.
- BUGDBA 06/11/97 08:00 pm *** (CHG: Sta->96)
- SNUTAKKI 06/23/97 02:33 pm ***
In fact this is the duplicate of bug396003 which was fixed in 7.3.2.3
- LSCOTTO 07/08/97 05:20 pm ***
Received on Tue Nov 24 1998 - 06:55:03 CST