[HACKERS] strange behaviour (bug)

[Kovacs Zoltan]

Hi,

I experience a strange error with 7.0.2. I cannot get any results with
certain queries. For example, a foo table is defined with a few columns,
it has a

id_string varchar(100)

column, too. I filling this table, it contains e.g. a row with
'something' in the column id_string. I give the next query:

> select * from foo where id_string = 'something';

I get no result.

> select * from foo where id_string like '%something';

I get the row. Strange. Then, if I try to check the result:

> select substr(id_string,1,1) from foo where id_string like '%something';

now I will get 's' as expected... Dumping the database out and bringing it
back the problem doesn't appear anymore... for a while... I cannot give
an exact report, but usually this bug occurs when I stop the database
and I start it again.

Did anybody experience such a behaviour?

TIA, Zoltan

Kov\'acs, Zolt\'an
kov...@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

[Tom Lane]

Kovacs Zoltan <kov...@pc10.radnoti-szeged.sulinet.hu> writes:
> now I will get 's' as expected... Dumping the database out and bringing it
> back the problem doesn't appear anymore... for a while... I cannot give
> an exact report, but usually this bug occurs when I stop the database
> and I start it again.

Hmm. Is it possible that when you restart the postmaster, you are
accidentally starting it with a different environment --- in particular,
different LOCALE or LC_xxx settings --- than it had before?

If there is an index on id_string then

> select * from foo where id_string = 'something';

would try to use the index, and so could get messed up by a change
in LOCALE; the index would now appear to be out of order according to
the new LOCALE value.

We really ought to fix things so that all the LOCALE settings are saved
by "initdb" and then re-established during postmaster start, rather than
relying on the user always to start the postmaster with the same
environment. People have been burnt by this before :-(

regards, tom lane