Thread: BUG #5877: problem with wild char used in where clause

The following bug has been logged online:

Bug reference:      5877
Logged by:          Savita
Email address:      savita.halli@gmail.com
PostgreSQL version: 8.3.5
Operating system:   Solaris
Description:        problem with wild char used in where clause
Details:

I have a table with id as primary key.
"slect id from table" gives me following data
      id
--------------
 AB1
 AB2
 AB3
 AB4
(4 rows)

Now when I need ids which starts from A
select id from table where id like 'A%'; Gives the follwoing result set.

      id
--------------
 AB1
 AB2
 AB3
 AB4
(4 rows)

When I ran query to get the ids which ends with B1
select id from table where id like '%B1'
does not return me any rows. Should it be not returning row with id AB1? Is
there known issue?

Thanks in advance
Savita

"Savita" <savita.halli@gmail.com> wrote:

> PostgreSQL version: 8.3.5

> I have a table with id as primary key.

> When I ran query to get the ids which ends with B1
> select id from table where id like '%B1'
> does not return me any rows.

You might want to check all the bug fixes listed in the 8.3 bug fix
releases since the 2008 version you're running:

http://www.postgresql.org/docs/8.3/static/release.html

Then consider an in-place upgrade:

http://www.postgresql.org/support/versioning

and see if the problem is still there.  It doesn't exist on the more
current releases I'm running.  For example:

test=# create table t (id text not null primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
test=# insert into t values ('AB1'),('AB2'),('AB3'),('AB4');
INSERT 0 4
test=# select id from t;
 id
-----
 AB1
 AB2
 AB3
 AB4
(4 rows)

test=# select id from t where id like 'A%';
 id
-----
 AB1
 AB2
 AB3
 AB4
(4 rows)

test=# select id from t where id like '%B1';
 id
-----
 AB1
(1 row)

If you apply the available 8.3 bug fixes and still see the problem,
creating a self-contained test case like the above will allow others
to better help you.

-Kevin

On Thu, Feb 10, 2011 at 11:02:40AM +0000, Savita wrote:
> When I ran query to get the ids which ends with B1
> select id from table where id like '%B1'
> does not return me any rows. Should it be not returning row with id AB1? Is
> there known issue?

is this by any chance char(x) datatype?

if yes - please read:
http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

"Savita" <savita.halli@gmail.com> wrote:

> When I ran query to get the ids which ends with B1
> select id from table where id like '%B1'
> does not return me any rows. Should it be not returning row with
> id AB1? Is there known issue?

Oh, I did have one more thought after I posted -- what is the data
type of the id column.  If it is character(n), also specified as
char(n), you would need to include trailing spaces or a wildcard at
the end of your string.  For all purposes, including pattern
matching, char(n) values are considered to be padded with space
characters to their declared length.  So if the id was char(4) the
'%B1' would be considered a match to AAB1 but not AB1.

Use of char(n) columns is generally discouraged because the
semantics required by the standard can be rather surprising.
Consider varchar(n) or text instead.

-Kevin