Re: Oracle SQL bug in 9.2.0.8
Date: Thu, 10 Feb 2011 17:06:17 -0800 (PST)
Message-ID: <a0454351-4c82-40f9-92db-b56a9d768f65_at_y12g2000prf.googlegroups.com>
On Feb 11, 10:17 am, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
> 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.
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. ;)
Received on Thu Feb 10 2011 - 19:06:17 CST