On Mar 15, 4:35 pm, Karsten Baumgarten <karsten.baumgar..._at_gmx.net>
wrote:
> 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
You might be hitting a bug due to a query optimization. Do you see
the same results if you execute the following before executing the
query:
ALTER SESSION SET "_NO_OR_EXPANSION"=TRUE;
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Mar 15 2007 - 17:46:00 CDT
