Re: Bug Report

Mark D Powell 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.

What I am saying is that the SQL runs just fine in SQL*Plus even if table2 does not contain any field with the name of field1. Here's my exact test query.

SELECT location_code
FROM customers
WHERE location_code IN (

   SELECT location_code
   FROM customers
   MINUS
   SELECT location_code
   FROM inventory;

The table inventory has no field named location_code. Oracle 8.1.6 does not care. It runs it anyway and returns a result.

Daniel A. Morgan Received on Thu Jan 25 2001 - 22:50:05 CST