Charles Hooper wrote:
> 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.
>
Thanks a lot for your hints, Anurag and Charles! It turned out that the
culprit actually was subquery elimination.
Regards,
Karsten
Received on Fri Mar 16 2007 - 15:56:52 CDT
