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

On Feb 10, 11:06=A0am, DG problem <skatef...@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 =A0 '
> select
> =A0 '<'||Y||'>', Y
> from
> =A0 ( select 'WHY =A0 ' Y from dual) x
> where
> =A0 Y =3D '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 =3D 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_elements=
002.htm#i55214

look for the "Blank-Padded and Nonpadded Comparison Semantics"
paragraph.

On Feb 10, 10:34=A0am, Noons <wizofo...@gmail.com> wrote:
> Same in 10gr2. =A0And 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.
>

But there is no conversion going on. It is looking for an exact match.
Also, you can see that the spaces are still present in the output.

The result displayed, 'WHY   ', does not equal 'WHY'.

I guess I should have used this simpler example:

select 'Why is this displayed?'
from DUAL
where 'WHY   ' = 'WHY'
;

It seems incorrect to me, but I guess it must be correct in database
terms?

I'm sure this must have been covered online some where.

"DG problem" <skatefree@gmail.com> a �crit dans le message de news: 
2cd5fcc9-694f-4a81-bbc4-29eaf9eaf9d5@m27g2000prj.googlegroups.com...
| 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 :)

Read the following thread in AskTom:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

Regards
Michel

In article <7e05e7cd-e4ca-4f63-bf56-4f8175ac0245
@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

DG problem wrote,on my timestamp of 10/02/2011 12:04 PM:
> On Feb 10, 10:34 am, Noons<wizofo...@gmail.com>  wrote:
>> 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.
>>
>
> But there is no conversion going on. It is looking for an exact match.
> Also, you can see that the spaces are still present in the output.
>
> The result displayed, 'WHY   ', does not equal 'WHY'.
>


All I can say is: read the manual I pointed out.
Implicit conversion means you don't see it.  Otherwise it'd be explicit.

Geoff Muldoon wrote,on my timestamp of 10/02/2011 4:54 PM:

>
> 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.

Actually, if someone read the manual paragraph I pointed out, it'd be very clear 
what is going on.  It's got nothing to do with SQL99.

On Feb 10, 12:25=A0am, Noons <wizofo...@yahoo.com.au> wrote:
> Geoff Muldoon wrote,on my timestamp of 10/02/2011 4:54 PM:
>
>
>
> > 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.
>
> Actually, if someone read the manual paragraph I pointed out, it'd be ver=
y clear
> what is going on. =A0It's got nothing to do with SQL99.

Poking around a bit in the sqlplus manual, the only clue that things
are defined default as char are the define and accept commands.
Couldn't really find a clue that default strings are char, though in
retrospect I guess the define definition should be one.  A weak
argument (very weak "we've always done it that way") against what
asktom says about always using varchar2, for "historical reasons" I
suppose.  It explains some corrections I had to make to sqlplus
scripts when I upgraded from char to varchar2, and didn't understand
why at the time (or I probably did, but don't remember, fog of war in
the coding deathmarch).  Forehead slapping obvious, now.  I'm still
finding proprietary 4GL code that has strange extra code from the char
days.

One would have to have access to the SQL99 specs to know if this
default is actually considered there.  My guess is it wouldn't be, it
would be more likely the character types are defined, but defaults...
vendor choice?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2011/feb/08/if-co-worker-calls-you-old-m=
an-can-you-sue-age-dis/

Noons says...
> 
> Geoff Muldoon wrote,on my timestamp of 10/02/2011 4:54 PM:
> 
> >
> > 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.
> 
> Actually, if someone read the manual paragraph I pointed out, it'd be very clear 
> what is going on.  It's got nothing to do with SQL99.

Your link indeed explains Oracle's differential treatment of blank-padded 
and nonpadded character types.  My posting simply reinforced that Oracle 
default casts strings in where clauses to the blank-padded CHAR/NCHAR 
rather than the nonpadded VARCHAR/etc types.  My reference to SQL99 was 
merely questioning whether Oracle's documented methodology is compliant 
with the standard.

GM

Geoff Muldoon says...
> 
> Noons says...
> > 
> > Geoff Muldoon wrote,on my timestamp of 10/02/2011 4:54 PM:
> > 
> > >
> > > 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.

> > Actually, if someone read the manual paragraph I pointed out, it'd be 
very clear 
> > what is going on.  It's got nothing to do with SQL99.
> 
> Your link indeed explains Oracle's differential treatment of blank-padded 
> and nonpadded character types.  My posting simply reinforced that Oracle 
> default casts strings in where clauses to the blank-padded CHAR/NCHAR 
> rather than the nonpadded VARCHAR/etc types.  My reference to SQL99 was 
> merely questioning whether Oracle's documented methodology is compliant 
> with the standard.

And following up my own post ...

International Standard ISO/IEC 9075:1992

8.2 <comparison predicate>

3) The comparison of two character strings is determined as follows:

