BUG #14990: hashint4() <> hashint8() for small negative values, causing hash joins to fail

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Cc: tcook(at)blackducksoftware(dot)com
Subject: BUG #14990: hashint4() <> hashint8() for small negative values, causing hash joins to fail
Date: 2017-12-21 16:58:00
Message-ID: 20171221165800.826.12026@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14990
Logged by: Todd Cook
Email address: tcook(at)blackducksoftware(dot)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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-12-21 17:31:44 Re: BUG #14990: hashint4() <> hashint8() for small negative values, causing hash joins to fail
Previous Message PG Bug reporting form 2017-12-21 15:06:56 BUG #14989: Postgis 2.4.2 for PostgreSQL 10 fails to install