Horton

Thread

  1. lower() broken?

    Vince Vielhaber <vev@michvhf.com> — 1999-02-19T18:31:51Z

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

  2. Re: [HACKERS] lower() broken?

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-02-20T02:45:07Z

    > 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

  3. Re: [HACKERS] lower() broken?

    Vince Vielhaber <vev@michvhf.com> — 1999-02-20T03:43:57Z

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

  4. Re: [HACKERS] lower() broken?

    Roland Roberts <roberts@panix.com> — 1999-02-20T04:50:07Z

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

  5. Re: [HACKERS] lower() broken?

    Vince Vielhaber <vev@michvhf.com> — 1999-02-20T05:02:08Z

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

  6. Re: [HACKERS] lower() broken?

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-02-20T06:17:05Z

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

  7. Re: [HACKERS] lower() broken?

    jose' soares <sferac@bo.nettuno.it> — 1999-02-22T13:44:34Z

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