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

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)

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> However, for small-magnitude negative numbers, hashint8() and hashint4() do
> not produce the same results:

Hm, it works as expected for me.  What compiler and compile options
did you use?

regression=# select i, hashint2(i::smallint), hashint4(i), hashint8(i::bigint) from
generate_series(-10,10) i;
  i  |  hashint2   |  hashint4   |  hashint8
-----+-------------+-------------+-------------
 -10 |  1890576549 |  1890576549 |  1890576549
  -9 |   108489501 |   108489501 |   108489501
  -8 |  1704507032 |  1704507032 |  1704507032
  -7 |   -82204417 |   -82204417 |   -82204417
  -6 | -2107898529 | -2107898529 | -2107898529
  -5 |  1412886042 |  1412886042 |  1412886042
  -4 |   174778200 |   174778200 |   174778200
  -3 | -1044245375 | -1044245375 | -1044245375
  -2 | -1242090879 | -1242090879 | -1242090879
  -1 |   385747274 |   385747274 |   385747274
   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
(21 rows)


            regards, tom lane

On 12/21/17 12:31, Tom Lane wrote:
> =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
>> However, for small-magnitude negative numbers, hashint8() and hashint4() do
>> not produce the same results:
> 
> Hm, it works as expected for me.  What compiler and compile options
> did you use?

$ gcc --version
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16)


CONFIGURE = '--prefix=/home/tac/tmp/pg10' '--with-icu' '--with-perl' '--with-python' '--with-tcl'
'--with-tclconfig=/usr/lib64''--with-openssl' '--with-pam' '--with-gssapi' '--with-includes=/usr/include'
'--with-libraries=/usr/lib64''--enable-nls' '--enable-dtrace' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt'
'--with-ldap''--with-selinux' '--with-systemd' '--with-system-tzdata=/usr/share/zoneinfo' '--enable-debug'
 
CC = gcc
CPPFLAGS = -DFRONTEND -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels
-Wmissing-format-attribute-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2
 
CFLAGS_SL = -fPIC
LDFLAGS = -L../../src/common -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/home/tac/tmp/pg10/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lrt
-lcrypt-ldl -lm
 
VERSION = PostgreSQL 10.1

Also, I built from HEAD (as of yesterday afternoon) on REL_10_STABLE.

-- todd

On 12/21/17 12:31, Tom Lane wrote:
> =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
>> However, for small-magnitude negative numbers, hashint8() and hashint4() do
>> not produce the same results:
> 
> Hm, it works as expected for me.  What compiler and compile options
> did you use?

This is looking like a make failure of some sort.  I did

   git reset --hard
   make clean
   make

but I just now did

   make clean
   find . -name '*.?o' -print0 | xargs -0 rm
   make

and now I get the same results as you.

-- todd