Re: Bug Report

Jonathon's response (as usual) is exactly right. Allow me to clarify.

The reported problem is not an Oracle bug. The SQL statement is being interpretted and executed per the specifications. The "bug", if there is one, is in the query.

This is an example of why I encourage (require) SQL developers to fully qualify every column (via aliases) in every expression in every statement that references more than a single view or table. It might be a little more work, but it is good programming practice.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:980457875.20267.0.nnrp-13.9e984b29_at_news.demon.co.uk...
>
> There is a technical term for this,
> something like column capture,
> or scope creep.
>
> If field1 does not exist in table2
> then the fully qualified interpretation
> of your query is:
>
> SELECT t00.field1
> FROM table1 t00
> WHERE t00.field1 IN (
> SELECT t01.field1
> FROM table1 t01
> MINUS
> SELECT t00.field1
> FROM table2)
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
>
> Publishers: Addison-Wesley
> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> Daniel A. Morgan wrote in message <3A6FB505.FB0F2B6C_at_exesolutions.com>...
> >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
> >
>
>
>
Received on Sat Jan 27 2001 - 15:09:01 CST