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 :)

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

Original text of this message