ORDER BY bug in 8.1, WinXP

This problem was reported by Paolo Lopez in pgsql-es-ayuda. Those who
can read spanish can probably get a better picture by seeing the
archives there. The initial post in the thread is this one:
http://archives.postgresql.org/pgsql-es-ayuda/2006-04/msg00095.php

This one, by Oswaldo Hernandez, has a detailed test case and more
exploration of problem conditions:

http://archives.postgresql.org/pgsql-es-ayuda/2006-04/msg00204.php

Apparently the point is that it fails when there is an index scan using
the primary key. So maybe the problem is that the index is corrupt.

I observe that Paolo was using 8.1.0 and Oswaldo 8.1.1. I can't
reproduce the problem here, but my system is Linux.

Oswaldo writes (translated):

> Windows XP SP1
> postgres=# select version();
> version
> ------------------------------------------------------------------------------------------
> PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)
> (1 fila)
>
> Even with this expression I can reproduce the problem on my system:
>
> CREATE TABLE tablita (
> d int4 ,
> e int4 ,
> f int4 ,
> dia int4 ,
> primary key (d, e, f, dia)
> );
>
>
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 1);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 3);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 5);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 2);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 4);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 5);
>
> select dia from tablita where d = 1 and e = 1 order by dia;
>
> The result, both on pgadmin and psql is:
>
> dia
> -----
> 1
> 3
> 5
> 2
> 4
> 5
> (6 filas)
>
>
> * I think I've found something:
>
> 1. Change the values of column 'e':
>
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 1, 1);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 1, 3);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 1, 5);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 2, 2);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 2, 4);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 2, 5);
>
> select dia from tablita where d = 1 and e = 2 order by dia;
>
> The result is correct:
>
> dia
> -----
> 1
> 2
> 3
> 4
> 5
> 5
> (6 filas)
>
>
> 2. Change the values of columns 'd' and 'e' and put the same value to
> both, but different from '1':
>
> INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 1, 1);
> INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 1, 3);
> INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 1, 5);
> INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 2, 2);
> INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 2, 4);
> INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 2, 5);
>
> select dia from tablita where d = 21512 and e = 21512 order by dia;
>
> Result is wrong again:
> dia
> -----
> 1
> 3
> 5
> 2
> 4
> 5
> (6 filas)
>
> 3. Put the same value in 'd' and 'e', but change the where condition:
>
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 1);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 3);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 5);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 2);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 4);
> INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 5);
>
> postgres=# select dia from tablita where d > 0 and e > 0 order by dia;
> dia
> -----
> 1
> 2
> 3
> 4
> 5
> 5
> (6 filas)
>
> The result is correct:
>
> 4.
>
> More tests changing WHERE conditions:
>
> postgres=# select dia from tablita where e = d and e = 1 order by dia;
> dia
> -----
> 1
> 3
> 5
> 2
> 4
> 5
> (6 filas)
> Wrong
>
> postgres=# select dia from tablita where d between 1 and 1 and e between 1
> and 1 order by dia;
> dia
> -----
> 1
> 2
> 3
> 4
> 5
> 5
> (6 filas)
> Correct
>
> postgres=# select dia from tablita where e = d and e > 0 order by dia;
> dia
> -----
> 1
> 2
> 3
> 4
> 5
> 5
> (6 filas)
> Correct
>
>
> Summary:
> It looks like the failure only presents itself when:
> en WHERE both conditions are present
> 'd' and 'e' have the same value
> the WHERE clause uses operator = for both conditions

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.