Re: Oracle SQL bug in 9.2.0.8
From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Fri, 11 Feb 2011 09:39:41 +1100
Message-ID: <MPG.27bf143bf2c40a13989869_at_news.x-privat.org>
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.
Date: Fri, 11 Feb 2011 09:39:41 +1100
Message-ID: <MPG.27bf143bf2c40a13989869_at_news.x-privat.org>
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 Received on Thu Feb 10 2011 - 16:39:41 CST