Strange issue with SQL containing 'AND 1=0'

Hi,

a fried of mine came up with a very strange issue. Executing the query below yields a result, although it seems clear that it should not:

SELECT * FROM sometable
WHERE some_date BETWEEN TO_DATE ('01012007 090000', 'MMDDYYYY HH24MISS') AND TO_DATE ('03132007 090000', 'MMDDYYYY HH24MISS') + 1 AND ( 1=0 OR some_number LIKE '2110520') AND ( 1=0 )
ORDER BY some_column_1, some_column_2

This should not return anything, because of the last AND clause always yields FALSE. But it does...

Interestingly enough, this happens only on a single installation (9.2.0.2), executing it on other test systems brought up the result I'd expect: nothing at all.

I wasn't able to do much further analysis, because I have no access to the system in question. The first guess was some sort of ill-advised cursor sharing, but apparently it was not. Rewriting the query to something like

SELECT * FROM sometable
WHERE (1=0 OR some_number = '2110520')
AND (1=0) had the exact same effect: each row satisfiying the "some_number = '...'" condition came back as a result. Flushing the shared pool had no effect as well.

Now the funny part is, if you swap the order of conditions to something like

SELECT * FROM sometable
WHERE (1=0) AND (1=0 OR some_number = '2110520')

it works as expected. The result set is empty.

I'm baffled and have no idea what is going on... Just wanted to ask here if someone seen this before? Maybe I'm just missing the obvious...

Thanks in advance for your thoughts!

Regards,
Karsten Received on Thu Mar 15 2007 - 15:35:06 CDT