Re: Bug Report

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 25 Jan 2001 17:41:27 +0100
Message-ID: <94pl0k$tjq$1@s1.read.news.oleane.net>

"Mark D Powell" <markp7832_at_my-deja.com> a écrit dans le message news: 94pf55$fd0$1_at_nnrp1.deja.com...
> In article <3A6FB505.FB0F2B6C_at_exesolutions.com>,
> "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:
> > I have found in Oracle 8.1.6 that the following code will run in
 PL/SQL
> >
> > SELECT field1
> > FROM table1
> > WHERE field1 IN (
> > SELECT field1
> > FROM table1
> > MINUS
> > SELECT field1
> > FROM table2)
> >
> > Even if field1 does not exist in table2.
> >
> > Be very very careful.
> >
> > Daniel A. Morgan
> >
> Daniel, I do not understand what you mean by field1 does not exist in
> table2. Are you saying the column field1 does not exist or the value
> does not exist?
>
> Since the difference between two queries can be defined as the rows
> returned by the first query that are not also returned by the second
> query this would seem to be the correct response if you are talking
> value. In fact, as written it appears to me that the results of your
> outer query should match the result of the inner query so why do you
> even have an outer query? Should one of the table names be different
> or did you mean to say 'does exist in table2'?
>
> I have had difficulty seeing the obvious before so it is very possible
> I am missing something here.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
> Sent via Deja.com
> http://www.deja.com/

Daniel means column field1 does not exists. I check it with 8.1.5 and 8.1.6 :

v815> create table t1 (col1 number);
v815> create table t2 (col2 number);
v815> select col1 from t2;
select col1 from t2
       *

ERROR at line 1:
ORA-00904: invalid column name

v815> select col1 from t1 where col1 in (select col1 from t2);

no rows selected

v815> insert into t1 values (0);
v815> select col1 from t1 where col1 in (select col1 from t2);

no rows selected

v815> insert into t2 values (0);
v815> select * from t1;

      COL1

---

         0

1 row selected.

v815> select * from t2;

      COL2

---

         0

1 row selected.

v815> select col1 from t1 where col1 in (select col1 from t2);

      COL1

---

         0

1 row selected.

Actually, you get all the rows of t1 as soon as there is a row in t2.

v815> insert into t1 values (-1);
v815> select * from t1;

      COL1

---

         0
        -1

2 rows selected.

v815> select * from t2;

      COL2

---

         0

1 row selected.

v815> select col1 from t1 where col1 in (select col1 from t2);

      COL1

---

         0
        -1

2 rows selected.

--
Cheers
Michel

Received on Thu Jan 25 2001 - 10:41:27 CST