Обсуждение: 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.
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.
--
==========================================================================
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
==========================================================================
> 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' -