Re: [SQL] Re: [BUGS] "ORDER BY" issue - is this a bug?

Tom Lane wrote:
>
> Max Pyziur <pyz(at)panix(dot)com> writes:
> >> That's pretty bizarre (not to say difficult to believe). What LOCALE
> >> setting are you running the postmaster in?
>
> > On none of the installations - the two 6.5.x and the 7.0.2-2 one - I
> > don't have any locale set. I get (what I think are) correct results
> > with the first two.
>
> > Does 7.0.2-2 require the setting of locale.
>
> AFAIK its behavior should be the same as 6.5 for LOCALE issues. That's
> why I suspect an environment difference.

Thanks for your quick replies.

I dropped my databases, uninstalled the 7.0.2-2 rpms and installed 6.5.3 rpms on
my development RH6.2 Linux 2.2.14-5.0 system and I still get the anomalous query
result on that box.

> I can assure you there is no code in the backend that will do
> case-insensitive, punctuation-insensitive comparisons --- much less any
> to do so without request. I'm betting that either this is your error,
> or the strcmp() library function is doing it; and as far as I've heard,
> only LOCALE environment variables might affect the behavior of strcmp().

There is a $LANG variable which is set to en_US; is this what might be causing
the problem? I've tried unsetting it (unset LANG) and still get the problem.
Is there something else which I should be looking at?

> It also seems possible that no sort is happening at all (which would be
> a planner bug), and the ordering you're getting is just whatever happens
> to be in the underlying table. Does EXPLAIN show that the query is
> being done with an explicit sort?

Running EXPLAIN the results are:
headlines1=> explain select headline from headlines where headline like 'Alb%'
order by 1 ;
NOTICE: QUERY PLAN:

Sort (cost=221.15 rows=1 width=12)
-> Seq Scan on headlines (cost=221.15 rows=1 width=12)

EXPLAIN

> regards, tom lane

Thanks again,

Max Pyziur BRAMA - Gateway Ukraine
pyz(at)brama(dot)com http://www.brama.com/