The following bug has been logged on the website:
Bug reference: 14990
Logged by: Todd Cook
Email address: tcook@blackducksoftware.com
PostgreSQL version: 10.1
Operating system: CentOS Linux release 7.4.1708 (Core)
Description:
In hashint8(), there is this comment:
* The idea here is to produce a hash value compatible with the values
* produced by hashint4 and hashint2 for logically equal inputs; this is
* necessary to support cross-type hash joins across these input types.
However, for small-magnitude negative numbers, hashint8() and hashint4() do
not
produce the same results:
select i, hashint2(i::smallint), hashint4(i), hashint8(i::bigint) from
generate_series(-10,10) i;
i | hashint2 | hashint4 | hashint8
-----+-------------+-------------+-------------
-10 | 1890576549 | 1890576549 | -1967423857
-9 | 108489501 | 108489501 | -967276248
-8 | 1704507032 | 1704507032 | -2037376779
-7 | -82204417 | -82204417 | 528648157
-6 | -2107898529 | -2107898529 | -2130127737
-5 | 1412886042 | 1412886042 | 153875034
-4 | 174778200 | 174778200 | 1362278291
-3 | -1044245375 | -1044245375 | 983684381
-2 | -1242090879 | -1242090879 | -860369868
-1 | 385747274 | 385747274 | 68015284
0 | -272711505 | -272711505 | -272711505
1 | -1905060026 | -1905060026 | -1905060026
2 | 1134484726 | 1134484726 | 1134484726
3 | -28094569 | -28094569 | -28094569
4 | -1011077333 | -1011077333 | -1011077333
5 | -1330264708 | -1330264708 | -1330264708
6 | 566031088 | 566031088 | 566031088
7 | -978793473 | -978793473 | -978793473
8 | -2047600124 | -2047600124 | -2047600124
9 | 1672378334 | 1672378334 | 1672378334
10 | -1547814713 | -1547814713 | -1547814713
set enable_mergejoin = false ;
select v1 from generate_series(-10,-1) v1, generate_series(-15,-5) v2 where
v1::integer = v2::bigint ;
v1
----
(0 rows)
On 9.5, we get
set enable_mergejoin = false ;
select v1 from generate_series(-10,-1) v1, generate_series(-15,-5) v2 where
v1::integer = v2::bigint ;
v1
-----
-10
-9
-8
-7
-6
-5
(6 rows)
