Thread: LIKE erratic? or unseen DB corruption?
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)
Creating a new table, and populating it with trial values shows no problem --
it all works as expected (both '=' and 'LIKE' returning the same results).
If selects are done on other columns (same table) they seem to work correctly,
whether one or more rows are returned.
Can someone please tell me the really stupid thing that I'm doing wrong?
Thanks....
-frank
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 >-------+-------+-----+----+-----+--------+---------+----------+---------+-- -----+--------- >(0 rows) >
> 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
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
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
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 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
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
> >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)
> >
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 On Mon, May 21, 2001 at 08:12:01AM -0700, Frank Miles wrote: >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) > >Creating a new table, and populating it with trial values shows no problem -- >it all works as expected (both '=' and 'LIKE' returning the same results). >If selects are done on other columns (same table) they seem to work correctly, >whether one or more rows are returned. > >Can someone please tell me the really stupid thing that I'm doing wrong? >Thanks.... > > -frank > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly