lower() broken?

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.
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()?

Not sure. You *did* forget to tell us what data type is used for column
"username".

- Tom

On Sat, 20 Feb 1999, Thomas G. Lockhart wrote:

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.
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()?

Not sure. You *did* forget to tell us what data type is used for column
"username".

Oops! Yeah, I guess lower wouldn't work so well if it was a numeric
field. Anyway, username is a char(8).

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

-----BEGIN PGP SIGNED MESSAGE-----

"Vince" == Vince Vielhaber <vev@michvhf.com> writes:

Vince> Oops! Yeah, I guess lower wouldn't work so well if it was
Vince> a numeric field. Anyway, username is a char(8).

It won't be equal to 'joblo', it will be equal to 'joblo '. You may
want to consider using varchar(8).

roland
- --
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD Custom Software Solutions
roberts@panix.com 101 West 15th St #4NN
rbroberts@acm.org New York, NY 10011

-----BEGIN PGP SIGNATURE-----
Version: 2.6.2
Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface

iQCVAwUBNs4+/eoW38lmvDvNAQHyYwQAm5l6iiHIzHmpZ+9hYUe+FX81TeKLG7Tm
koqbU3zxCVHVRcWID7PH7EjnHhPYga19ctNyE8Y0nVsKpzc9DadACfBdYexUy+Qc
TdS9WiDzFyO0eOg4BrjV67ZWBtTwIxOYng9NSZHlUOgNx9HLggmIH0Tnfl2vyU8H
EAaq/zlq6c8=
=Lizm
-----END PGP SIGNATURE-----

On 19 Feb 1999, Roland Roberts wrote:

-----BEGIN PGP SIGNED MESSAGE-----

"Vince" == Vince Vielhaber <vev@michvhf.com> writes:

Vince> Oops! Yeah, I guess lower wouldn't work so well if it was
Vince> a numeric field. Anyway, username is a char(8).

It won't be equal to 'joblo', it will be equal to 'joblo '. You may
want to consider using varchar(8).

Damn. That's the one thing that never even occurred to me!

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Vince> Oops! Yeah, I guess lower wouldn't work so well if it was
Vince> a numeric field. Anyway, username is a char(8).
It won't be equal to 'joblo', it will be equal to 'joblo '. You
may want to consider using varchar(8).

Damn. That's the one thing that never even occurred to me!

I don't remember what my old Ingres system did for comparisons of char
against other string types; does every system (or the SQL standard)
consider the trailing blanks significant, or should they be implicitly
ignored in comparisons?

btw, if you don't want to redefine the column, then try

where trim(trailing from username) = lower('joblo');

but that will be a slower query since "username" must be trimmed before
comparison.

- Tom

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' -