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