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 reproducable anymore !)
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 RPMs from 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 >>>>>>
***********************************************************************
