Re: Oracle SQL bug in 220.127.116.11
Date: Wed, 9 Feb 2011 16:34:54 -0800 (PST)
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 '
> '<'||Y||'>', Y
> ( select 'WHY ' Y from dual) x
> 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:
2 '<'||Y||'>', Y
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:
look for the "Blank-Padded and Nonpadded Comparison Semantics" paragraph. Received on Wed Feb 09 2011 - 18:34:54 CST