a) If the length in characters of X is not equal to the length in 
characters of Y, then the shorter string is effectively replaced, for the 
purposes of comparison, with a copy of itself that has been extended to 
the length of the longer string by concatenation on the right of one or 
more pad characters, where the pad character is chosen based on CS. If CS 
has the NO PAD attribute, then the pad character is an implementation-
dependent character different from any character in the character set of X 
and Y that collates less than any string under CS. Otherwise, the pad 
character is a <space>.

....

So it's not only is it not a "bug", and not only is it fully documented,  
Oracle's treatment of blank-padded CHAR types with trailing whitespace is 
actually consistent with the ANSI standard.

The only remaining issue is where it might be documented that Oracle's 
implicit casting of strings in WHERE clauses is to blank-padded rather 
than nonpadded types, and whether this is configurable.

GM

On Feb 11, 10:17=A0am, Geoff Muldoon <geoff.muld...@trap.gmail.com>
wrote:

> So it's not only is it not a "bug", and not only is it fully documented, =
=A0
> Oracle's treatment of blank-padded CHAR types with trailing whitespace is
> actually consistent with the ANSI standard.
>
> The only remaining issue is where it might be documented that Oracle's
> implicit casting of strings in WHERE clauses is to blank-padded rather
> than nonpadded types, and whether this is configurable.

I don't think there is a written rule anywhere for that but of course
I may be wrong.
This is what I think is happening: when the column involved in a
comparison is a string constant padded with blanks (as opposed to a
column in a table which has a defined type), Oracle implicitly
converts it to CHAR.  Same happens with the inner dual query.  Which
according to the blank-padding comparison rules in that manual's
paragraph cause it to behave like you saw.  What I did with my SQL
example was to force Oracle to use my explicit conversion rather than
an implicit one.  And it then behaved as you expected - return no rows
- and once again according to the blank-padded/non-padded comparison
rules.
Thing I've found with Oracle is to never assume a constant - be it in
a query or in a predicate - is a given type: always try to explicitly
CAST it.  It saves a heck of a lot of surprises with the implicit
conversions.  But once again: there are no absolutes, so take this
with the appropriate amount of common sense judgement.  ;)

On Feb 10, 8:06=A0pm, Noons <wizofo...@gmail.com> wrote:
> On Feb 11, 10:17=A0am, Geoff Muldoon <geoff.muld...@trap.gmail.com>
> wrote:
>
> > So it's not only is it not a "bug", and not only is it fully documented=
, =A0
> > Oracle's treatment of blank-padded CHAR types with trailing whitespace =
is
> > actually consistent with the ANSI standard.
>
> > The only remaining issue is where it might be documented that Oracle's
> > implicit casting of strings in WHERE clauses is to blank-padded rather
> > than nonpadded types, and whether this is configurable.
>
> I don't think there is a written rule anywhere for that but of course
> I may be wrong.
> This is what I think is happening: when the column involved in a
> comparison is a string constant padded with blanks (as opposed to a
> column in a table which has a defined type), Oracle implicitly
> converts it to CHAR. =A0Same happens with the inner dual query. =A0Which
> according to the blank-padding comparison rules in that manual's
> paragraph cause it to behave like you saw. =A0What I did with my SQL
> example was to force Oracle to use my explicit conversion rather than
> an implicit one. =A0And it then behaved as you expected - return no rows
> - and once again according to the blank-padded/non-padded comparison
> rules.
> Thing I've found with Oracle is to never assume a constant - be it in
> a query or in a predicate - is a given type: always try to explicitly
> CAST it. =A0It saves a heck of a lot of surprises with the implicit
> conversions. =A0But once again: there are no absolutes, so take this
> with the appropriate amount of common sense judgement. =A0;)

Noons

You are wrong. :)

This is what happens when you let script-kiddies create/design
database engines. The problems one has with the conversion of MySQL
CHAR to Oracle CHAR is the fact that the idiots at MySQL used VARCHAR
and CHAR interchangeably.   In 5.1  they added a my.cnf
"sqlmode_pad_char_to_full_length" variable to set it to use the ANSI
standard which is  CHAR is FIXED LENGTH and VARCHAR is VARIABLE length
which is the correct behavior of these data types.
see:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_pad_cha=
r_to_full_length.

I am the one that actually filed the bug against MySQL to get them to
fix their stupidity.  There is a reason the SQL standard going back to
it's inception used things called DATA TYPES.  MySQL developers did
not understand this.  They figured if you used a CHAR(10) field and
only put in 5 characters that you only wanted to get 5 characters back
- WRONG.  CHAR behavior has ALWAYS been to return N characters
regardless of data input (Except for MySQL) - example: CHAR(10) with a
value of 'HELLO' would actually return 'HELLO     '. Hello + 5
spaces.  MySQL would just return 'HELLO'.  My exchange in the bug
database was quite contentious. The reason for initially not
correcting the problem was that they (MySQL) "documented" the
behavior. Documented or not, a data type is a data type and if you do
something different violating a 30+year standard, it is still wrong.

The bigger issue you are going to have is that the application may pad
or remove spacing to compensate for MySQL's poor understanding of DATA
TYPES.  .

 onedbguru

