Skip site navigation (1) Skip section navigation (2)

upper() problem in 7.0.2

From: "Christopher L(dot) Cousins" <chris(at)impulse(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: upper() problem in 7.0.2
Date: 2000-07-03 18:38:02
Message-ID: 20000703113802.A13916@cobalt.impulse.net (view raw or flat)
Thread:
Lists: pgsql-bugs
Your name               : Christopher L. Cousins
Your email address      : chris(at)impulse(dot)net


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : AMD K7 (Athlon) ("AuthenticAMD" 686-class) 705 MHz

  Operating System (example: Linux 2.0.26 ELF)  : OpenBSD atlantia 2.7 ATLANTIA#3 i386

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2

  Compiler used (example:  gcc 2.8.0)           : gcc version 2.95.2 19991024 (release)


Please enter a FULL description of your problem:
------------------------------------------------

Working on the following table:

tickets=# \d tbluser
          Table "tbluser"
 Attribute |    Type     | Modifier 
-----------+-------------+----------
 username  | varchar(16) | 
 gecos     | varchar(50) | 
 expdate   | timestamp   | 

There are (4523 rows) in this table.

Using PHP4 or PHP3, the following query will ~sometimes~ cause the backend to
terminate.  It depends the value of $searchstr and appears (to me anyway) to be
random.  A $searchstr that causes the backend to crash right now may work just
fine in a few hours. 

The same query issued through psql will not cause the backend to crash.

For both interfaces (psql and PHP4) the query logged (debug) is the same.

SELECT * FROM tblUser WHERE
upper(tblUser.username) LIKE upper('%$searchstr%')
OR 
upper(tblUser.gecos) LIKE upper('%$searchstr%');

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
If I change my query back to it's original form, one of our employees
will find a current value that causes the backend to crash (this is
part of a trouble ticket system).  Once they find the right string, I can
make the problem happen over and over by hitting reload on my web browser.


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

My fix was to use the builtin strtoupper() function in PHP4 to make $searchstr
uppercase before issueing the query to postgres.  The following query has been
working for the last couple of days.

SELECT * FROM tblUser WHERE
upper(tblUser.username) LIKE '%$searchstr%'
OR 
upper(tblUser.gecos) LIKE '%$searchstr%';

-- 

--Chris

                              ____ 
Impulse Internet Services    /    \
____________________________/      \_____

http://www.impulse.net <chris(at)impulse(dot)net>

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2000-07-03 19:24:22
Subject: Re: \d* won't work in 7.0.2?
Previous:From: Lamar OwenDate: 2000-07-03 15:59:30
Subject: Re: \d* won't work in 7.0.2?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group