Re: Oracle SQL bug in 220.127.116.11
Date: Fri, 11 Feb 2011 13:49:15 -0800 (PST)
On Feb 11, 4:01 pm, onedbguru <onedbg..._at_yahoo.com> wrote:
> 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. ;)
> 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.
> 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. .
$_at_%@#%$@# 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. Received on Fri Feb 11 2011 - 15:49:15 CST