Re: Oracle SQL bug in 9.2.0.8
From: Noons <wizofoz2k_at_gmail.com>
Date: Wed, 9 Feb 2011 16:34:54 -0800 (PST)
Message-ID: <b2f98967-d734-4d41-9536-a6550c6dfbc6_at_8g2000prb.googlegroups.com>
On Feb 10, 11:06 am, DG problem <skatef..._at_gmail.com> wrote:
> I've only just come across this possible SQL bug and was wondering why
> this SQL returns a result? I got it through an email and had to change
> it to Oracle's syntax as the original used the concat operator of +
> and it didn't use DUAL.
>
> -- Note the spaces after 'WHY '
> select
> '<'||Y||'>', Y
> from
> ( select 'WHY ' Y from dual) x
> where
> Y = 'WHY'
> ;
>
> P.S. Yes I know that 9i is well and truly obsolete :)
Date: Wed, 9 Feb 2011 16:34:54 -0800 (PST)
Message-ID: <b2f98967-d734-4d41-9536-a6550c6dfbc6_at_8g2000prb.googlegroups.com>
On Feb 10, 11:06 am, DG problem <skatef..._at_gmail.com> wrote:
> I've only just come across this possible SQL bug and was wondering why
> this SQL returns a result? I got it through an email and had to change
> it to Oracle's syntax as the original used the concat operator of +
> and it didn't use DUAL.
>
> -- Note the spaces after 'WHY '
> select
> '<'||Y||'>', Y
> from
> ( select 'WHY ' Y from dual) x
> where
> Y = 'WHY'
> ;
>
> P.S. Yes I know that 9i is well and truly obsolete :)
Same in 10gr2. And I suspect it'd be the same in 11g. I think you've been trapped by implicit conversion in Oracle, which doesn't happen in the original MSSQL.
This produces the expected result and eliminates implicit conversions:
1 select
2 '<'||Y||'>', Y
3 from
4 ( select cast('WHY ' as char(6)) Y from dual) x
5 where
6* Y = cast('WHY' as varchar(3))
SQL> /
no rows selected
The relevant part of the documentation can be found here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements002.htm#i55214
look for the "Blank-Padded and Nonpadded Comparison Semantics" paragraph. Received on Wed Feb 09 2011 - 18:34:54 CST