Oracle SQL bug in 9.2.0.8

#1

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

#2

On Feb 10, 11:06*am, DG problem <skatef... (AT) gmail (DOT) com> wrote:

Quote:

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/B...002.htm#i55214

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

#3

On Feb 10, 10:34*am, Noons <wizofo... (AT) gmail (DOT) com> wrote:

Quote:

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

#4

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.

#5

"DG problem" <skatefree (AT) gmail (DOT) com> a écrit dans le message de news:
2cd5fcc9-694f-4a81-bbc4-29eaf9eaf9d5...oglegroups.com...

Quote:

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/...90034684447 6

Regards
Michel

#6

In article <7e05e7cd-e4ca-4f63-bf56-4f8175ac0245
@u24g2000prn.googlegroups.com>, DG problem says...

Quote:

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

#7

DG problem wrote,on my timestamp of 10/02/2011 12:04 PM:

Quote:

On Feb 10, 10:34 am, Noons<wizofo... (AT) gmail (DOT) 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.

#8

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

Quote:

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.

#9

On Feb 10, 12:25*am, Noons <wizofo... (AT) yahoo (DOT) com.au> wrote:

Quote:

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.

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/2...u-sue-age-dis/

#10

Noons says...

Quote:

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