Thread: unable to understand query result

I have a database returing these result sets for two almost
identical queries:

#v+
$ select id, pid, length(pid), md5(pid) from product where pid  like '8000000';
 id   | pid     | length |               md5
------+---------+--------+----------------------------------
 3594 | 8000000 |      7 | 60b5792913f4acbccf45c281fa9e3c9f
(1 row)

$ select id, pid, length(pid), md5(pid) from product where pid  like '%8000000';
 id   | pid     | length |               md5
------+---------+--------+----------------------------------
 3594 | 8000000 |      7 | 60b5792913f4acbccf45c281fa9e3c9f
  722 | 8000000 |      7 | 60b5792913f4acbccf45c281fa9e3c9f
(2 rows)
#v-


There should be only one product with this id (no unique key,
because this is not a general constraint but just the current
situation), but there are two. Looking for it with query 1 I got
only one result, suspecting leading or trailing blanks I found the
other one using the wildcard.

But: What is wrong here? There must be some difference between the
two product ids, but with the same md5-hash there seems to be none.

This is by far too trivial to be a postgresql-bug, but what exactly
am I missing here?

Bye,
Stefan

On 06.07.22 11:31, Stefan Froehlich wrote:
> I have a database returing these result sets for two almost
> identical queries:
> 
> #v+
> $ select id, pid, length(pid), md5(pid) from product where pid  like '8000000';
>   id   | pid     | length |               md5
> ------+---------+--------+----------------------------------
>   3594 | 8000000 |      7 | 60b5792913f4acbccf45c281fa9e3c9f
> (1 row)
> 
> $ select id, pid, length(pid), md5(pid) from product where pid  like '%8000000';
>   id   | pid     | length |               md5
> ------+---------+--------+----------------------------------
>   3594 | 8000000 |      7 | 60b5792913f4acbccf45c281fa9e3c9f
>    722 | 8000000 |      7 | 60b5792913f4acbccf45c281fa9e3c9f
> (2 rows)
> #v-

Check the EXPLAIN output for the two queries.  Maybe there is index 
corruption somewhere.

On Wed, Jul 06, 2022 at 11:40:22AM +0200, Peter Eisentraut wrote:
> On 06.07.22 11:31, Stefan Froehlich wrote:
> >I have a database returing these result sets for two almost
> >identical queries:
> >
> >#v+
> >$ select id, pid, length(pid), md5(pid) from product where pid  like '8000000';
> >(1 row)
> >
> >$ select id, pid, length(pid), md5(pid) from product where pid  like '%8000000';
> >(2 rows)
> >#v-
 
> Check the EXPLAIN output for the two queries.  Maybe there is index
> corruption somewhere.

OMG. Thanks, this was the root of the problem. Never run into this
before so it did not come to my mind.

Bye,
Stefan