Re: Oracle SQL bug in 126.96.36.199
Date: Thu, 10 Feb 2011 17:06:17 -0800 (PST)
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