On Feb 11, 4:01=A0pm, onedbguru <onedbg...@yahoo.com> wrote:
> On Feb 10, 8:06=A0pm, Noons <wizofo...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Feb 11, 10:17=A0am, Geoff Muldoon <geoff.muld...@trap.gmail.com>
> > wrote:
>
> > > So it's not only is it not a "bug", and not only is it fully document=
ed, =A0
> > > Oracle's treatment of blank-padded CHAR types with trailing whitespac=
e is
> > > actually consistent with the ANSI standard.
>
> > > The only remaining issue is where it might be documented that Oracle'=
s
> > > implicit casting of strings in WHERE clauses is to blank-padded rathe=
r
> > > than nonpadded types, and whether this is configurable.
>
> > I don't think there is a written rule anywhere for that but of course
> > I may be wrong.
> > This is what I think is happening: when the column involved in a
> > comparison is a string constant padded with blanks (as opposed to a
> > column in a table which has a defined type), Oracle implicitly
> > converts it to CHAR. =A0Same happens with the inner dual query. =A0Whic=
h
> > according to the blank-padding comparison rules in that manual's
> > paragraph cause it to behave like you saw. =A0What I did with my SQL
> > example was to force Oracle to use my explicit conversion rather than
> > an implicit one. =A0And it then behaved as you expected - return no row=
s
> > - and once again according to the blank-padded/non-padded comparison
> > rules.
> > Thing I've found with Oracle is to never assume a constant - be it in
> > a query or in a predicate - is a given type: always try to explicitly
> > CAST it. =A0It saves a heck of a lot of surprises with the implicit
> > conversions. =A0But once again: there are no absolutes, so take this
> > with the appropriate amount of common sense judgement. =A0;)
>
> Noons
>
> You are wrong. :)
>
> This is what happens when you let script-kiddies create/design
> database engines. The problems one has with the conversion of MySQL
> CHAR to Oracle CHAR is the fact that the idiots at MySQL used VARCHAR
> and CHAR interchangeably. =A0 In 5.1 =A0they added a my.cnf
> "sqlmode_pad_char_to_full_length" variable to set it to use the ANSI
> standard which is =A0CHAR is FIXED LENGTH and VARCHAR is VARIABLE length
> which is the correct behavior of these data types.
> see:http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_p=
.....
>
> I am the one that actually filed the bug against MySQL to get them to
> fix their stupidity. =A0There is a reason the SQL standard going back to
> it's inception used things called DATA TYPES. =A0MySQL developers did
> not understand this. =A0They figured if you used a CHAR(10) field and
> only put in 5 characters that you only wanted to get 5 characters back
> - WRONG. =A0CHAR behavior has ALWAYS been to return N characters
> regardless of data input (Except for MySQL) - example: CHAR(10) with a
> value of 'HELLO' would actually return 'HELLO =A0 =A0 '. Hello + 5
> spaces. =A0MySQL would just return 'HELLO'. =A0My exchange in the bug
> database was quite contentious. The reason for initially not
> correcting the problem was that they (MySQL) "documented" the
> behavior. Documented or not, a data type is a data type and if you do
> something different violating a 30+year standard, it is still wrong.
>
> The bigger issue you are going to have is that the application may pad
> or remove spacing to compensate for MySQL's poor understanding of DATA
> TYPES. =A0.
>
> =A0onedbguru

$@%@#%$@# my apologies - looks like I answered the wrong question...
Sort-of. (I saw the phrase "my SQL" with the discussion of CHAR/
VARCHAR and translated it MySQL and went on my rant...  again, my
apologies.

One labeling themselves as guru:

# $@%@#%$@# my apologies - looks like I answered the wrong question...
Sort-of.

Maybe reading the whole thread and looking at replies in context of
the original question is an option?

Of course I am just a groundhog which is not anything close to a
"guru" ...

On Feb 11, 6:01=A0pm, John Hurley <hurleyjo...@yahoo.com> wrote:
> One labeling themselves as guru:
>
> # $@%@#%$@# my apologies - looks like I answered the wrong question...
> Sort-of.
>
> Maybe reading the whole thread and looking at replies in context of
> the original question is an option?
>
> Of course I am just a groundhog which is not anything close to a
> "guru" ...

Thanks for the advice... :)   my speed reader needs some new brake
pads...

onedbguru wrote,on my timestamp of 12/02/2011 10:24 AM:
> On Feb 11, 6:01 pm, John Hurley<hurleyjo...@yahoo.com>  wrote:
>> One labeling themselves as guru:
>>
>> # $@%@#%$@# my apologies - looks like I answered the wrong question...
>> Sort-of.
>>
>> Maybe reading the whole thread and looking at replies in context of
>> the original question is an option?
>>
>> Of course I am just a groundhog which is not anything close to a
>> "guru" ...
>
> Thanks for the advice... :)   my speed reader needs some new brake
> pads...


No worries.  Only "architects"(aka "experts"), Java/j2ee duhvelopers and fusion 
marketeers are perfect and never wrong.
Common mortals are more than entitled to the occasional slip-up. I know that for 
a fact with me!
;)