| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Connection closed on simple query
OK, the mystery is solved. It is in fact a bug (#2352928) in the RDBMS.
Oracle's description:
> A query which includes FULL OUTER JOIN sometimes returns the wrong
> result or causes a core dump if there is a subquery with a
> GROUP BY on the right hand side of the FULL OUTER JOIN.
The problem is supposedly fixed in patch 9.2.0.2. I already asked the DBA to update.
Thanks to those of you who have taken the time to run the script.
Greetings,
-Caspar v. Seckendorff
"Rauf Sarwar" <rs_arwar_at_hotmail.com> schrieb im Newsbeitrag
news:92eeeff0.0306161213.3e6f47e5_at_posting.google.com...
> seckendorff_at_alphatec.de (Caspar von Seckendorff) wrote in message
news:<5b031a3f.0306120601.36adc33b_at_posting.google.com>...
> > On Oracle 9.2i I get the error ORA-03113 when executing the following
> > script. I actually wrote the following example for analysing a more
> > complex query, but then I realized that I can't execute it - Oracle
> > just simply closes the connection.
> >
> > Is this a bug? How can I work around it?
> >
> > Greetings,
> >
> > -Caspar
> >
> >
> > CREATE TABLE test_table (
> > country_id NUMBER(10),
> > country_text CHAR(20),
> > turnover NUMBER(10),
> > t_year NUMBER(4)
> > );
> >
> > INSERT INTO test_table (country_id, country_text, turnover, t_year)
> > VALUES (1, 'USA', 300, 1999);
> >
> > INSERT INTO test_table (country_id, country_text, turnover, t_year)
> > VALUES (1, 'USA', 300, 2000);
> >
> > INSERT INTO test_table (country_id, country_text, turnover, t_year)
> > VALUES (1, 'USA', 300, 2001);
> >
> > INSERT INTO test_table (country_id, country_text, turnover, t_year)
> > VALUES (2, 'Germany', 300, 2000);
> >
> > INSERT INTO test_table (country_id, country_text, turnover, t_year)
> > VALUES (2, 'Germany', 300, 2001);
> >
> > SELECT
> > COALESCE(t00.country_text, t01.country_text),
> > t00.turnover,
> > t01.turnover
> > FROM (
> > SELECT
> > country_id,
> > MAX(country_text) AS country_text,
> > SUM(turnover) AS turnover
> > FROM test_table
> > WHERE t_year = 1999
> > GROUP BY country_id
> > ) t00 FULL JOIN (
> > SELECT
> > country_id,
> > MAX(country_text) AS country_text,
> > SUM(turnover) AS turnover
> > FROM test_table
> > WHERE t_year = 2000
> > GROUP BY country_id
> > ) t01 ON t00.country_id = t01.country_id
> > ORDER BY 1;
> >
>
 |
 |