Re: 7.0.3 reproduceable serious select error

Hi Mark,

I just checked: the "demo.dump" file does not contain any characters
above 0x7F; it's just plain ASCII.  So that can't be the reason.
       greetings,       Rob van Nieuwkerk


> Rob van Nieuwkerk wrote:

Ehm .., *you* wrote this ! :-)

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