Re: Oracle SQL bug in 9.2.0.8
Date: Fri, 11 Feb 2011 13:01:58 -0800 (PST)
Message-ID: <305b1af7-24d3-45d9-a558-145abfe45233_at_w7g2000pre.googlegroups.com>
On Feb 10, 8:06 pm, Noons <wizofo..._at_gmail.com> wrote:
> 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. ;)
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/en/server-sql-mode.html#sqlmode_pad_char_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 Received on Fri Feb 11 2011 - 15:01:58 CST