Обсуждение: 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.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> I observe that Paolo was using 8.1.0 and Oswaldo 8.1.1.

This appears to be the same bug already fixed in 8.1.3:

2006-01-29 12:27  tgl

    * src/: backend/optimizer/path/indxpath.c,
    backend/optimizer/path/pathkeys.c, include/optimizer/paths.h
    (REL8_1_STABLE): Fix code that checks to see if an index can be
    considered to match the query's requested sort order.  It was
    assuming that build_index_pathkeys always generates a pathkey per
    index column, which was not true if implied equality deduction had
    determined that two index columns were effectively equated to each
    other.    Simplest fix seems to be to install an option that causes
    build_index_pathkeys to support this behavior as well as the
    original one.  Per report from Brian Hirt.

            regards, tom lane