Re: Oracle SQL bug in 9.2.0.8

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Thu, 10 Feb 2011 16:54:52 +1100
Message-ID: <MPG.27be28b5995e01ba989868_at_news.x-privat.org>

In article <7e05e7cd-e4ca-4f63-bf56-4f8175ac0245 _at_u24g2000prn.googlegroups.com>, DG problem says...
> select 'Why is this displayed?'
> from DUAL
> where 'WHY ' = 'WHY'
> ;

Someone else may be able to confirm that this is an acceptable interpretation of the ANSI SQL99 standard for the comparison of CHAR elements of unequal length where trailing whitespace only occurs.

Oracle seems to default strings in where clauses as CHAR.

The same behaviour does not happen for VARCHAR or VARCHAR2 elements, at least in 11.2.

select
  case
    when cast('WHY ' as char(4)) = cast('WHY' as char(3))     then 'Strings are seen as the same'     else 'Strings are seen as different'   end as CHAR_TYPE,
  case
    when

    cast('WHY ' as varchar(4)) = cast('WHY' as varchar(3)) 
    then 'Strings are seen as the same' 
    else 'Strings are seen as different' 

  end as VARCHAR_TYPE
from DUAL

Geoff M Received on Wed Feb 09 2011 - 23:54:52 CST