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