7.0.3 reproduceable serious select error - Mailing list pgsql-hackers

Hello,

I've selected postgresql 7.0.3 for our (critical) application and while
doing my first experiments I've found a bug which makes me worry very
much.

The problem is that a SELECT with a certain LIKE condition in combination
with a GROUP BY does not find the proper records when there is an index on
the particular column present.  When the index is removed the SELECT *does*
return the right answer.

Fortunately I managed to strip down our database and create a simple
single table with which the bug can be easily reproduced.

I've been searching in the Postgres bug-database and this problem
might be related to this report:
       http://www.postgresql.org/bugs/bugs.php?4~111

Below you find a psql-session that demonstrates the bug.

I've made a dump of the test-database available as:              http://dutepp0.et.tudelft.nl/~robn/demo.dump.bz2

(it is 46100 bytes long in compressed form but 45 MB when uncompressed,I tried to trim it down but then the bug isn't
reproducableanymore !)
 

The table is filled with all Spaces execpt for the "town" column.


Sysinfo:  
--------       - well-maintained Linux Red Hat 6.2- kernel 2.2.18       - Intel Pentium III       - postgresql-7.0.3-2
RPMsfrom the Postgresql site         (the problem also occurs with locally rebuilt Source RPM)
 

Any help is much appreciated !              Friendly greetings,       Rob van Nieuwkerk


psql session:
***********************************************************************
demo=> \d     List of relations   Name    | Type  | Owner 
------------+-------+-------demo_table | table | robn
(1 row)

demo=> \d demo_table          Table "demo_table"Attribute |   Type   | Modifier 
-----------+----------+----------postcode  | char(7)  |odd_even  | char(1)  |low       | char(5)  |high      | char(5)
|street   | char(24) | town      | char(24) |   area      | char(1)  |
 

demo=> \di
No relations found.
demo=> SELECT town FROM demo_table WHERE town  LIKE 'ZWO%' GROUP BY town;          town           
--------------------------ZWOLLE
(1 row)

demo=> SELECT town FROM demo_table WHERE town  LIKE 'Z%' GROUP BY town;
   <<<<<< here 86 towns are correctly found (output removed) >>>>>>

demo=> CREATE INDEX demo_table_town_idx ON demo_table(town);
CREATE
demo=> SELECT town FROM demo_table WHERE town  LIKE 'Z%' GROUP BY town;town
------
(0 rows)       <<<<<< This is wrong !!!!!! >>>>>>>

demo=> SELECT town FROM demo_table WHERE town  LIKE 'ZWO%' GROUP BY town;          town
--------------------------ZWOLLE
(1 row)

demo=> DROP INDEX demo_table_town_idx;
DROP
demo=> SELECT town FROM demo_table WHERE town  LIKE 'Z%' GROUP BY town;
   <<<<<< here 86 towns are correctly found again >>>>>>
***********************************************************************