LIKE erratic? or unseen DB corruption?

Is it possible that there are spaces after the 'IDC16W' in the field? Try:

LIKE 'IDC16W%'

and see if that makes a difference.

len

A direct query gets appropriate rows of data:

dbname=# select * from partdef where shpname = 'IDC16W';
pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt |

shpname | value | descrip

-------+-------+------+----+-----+--------+---------+----------+-----------

-------+------------+---------

17 | 328 | 08X2 | 0 | 0 | | | 1 | IDC16W

| Header-8x2 |

11 | 323 | 08X2 | 0 | 0 | | | 1 | IDC16W

| Header-8x2 |

...while the very same query (substituting LIKE for the '=' sign) gets

nothing!?

dbname=# select * from partdef where shpname LIKE 'IDC16W';
pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt | shpname |

value | descrip

-------+-------+-----+----+-----+--------+---------+----------+---------+--

-----+---------

Frank Miles <fpm@u.washington.edu> writes:

A direct query gets appropriate rows of data:
dbname=# select * from partdef where shpname = 'IDC16W';
...while the very same query (substituting LIKE for the '=' sign) gets nothing!?

Hm. Does EXPLAIN show the same kind of plan (index or seq scan) for
both queries? If not, does forcing the plan choice via ENABLE_xxxSCAN
make a difference? Do you have locale support turned on, and if so
what locale are you using?

regards, tom lane

From: "Frank Miles" <fpm@u.washington.edu>

A direct query gets appropriate rows of data:

dbname=# select * from partdef where shpname = 'IDC16W';
pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt |

shpname | value | descrip

-------+-------+------+----+-----+--------+---------+----------+----------

--------+------------+---------

17 | 328 | 08X2 | 0 | 0 | | | 1 | IDC16W

| Header-8x2 |

11 | 323 | 08X2 | 0 | 0 | | | 1 | IDC16W

| Header-8x2 |

...while the very same query (substituting LIKE for the '=' sign) gets

nothing!?

Is "shpname" char() rather than varchar() or text? If so I think = strips
spaces from the end and LIKE doesn't.

Try a LIKE 'IDC16W%' and see if that works, or try padding the match with
spaces. Then, I'd recommend replacing any char() with varchar() - not
noticably slower (IMHO) and a lot less irritating.

- Richard Huxton

A direct query gets appropriate rows of data:

dbname=# select * from partdef where shpname = 'IDC16W';

...while the very same query (substituting LIKE for the '=' sign) gets

nothing!?

dbname=# select * from partdef where shpname LIKE 'IDC16W';

Can someone please tell me the really stupid thing that I'm doing wrong?

Just a guess here... is shpname a CHAR field (which would be padded with
spaces)? If so you'd have to do LIKE 'IDC16W%'

Greg

On Mon, 21 May 2001, Len Morgan wrote:

Is it possible that there are spaces after the 'IDC16W' in the field? Try:

LIKE 'IDC16W%'

and see if that makes a difference.

len

Indeed it does allow 'LIKE' to find the item -- this item has
the type char(16). Thanks!

-frank

On Mon, 21 May 2001, Tom Lane wrote:

Frank Miles <fpm@u.washington.edu> writes:

A direct query gets appropriate rows of data:
dbname=# select * from partdef where shpname = 'IDC16W';
...while the very same query (substituting LIKE for the '=' sign) gets nothing!?

Hm. Does EXPLAIN show the same kind of plan (index or seq scan) for
both queries? If not, does forcing the plan choice via ENABLE_xxxSCAN
make a difference? Do you have locale support turned on, and if so
what locale are you using?

regards, tom lane

Seq scan for '=' and for 'LIKE'; no locale support enabling. As Len
Morgan suggested, it appears to be a matter of LIKE being sensitive to
trailing spaces, and '=' NOT being sensitive to them. The field data type
is char(16) {not stated in my original message}.

Is "LIKE" deprecated for testing when a trailing '%' isn't used (e.g. wx%yz)?
Regexp is certainly a possible alternative, especially given the seq scan.
Though I have to say it seems weird that '=' matches, and 'LIKE' doesn't.

Thanks for your help!

-frank

Frank Miles <fpm@u.washington.edu> writes:

Seq scan for '=' and for 'LIKE'; no locale support enabling. As Len
Morgan suggested, it appears to be a matter of LIKE being sensitive to
trailing spaces, and '=' NOT being sensitive to them. The field data type
is char(16) {not stated in my original message}.

Bingo. '=' for char(N) fields is not sensitive to trailing spaces, per
SQL specs. But LIKE does not have a variant for char(N), it's purely
a "text" operation; so it thinks spaces are significant.

As near as I can tell, the SQL spec does not have any provision that
requires ignoring trailing blanks in a LIKE comparison on char(N),
which seems rather an oversight on their part.

I tend to think that char(N) is evil and should be avoided in favor of
varchar or text. Those trailing spaces are just too likely to cause
confusion; and when do they buy you anything?

regards, tom lane

Frank Miles writes:

Running 7.1(.0) on a PC running Debian Linux (potato), I have a strange situation in psql:

A direct query gets appropriate rows of data:

dbname=# select * from partdef where shpname = 'IDC16W';
pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt | shpname | value | descrip
-------+-------+------+----+-----+--------+---------+----------+------------------+------------+---------
17 | 328 | 08X2 | 0 | 0 | | | 1 | IDC16W | Header-8x2 |
11 | 323 | 08X2 | 0 | 0 | | | 1 | IDC16W | Header-8x2 |

...while the very same query (substituting LIKE for the '=' sign) gets nothing!?

dbname=# select * from partdef where shpname LIKE 'IDC16W';
pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt | shpname | value | descrip
-------+-------+-----+----+-----+--------+---------+----------+---------+-------+---------
(0 rows)

This is supposed to work. What data type is column "shpname"? Did you
configure with locale, and if so, what locale are you running under? Is
there an index on "shpname"?

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

a few months back i had a similar problem.. The query ran my query
properly when i didnt give any group by constraints but failed to give
right grouping results when i used a group by claus..
I deleted the entire set of data and reloaded it to drive it away,,
Hope it helps
Anand