Skip site navigation (1) Skip section navigation (2)

ORDER BY bug in 8.1, WinXP

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: ORDER BY bug in 8.1, WinXP
Date: 2006-04-08 17:23:35
Message-ID: 20060408172335.GB14999@surnet.cl (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-es-ayuda
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.

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-04-08 19:13:36
Subject: Re: ORDER BY bug in 8.1, WinXP
Previous:From: Martijn van OosterhoutDate: 2006-04-08 17:16:39
Subject: Re: bug in windows xp

pgsql-es-ayuda by date

Next:From: Tom LaneDate: 2006-04-08 19:13:36
Subject: Re: ORDER BY bug in 8.1, WinXP
Previous:From: Alvaro HerreraDate: 2006-04-08 17:09:10
Subject: Re: AYUDA CON ODBC DE POSTGRESQL PARA VISUAL BASIC 6.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group