Re: Surprising bug

On Thu, 06 May 2004 20:54:19 GMT, "Rich Dillon" <richdillon_at_no.spam> wrote:

>In another forum, someone asked a SQL question which promted me to run the
>following test. On my version of Oracle (shown below) on my Windows XP
>laptop, the results pointed to what seems to be an extraordinary bug
>involving correlated subqueries and views (or derived tables). Oddly, I
>haven't seen this bug before. Here's the repro script. Does anyone have
>information on this?
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>

[snip]
>
>On my machine, the first (using the table) is correct. The second (using
>the view) is incorrect. These are the results:
>
> WINNER WINS
> -------------------- ----------
> Penguins 3
>
> 1 row selected.
>
>
> WINNER WINS
> -------------------- ----------
> Ducks 2
> Houseflies 1
> Penguins 3
>
> 3 rows selected.

 9.2.0.1.0 wasn't exactly bug free - but it appears fixed now.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning option
JServer Release 9.2.0.5.0 - Production

User created.

[snip]

Connected.

[snip]

SQL> SELECT * FROM wintable;

WINNER                     WINS
-------------------- ----------
Ducks                         2
Houseflies                    1
Penguins                      3

SQL> SELECT * FROM winview;

WINNER                     WINS
-------------------- ----------
Ducks                         2
Houseflies                    1
Penguins                      3

SQL>
SQL> SELECT *
  2 FROM wintable w1
  3 WHERE NOT EXISTS (

  4     SELECT *
  5     FROM wintable w2
  6     WHERE w2.wins > w1.wins);

WINNER                     WINS
-------------------- ----------
Penguins                      3

SQL>
SQL> SELECT *
  2 FROM winview w1
  3 WHERE NOT EXISTS (

  4     SELECT *
  5     FROM winview w2
  6     WHERE w2.wins > w1.wins);

WINNER                     WINS
-------------------- ----------
Penguins                      3

--
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space