Strange SQL behaviour when using query short-cuts

I am running a 9.0.1 database on a W2K server and have come across some strange behaviour with a SQL query. I have a query which runs in a PL/SQL cursor which has several PL/SQL variables used to switch on and off certain rules. One idea I had was to have two queries UNIONed together with a simple switch selecting which half was to operate (I know it sounds like there are probably better ways of doing this but I have my reasons).

To cut a long story short (too late, I hear you cry?), adding "AND 0=1" to a query will only *sometimes* be included in the rules. If you will excuse the long post, below is a simplified example which can be copied & pasted into SQL*Plus to demonstrate the issue.

Why does it behave this way, as I am sure it did not with oracle 8.0.5 (I used to use this approach to get really slow queries short-cut when analysing execution paths)?

If you change the "0=1" to "ID=ID+1" it works as expected, but takes longer (normally, short-cutting a query takes no time, which is why I was not concerned about using a UNIONed query).

• Same thing happens with non-temporary tables create global temporary table play ( id number(12) not null, name varchar2(30) not null, dob date ) on commit delete rows;

INSERT INTO play
(id, NAME, dob)
VALUES (1, 'Bob', TO_DATE ('01-01-1971','dd-mm-yyyy'));

INSERT INTO play
(id, NAME, dob)
VALUES (1, 'Ben', TO_DATE ('02-03-1974','dd-mm-yyyy'));

• A simple example - it works as expected SELECT id, NAME, TO_CHAR (dob, 'DD-MON-YYYY HH24:MI:SS') FROM play WHERE id < 10 AND ('%e%' IS NULL OR LOWER(Name) LIKE LOWER('%e%')) AND ('' IS NULL OR dob >= TO_DATE('', 'DD-MON-YYYY HH24:MI:SS'));
• (Remember that '' is identical to NULL to oracle)
• As above, but with "0=1" as the first part of the restrictions.
• This also works as expected. SELECT id, NAME, TO_CHAR (dob, 'DD-MON-YYYY HH24:MI:SS') FROM play WHERE 0=1 AND id < 10 AND ('%e%' IS NULL OR LOWER(Name) LIKE LOWER('%e%')) AND ('' IS NULL OR dob >= TO_DATE('', 'DD-MON-YYYY HH24:MI:SS'));
• As above, but with the "0=1" moved to the end of the restrictions.
• In this case it does NOT work as expected. SELECT id, NAME, TO_CHAR (dob, 'DD-MON-YYYY HH24:MI:SS') FROM play WHERE id < 10 AND ('%e%' IS NULL OR LOWER(Name) LIKE LOWER('%e%')) AND ('' IS NULL OR dob >= TO_DATE('', 'DD-MON-YYYY HH24:MI:SS')) AND 0=1;

  ---

Is this a bug? I think we should be told!

ETA Received on Fri Jul 04 2003 - 13:31:11 CEST