Re: Bug Report

In article <94pf55$fd0$1_at_nnrp1.deja.com>,   Mark D Powell <markp7832_at_my-deja.com> wrote:
> 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/
>

I tried this on a database here and it returns 'No rows found' even when the column does not exist on table2. If I remove the outer query and just do:
  select field1
  from table1
  minus
  select field1
  from table2;

I get the following error message:
select field1

       *
ERROR at line 4:
ORA-00904: invalid column name
(This is to be expected because field1 does not exist on table2)

Very interesting behavior.......It does not report the error when wrapped in an outer join. Also, the no rows found is incorrect. Logically, if it is supposed to work this way, I should get all the values from field1 and table1. So, the 'No rows found' message implies that the column does exist on table2 and that all the data on both tables matches. Does this sound right??? I am running 8.1.6.0.0 on Solaris 2.6

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 25 2001 - 10:39:27 CST