upper() problem in 7.0.2 - Mailing list pgsql-bugs

Your name               : Christopher L. Cousins
Your email address      : chris@impulse.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@impulse.net>