Thread: AW: AW: Truncation of char, varchar types

AW: AW: Truncation of char, varchar types

From
Zeugswetter Andreas SB
Date:
> > > Excessively long values are currently silently truncated when they are
> > > inserted into char or varchar fields.  This makes the entire notion of
> > > specifying a length limit for these types kind of useless, IMO.  Needless
> > > to say, it's also not in compliance with SQL.
> >
> > To quote Tom "paragraph and verse please" :-)
> 
> SQL 1992, 9.2 GR 3 e)
> 
> """
> If the data type of T is variable-length character string and
> the length in characters M of V is greater than the maximum
> length in characters L of T, then,
> 
> Case:
> 
>  i) If the rightmost M-L characters of V are all <space>s, then
>     the value of T is set to the first L characters of V and
>     the length in characters of T is set to L.
> 
> ii) If one or more of the rightmost M-L characters of V are
>     not <space>s, then an exception condition is raised: data
>                           ^^^^^^^^^
>     exception-string data, right truncation.
> """

Thank you. Is an "exception condition" necessarily an error, or 
is a warning also an exception condition ?

> Similarly in SQL 1999 and for other data types.
> 
> > > How do people feel about changing this to raise an error in this
> > > situation?
> >
> > Can't do.
> 
> Why not?

Because other db's only raise a warning. Of course we don't want to
copy that behavior if they are not conformant. See above question.

Andreas


Re: AW: AW: Truncation of char, varchar types

From
Peter Eisentraut
Date:
Zeugswetter Andreas SB writes:

> Thank you. Is an "exception condition" necessarily an error, or
> is a warning also an exception condition ?

A warning/notice is called a "completion condition".

> Because other db's only raise a warning. Of course we don't want to
> copy that behavior if they are not conformant. See above question.

Someone said Oracle raises an error.  Informix seems to be the only other
db that truncates silently.  I think Oracle wins here...

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/