Re: Oracle SQL bug in 9.2.0.8
Date: Fri, 11 Feb 2011 10:17:13 +1100
Message-ID: <MPG.27bf1d08dedcb90898986a_at_news.x-privat.org>
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:
- 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 implementationdependent 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 Received on Thu Feb 10 2011 - 17:17:13 CST