Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.1) with ESMTP id RAA80398 for ; Mon, 26 Apr 1999 17:25:00 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id RAA04452; Mon, 26 Apr 1999 17:23:54 -0400 (EDT) To: Christophe Labouisse cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Strange behavior In-reply-to: Your message of 26 Apr 1999 21:37:46 +0200 Date: Mon, 26 Apr 1999 17:23:54 -0400 Message-ID: <4450.925161834@sss.pgh.pa.us> From: Tom Lane Christophe Labouisse writes: > I tried to make a simple select on a single table and I find some > strange results. > select crit_url from crit where crit_url like 'films/%' order by crit_url; > [ gives wrong results, whereas this gives right results: ] > select crit_url from crit where crit_url like '%films/%' order by crit_url; What locale and/or MULTIBYTE setting are you using? There is a hack in the parser that tries to transform the first of these into this: select crit_url from crit where crit_url like 'films/%' AND crit_url >= 'films/' AND crit_url <= 'films/\377' order by crit_url; in order to make it possible to use an index to restrict the scan. (Since the parser doesn't know whether any indexes are available, it does this whether there is an index or not. But it doesn't do it for patterns that don't require a specific match at the left.) It occurs to me, however, that '\377' may not be a valid character in all the character sets we support, and may not sort above all other characters even if it is valid. That could result in the <= clause rejecting some entries that ought to match... regards, tom lane