Thread: BUG #5438: Bug/quirk in ascii() function

The following bug has been logged online:

Bug reference:      5438
Logged by:          Christoph Zwerschke
Email address:      cito@online.de
PostgreSQL version: 8.0-8.4
Operating system:   all
Description:        Bug/quirk in ascii() function
Details:

As you would expect,

   ascii(cast(' ' as char(1)))

yields 32 in Oracle, SQL Server and MySQL.

However, in PostgreSQL 8.0 to 8.4,

   ascii(cast(' ' as char(1))),
   ascii(cast(' ' as char))

both give 0. I can imagine why this happens (after all, ' ' is the same as
'' when casted to char), but it is not obvious and what you expect, and
different from the behavior of the other database engines.

With variable length strings, it works as expected,

   ascii(' '),
   ascii(cast(' ' as varchar(1))),
   ascii(cast(' ' as varchar))

all give 32.

I think this quirk should be fixed or at least mentioned in the
documentation of ascii().

"Christoph Zwerschke" <cito@online.de> wrote:

>    ascii(cast(' ' as char(1))),
>    ascii(cast(' ' as char))
> both give 0.
>
> I think this quirk should be fixed or at least mentioned in the
> documentation of ascii().

The problem is not in ascii(), but in casting from char to text.
We have only one version of ascii() in default; ascii(text).
So, if you use ascii( ' '::char(1) ), it is actually handled as
ascii( ' '::char(1)::text ). Traling spaces were removed during
the cast to text. You could have the same result with other databases
if you define a char version of ascii().

=# CREATE FUNCTION ascii(bpchar) RETURNS integer AS 'ascii' LANGUAGE internal;
=# SELECT ascii(cast(' ' as char(1)));
 ascii
-------
    32
(1 row)

Do you know how the SQL standard mention the behavior? IMHO, postgres'
behavior is more reasonable because length(' '::char(1)) is 0.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Am 26.04.2010 12:11, schrieb Takahiro Itagaki:
> The problem is not in ascii(), but in casting from char to text.
> We have only one version of ascii() in default; ascii(text).
> So, if you use ascii( ' '::char(1) ), it is actually handled as
> ascii( ' '::char(1)::text ). Traling spaces were removed during
> the cast to text.

Ok, that makes sense.

> Do you know how the SQL standard mention the behavior? IMHO, postgres'
> behavior is more reasonable because length(' '::char(1)) is 0.

Just found http://troels.arvin.dk/db/rdbms/ which claims that this is
against the standard:

   "PostgreSQL: Stores CHARs in space padded form, but violates
    the standard by (conceptually) truncating trailing white-space
    before performing most functions, operators, and comparisons
    (like the CHARACTER_LENGTH-function and the concatenation(||)
    operator)."

Not sure if this is correct and how well-defined the SQL standard
actually is in this regard. It seems Oracle does not remove trailing
spaces when converting from char to varchar.

-- Christoph

Christoph Zwerschke <cito@online.de> wrote:
> Am 26.04.2010 12:11, schrieb Takahiro Itagaki:

>> Do you know how the SQL standard mention the behavior? IMHO,
>> postgres' behavior is more reasonable because
>> length(' '::char(1)) is 0.
>
> Just found http://troels.arvin.dk/db/rdbms/ which claims that this
> is against the standard:
>
>    "PostgreSQL: Stores CHARs in space padded form, but violates
>     the standard by (conceptually) truncating trailing white-space
>     before performing most functions, operators, and comparisons
>     (like the CHARACTER_LENGTH-function and the concatenation(||)
>     operator)."
>
> Not sure if this is correct and how well-defined the SQL standard
> actually is in this regard. It seems Oracle does not remove
> trailing spaces when converting from char to varchar.

That is consistent with how I remember the standard (although I
don't have time right now to fight my way through it to confirm).
My recollection is that char(n) should be treated exactly like a
varchar padded with spaces to n characters *except* for character
string comparisons, where trailing spaces are supposed to be
ignored.

-Kevin