Обсуждение: Strange behaviour ?

Поиск
Список
Период
Сортировка

Strange behaviour ?

От
Daniele Orlandi
Дата:
tacacs=> CREATE TABLE bugdb
tacacs-> (
tacacs->  id       int4 default nextval('bugdb_id_seq') primary key,
tacacs->  email    varchar(50) NOT NULL,
tacacs->  sdescr   varchar(80),
tacacs->  ldescr   text,
tacacs->  version  char(16),
tacacs->  status   char(16),
tacacs->  comments text,
tacacs->  ctime    datetime,
tacacs->  mtime    datetime
tacacs-> );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index bugdb_pkey for
table bugdb
CREATE

tacacs=> insert into bugdb values ( 1,'daniele@orlandi.com','Test
BUG','TestText','3.0.0','open' );
INSERT 2725845 1

tacacs=> SELECT id,status,version,email,sdescr FROM bugdb WHERE status = 'open'
ORDER BY id;
id|status          |         version|email              |sdescr
--+----------------+----------------+-------------------+--------
 1|open            |3.0.0           |daniele@orlandi.com|Test BUG
(1 row)

tacacs=> SELECT id,status,version,email,sdescr FROM bugdb WHERE lower(status) =
'open' ORDER BY id;
id|status|version|email|sdescr
--+------+-------+-----+------
(0 rows)

tacacs=> SELECT id,status,version,email,sdescr FROM bugdb WHERE lower(status) =
'open            ' ORDER BY id;
id|status          |         version|email              |sdescr
--+----------------+----------------+-------------------+--------
 1|open            |3.0.0           |daniele@orlandi.com|Test BUG
(1 row)

As you may see, LOWER(status) matches only 'open            ', not 'open'.

Is it the correct behaviour of LOWER() when applied to fixed size fields ?

Regards.

--
 Daniele

-------------------------------------------------------------------------------
"I'd crawl over an acre of 'Visual This++' and 'Integrated Development
That' to get to gcc, Emacs, and gdb.  Thank you."
(By Vance Petree, Virginia Power)
-------------------------------------------------------------------------------
 Se telecom italia aggiungesse uno scatto al giorno ad ogni abbonato, dal
 primo Gennaio avrebbe rubato 668.954.000.000 Lire.
-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) -

Re: [GENERAL] Strange behaviour ?

От
James Boorn
Дата:
look up the differences between varchar and char data types.

On Thu, 30 Jul 1998, Daniele Orlandi wrote:

> tacacs=> CREATE TABLE bugdb
> tacacs-> (
> tacacs->  id       int4 default nextval('bugdb_id_seq') primary key,
> tacacs->  email    varchar(50) NOT NULL,
> tacacs->  sdescr   varchar(80),
> tacacs->  ldescr   text,
> tacacs->  version  char(16),
> tacacs->  status   char(16),
> tacacs->  comments text,
> tacacs->  ctime    datetime,
> tacacs->  mtime    datetime
> tacacs-> );
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index bugdb_pkey for
> table bugdb
> CREATE
>
> tacacs=> insert into bugdb values ( 1,'daniele@orlandi.com','Test
> BUG','TestText','3.0.0','open' );
> INSERT 2725845 1
>
> tacacs=> SELECT id,status,version,email,sdescr FROM bugdb WHERE status = 'open'
> ORDER BY id;
> id|status          |         version|email              |sdescr
> --+----------------+----------------+-------------------+--------
>  1|open            |3.0.0           |daniele@orlandi.com|Test BUG
> (1 row)
>
> tacacs=> SELECT id,status,version,email,sdescr FROM bugdb WHERE lower(status) =
> 'open' ORDER BY id;
> id|status|version|email|sdescr
> --+------+-------+-----+------
> (0 rows)
>
> tacacs=> SELECT id,status,version,email,sdescr FROM bugdb WHERE lower(status) =
> 'open            ' ORDER BY id;
> id|status          |         version|email              |sdescr
> --+----------------+----------------+-------------------+--------
>  1|open            |3.0.0           |daniele@orlandi.com|Test BUG
> (1 row)
>
> As you may see, LOWER(status) matches only 'open            ', not 'open'.
>
> Is it the correct behaviour of LOWER() when applied to fixed size fields ?
>
> Regards.
>
> --
>  Daniele
>
> -------------------------------------------------------------------------------
> "I'd crawl over an acre of 'Visual This++' and 'Integrated Development
> That' to get to gcc, Emacs, and gdb.  Thank you."
> (By Vance Petree, Virginia Power)
> -------------------------------------------------------------------------------
>  Se telecom italia aggiungesse uno scatto al giorno ad ogni abbonato, dal
>  primo Gennaio avrebbe rubato 668.954.000.000 Lire.
> -------------------------------------------------------------------------------
>  Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
>  Via Mezzera 29/A - 20030 - Seveso (MI) -
>

/************************************************************
James Boorn
boorn@acsys.com
---------------------------------------------------------
"All right, brain, I don't like you and you don't like me -
    so let's just do this and I'll get back to killing you with beer."
-- Homer Simpson
************************************************************/

Re: [GENERAL] Strange behaviour ?

От
Daniele Orlandi
Дата:
James Boorn wrote:
>
> look up the differences between varchar and char data types.

I know the difference between varchar and char, but I don't see why LOWER() is
adding spaces. If this is the correct behaviour, could you kindly point me to
the related docs ?

Thanks.

Bye!

--
 Daniele

-------------------------------------------------------------------------------
"On the Internet, no one knows you're using Windows NT"
(Submitted by Ramiro Estrugo, restrugo@fateware.com)
-------------------------------------------------------------------------------
 Se telecom italia aggiungesse uno scatto al giorno ad ogni abbonato, dal
 primo Gennaio avrebbe rubato 669.032.000.000 Lire.
-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------