Re: Oracle SQL bug in 9.2.0.8

From: onedbguru <onedbguru_at_yahoo.com>
Date: Fri, 11 Feb 2011 13:49:15 -0800 (PST)
Message-ID: <3b1ccf5f-07d3-4f76-b54c-3f5cbb81f363_at_z31g2000vbs.googlegroups.com>



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.  ;)
>
> 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_p....
>
> 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

$_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

Original text of this message