Geoff Muldoon says...
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:

a) 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 implementation-
dependent 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

On Feb 11, 10:17*am, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:

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.

On Feb 10, 8:06*pm, Noons <wizofo... (AT) gmail (DOT) com> wrote:
Noons

You are wrong.

This is what happens when you let script-kiddies create/design
database engines. The problems one has with the conversion of MySQL
CHAR to Oracle CHAR is the fact that the idiots at MySQL used VARCHAR
and CHAR interchangeably. In 5.1 they added a my.cnf
"sqlmode_pad_char_to_full_length" variable to set it to use the ANSI
standard which is CHAR is FIXED LENGTH and VARCHAR is VARIABLE length
which is the correct behavior of these data types.
see:
http://dev.mysql.com/doc/refman/5.1/...to_full_length.

I am the one that actually filed the bug against MySQL to get them to
fix their stupidity. There is a reason the SQL standard going back to
it's inception used things called DATA TYPES. MySQL developers did
not understand this. They figured if you used a CHAR(10) field and
only put in 5 characters that you only wanted to get 5 characters back
- WRONG. CHAR behavior has ALWAYS been to return N characters
regardless of data input (Except for MySQL) - example: CHAR(10) with a
value of 'HELLO' would actually return 'HELLO '. Hello + 5
spaces. MySQL would just return 'HELLO'. My exchange in the bug
database was quite contentious. The reason for initially not
correcting the problem was that they (MySQL) "documented" the
behavior. Documented or not, a data type is a data type and if you do
something different violating a 30+year standard, it is still wrong.

The bigger issue you are going to have is that the application may pad
or remove spacing to compensate for MySQL's poor understanding of DATA
TYPES. .

onedbguru

On Feb 11, 4:01*pm, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote:
$@%@#%$@# my apologies - looks like I answered the wrong question...
Sort-of. (I saw the phrase "my SQL" with the discussion of CHAR/
VARCHAR and translated it MySQL and went on my rant... again, my
apologies.

One labeling themselves as guru:

# $@%@#%$@# my apologies - looks like I answered the wrong question...
Sort-of.

Maybe reading the whole thread and looking at replies in context of
the original question is an option?

Of course I am just a groundhog which is not anything close to a
"guru" ...

On Feb 11, 6:01*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
Thanks for the advice... my speed reader needs some new brake
pads...

onedbguru wrote,on my timestamp of 12/02/2011 10:24 AM:

No worries. Only "architects"(aka "experts"), Java/j2ee duhvelopers and fusion
marketeers are perfect and never wrong.
Common mortals are more than entitled to the occasional slip-up. I know that for
a fact with me!