ORDER BY bug in 8.1, WinXP - Mailing list pgsql-bugs

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.