Re: [HACKERS] lower() broken? - Mailing list pgsql-hackers

From jose' soares
Subject Re: [HACKERS] lower() broken?
Date
Msg-id 36D15F42.A75109C7@bo.nettuno.it
Whole thread Raw
In response to lower() broken?  (Vince Vielhaber <vev@michvhf.com>)
List pgsql-hackers

Vince Vielhaber ha scritto:

> I have a record in table cust with the username of  joblo  and it's
> already lower case.  This is from a cvsup a couple of weeks old.
>
> classifieds=> select count(*) from cust where username = lower('joblo');
> count
> -----
>     0
> (1 row)
>
> classifieds=> select count(*) from cust where username = 'joblo';
> count
> -----
>     1
> (1 row)
>
> Doesn't seem to matter if I use lower on username, 'joblo' or both.  And
> there's only the one record in the table.
>
> Did something break or did I forget how to use lower()?
>
> Vince.

I suppose you defined username as char() like...

prova=> create table test(username char(10));
CREATE
prova=> insert into test values ('joblo');
INSERT 207732 1
prova=> select count(*) from test where username = lower('joblo');
count
-----   0
(1 row)


prova=> select count(*) from test where trim(username) = lower('joblo');
count
-----   1
(1 row)

prova=> select count(*) from test where username = 'joblo';
count
-----   1
(1 row)

prova=> select count(*) from test where username = lower('joblo     ');
count
-----   1
(1 row)

The lower function "trims" the trailling spaces, this is why comparison fails.

because 'joblo     '  !=  'joblo'

I think this is a bug.
- Jose' -




pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] Re: Max backend limits cleaned up
Next
From: Michael Davis
Date:
Subject: RE: [HACKERS] Re: Max backend limits cleaned up