Re: Surprising bug

Thanks, Andy.

Looks like I should upgrade.

Rich

"Andy Hassall" <andy_at_andyh.co.uk> wrote in message news:09al90tth88ou70gr5610uih19fiml9mn1_at_4ax.com...
> 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
Received on Thu May 06 2004 - 16:17:22 CDT