"select ... where field like lower('%text%')" fails - Mailing list pgsql-bugs

From pgsql-bugs@postgresql.org
Subject "select ... where field like lower('%text%')" fails
Date
Msg-id 200103061134.f26BY0Y52994@hub.org
Whole thread Raw
List pgsql-bugs
Sean Kelly (lists@shortestpath.org) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
"select ... where field like lower('%text%')" fails

Long Description
I am trying to search a varchar(x) field with a query like "select ... where field like lower('%someText%')".

In one field, if the value someText is at the very start then the search fails.  In another field, if the value
someTextis at the very start then the search succeeds. 

[See code - note results where 0 rows are returned]



Sample Code
isp=> \d user_tbl
                 Table "user_tbl"
 Attribute |    Type     |        Modifier
-----------+-------------+-------------------------
 username  | varchar(10) | not null
 company   | varchar(80) | not null
 email     | varchar(80) |
 password  | varchar(20) | not null
 active    | boolean     | not null default 'TRUE'
 created   | timestamp   | not null
Index: user_tbl_pkey


isp=> SELECT username,company,active,created from user_tbl where username = 'sean';
 username |       company       | active |        created
----------+---------------------+--------+------------------------
 sean     | Sean's Test Company | t      | 2001-01-14 14:01:58+00
(1 row)


isp=> SELECT username,company,active,created from user_tbl where username like lower('%SEaN%');
 username |       company       | active |        created
----------+---------------------+--------+------------------------
 sean     | Sean's Test Company | t      | 2001-01-14 14:01:58+00
(1 row)


isp=> SELECT username,company,active,created from user_tbl where company like lower('%SEaN%');
 username | company | active | created
----------+---------+--------+---------
(0 rows)


isp=> SELECT username,company,active,created from user_tbl where company like lower('SEaN%');
 username | company | active | created
----------+---------+--------+---------
(0 rows)


isp=> SELECT username,company,active,created from user_tbl where company like lower('%EaN%');
 username |       company       | active |        created
----------+---------------------+--------+------------------------
 sean     | Sean's Test Company | t      | 2001-01-14 14:01:58+00
(1 row)


No file was uploaded with this report

pgsql-bugs by date:

Previous
From: Justin Clift
Date:
Subject: Irony bug
Next
From: pgsql-bugs@postgresql.org
Date:
Subject: "select ... where field like lower('%text%')" fails