pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christophe Labouisse <labouiss@cybercable.fr>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Strange behavior
Date: Mon, 26 Apr 1999 17:23:54 -0400
Message-ID: <4450.925161834@sss.pgh.pa.us> (raw)
In-Reply-To: <m37lqz5g1h.fsf@gabuzo.meunet>

Christophe Labouisse <labouiss@cybercable.fr> 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



view thread (9+ messages)  latest in thread

Message-ID: <4450.925161834@sss.pgh.pa.us>
Permalink:  ../4450.925161834@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4450.925161834@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, labouiss@cybercable.fr
  Subject: Re: [SQL] Strange behavior
  In-Reply-To: <4450.925161834@sss.pgh.pa.us>
  References: <m37lqz5g1h.fsf@gabuzo.meunet> <4450.925161834@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox