Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Strange issue with SQL containing 'AND 1=0'

Re: Strange issue with SQL containing 'AND 1=0'

From: Anurag Varma <avoracle_at_gmail.com>
Date: 15 Mar 2007 14:06:49 -0700
Message-ID: <1173992809.797747.212940@l75g2000hse.googlegroups.com>


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

Its a bug clearly ..
Appears like oracle is trying to do elimininate common subexpression incorrectly.
Try setting _eliminate_common_subexpr = FALSE; and rerun your query to see if it fixes it...

The solution would be to patch. 9.2.0.8 is the latest version amongst 9.2 ... you are way way behind in patching :)

Anurag Received on Thu Mar 15 2007 - 16:06:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US