Re: [SQL] Strange behavior

Christophe Labouisse <labouiss(at)cybercable(dot)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