[BUGS] ORDER BY bug in 8.1, WinXP

#1

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...4/msg00095.php

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

http://archives.postgresql.org/pgsql...4/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):

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#2

Alvaro Herrera <alvherre (AT) commandprompt (DOT) com> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[BUGS] ORDER BY bug in 8.1, WinXP

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

[BUGS] ORDER BY bug in 8.1, WinXP - 04-08-2006 , 12:24 PM

Re: [BUGS] ORDER BY bug in 8.1, WinXP - 04-08-2006 , 02:14 PM