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