By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

Strange SQL behaviour when using query short-cuts

P: n/a
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
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.