Re: 7.0.3 reproduceable serious select error

Rob van Nieuwkerk wrote:
I tried to reproduce this bug on 7.0.2 and 7.0.3 with both 8K and 32K block
sizes, and could not reproduce the error.

I am running RedHat 6.2 kernel 2.2.16.

I don't know enough to even be close, but I wonder if there are any subtle
differences between the way characters are treated for indexes vs the way they
are treated for table scans? If there are even slight differences in the way
this happens, a misinterpretation of ascii conversions for instance, (I am
assuming you may be using ascii characters above 0x7F), it could behave
something like this, and explain why I wouldn't see it. .Like I said, however,
I don't know  much so don't read too much into what I say.



> 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 >>>>>>
> ***********************************************************************