bug with UNION ALL in subquery in 7.1.3.4.0

UNION ALL sometimes causes wrong answers when used in the subquery of an IN predicate.

I suspect this syntax is being incorrectly converted to a join.

For those who aren't familiar with it, UNION ALL was introduced in ORACLE 7. It's like UNION, but duplicates are not eliminated. For that reason, it's supposed to be more efficient in some cases. It's not needed in the example below, but it should not cause the harm it causes. Looks like Oracle didn't test this very much.

I'd report this bug directly to Oracle, but they want to charge me extra for that privilege. :-(

Until we get more info, I advise people to avoid UNION ALL in subqueries.

Here's a simple example of the bug, with details:

    Last interactive login on Wednesday, 25-JAN-1995 09:03     Last non-interactive login on Tuesday, 24-JAN-1995 17:19     Current time: Wednesday, 25-JAN-1995 09:06

    Dartmouth College                           Kiewit Cluster node CEDAR
    Boot disk: $57$DUA51:                       OpenVMS VAX V6.1

$ sqlplus _at_bug.sql

SQL*Plus: Release 3.1.3.1.3 - Production on Wed Jan 25 09:09:18 1995

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.

Connected to:
Oracle7 Server Release 7.1.3.4.0 - Production Release With the Parallel Server option
PL/SQL Release 2.1.3.2.0 - Production

ADM> connect scott/tiger
Connected.
ADM> create table test(x number);

Table created.

ADM> insert into test
  2 select 1 from dual union select 2 from dual union select 3 from dual;

3 rows created.

ADM>
ADM> select * from test
  2 where x in

  3          (select x from test
  4           union all
  5           select x from test);

         X


----------

         1
         1
         2
         2
         3
         3

6 rows selected.

ADM> -- result above is not correct.
ADM>
ADM> select * from test
  2   where x in
  3          (select x from test
  4           union
  5           select x from test);

         X


----------

         1
         2
         3

3 rows selected.

ADM> -- above result is correct.
ADM> select * from test;

         X

         1
         2
         3

3 rows selected.

ADM> -- above is proof of what is in test. ADM> select x from test
  2 union all
  3 select x from test;

         X

         1
         2
         3
         1
         2
         3

6 rows selected.

ADM> -- above is what the UNION ALL returns. ADM> select * from test
  2 where x in
  3 (1,2,3,1,2,3);

         X

         1
         2
         3

3 rows selected.

ADM> -- works if we list the result of the UNION ALL
ADM> -- as literals.
ADM> drop table test;

Table dropped.

ADM> exit
Disconnected from Oracle7 Server Release 7.1.3.4.0 - Production Release With the Parallel Server option
PL/SQL Release 2.1.3.2.0 - Production
$ Received on Wed Jan 25 1995 - 15:36:25 CET