1-- 2-- Test hash functions 3-- 4-- When the salt is 0, the extended hash function should produce a result 5-- whose low 32 bits match the standard hash function. When the salt is 6-- not 0, we should get a different result. 7-- 8 9SELECT v as value, hashint2(v)::bit(32) as standard, 10 hashint2extended(v, 0)::bit(32) as extended0, 11 hashint2extended(v, 1)::bit(32) as extended1 12FROM (VALUES (0::int2), (1::int2), (17::int2), (42::int2)) x(v) 13WHERE hashint2(v)::bit(32) != hashint2extended(v, 0)::bit(32) 14 OR hashint2(v)::bit(32) = hashint2extended(v, 1)::bit(32); 15 16SELECT v as value, hashint4(v)::bit(32) as standard, 17 hashint4extended(v, 0)::bit(32) as extended0, 18 hashint4extended(v, 1)::bit(32) as extended1 19FROM (VALUES (0), (1), (17), (42), (550273), (207112489)) x(v) 20WHERE hashint4(v)::bit(32) != hashint4extended(v, 0)::bit(32) 21 OR hashint4(v)::bit(32) = hashint4extended(v, 1)::bit(32); 22 23SELECT v as value, hashint8(v)::bit(32) as standard, 24 hashint8extended(v, 0)::bit(32) as extended0, 25 hashint8extended(v, 1)::bit(32) as extended1 26FROM (VALUES (0), (1), (17), (42), (550273), (207112489)) x(v) 27WHERE hashint8(v)::bit(32) != hashint8extended(v, 0)::bit(32) 28 OR hashint8(v)::bit(32) = hashint8extended(v, 1)::bit(32); 29 30SELECT v as value, hashfloat4(v)::bit(32) as standard, 31 hashfloat4extended(v, 0)::bit(32) as extended0, 32 hashfloat4extended(v, 1)::bit(32) as extended1 33FROM (VALUES (0), (1), (17), (42), (550273), (207112489)) x(v) 34WHERE hashfloat4(v)::bit(32) != hashfloat4extended(v, 0)::bit(32) 35 OR hashfloat4(v)::bit(32) = hashfloat4extended(v, 1)::bit(32); 36 37SELECT v as value, hashfloat8(v)::bit(32) as standard, 38 hashfloat8extended(v, 0)::bit(32) as extended0, 39 hashfloat8extended(v, 1)::bit(32) as extended1 40FROM (VALUES (0), (1), (17), (42), (550273), (207112489)) x(v) 41WHERE hashfloat8(v)::bit(32) != hashfloat8extended(v, 0)::bit(32) 42 OR hashfloat8(v)::bit(32) = hashfloat8extended(v, 1)::bit(32); 43 44SELECT v as value, hashoid(v)::bit(32) as standard, 45 hashoidextended(v, 0)::bit(32) as extended0, 46 hashoidextended(v, 1)::bit(32) as extended1 47FROM (VALUES (0), (1), (17), (42), (550273), (207112489)) x(v) 48WHERE hashoid(v)::bit(32) != hashoidextended(v, 0)::bit(32) 49 OR hashoid(v)::bit(32) = hashoidextended(v, 1)::bit(32); 50 51SELECT v as value, hashchar(v)::bit(32) as standard, 52 hashcharextended(v, 0)::bit(32) as extended0, 53 hashcharextended(v, 1)::bit(32) as extended1 54FROM (VALUES (NULL::"char"), ('1'), ('x'), ('X'), ('p'), ('N')) x(v) 55WHERE hashchar(v)::bit(32) != hashcharextended(v, 0)::bit(32) 56 OR hashchar(v)::bit(32) = hashcharextended(v, 1)::bit(32); 57 58SELECT v as value, hashname(v)::bit(32) as standard, 59 hashnameextended(v, 0)::bit(32) as extended0, 60 hashnameextended(v, 1)::bit(32) as extended1 61FROM (VALUES (NULL), ('PostgreSQL'), ('eIpUEtqmY89'), ('AXKEJBTK'), 62 ('muop28x03'), ('yi3nm0d73')) x(v) 63WHERE hashname(v)::bit(32) != hashnameextended(v, 0)::bit(32) 64 OR hashname(v)::bit(32) = hashnameextended(v, 1)::bit(32); 65 66SELECT v as value, hashtext(v)::bit(32) as standard, 67 hashtextextended(v, 0)::bit(32) as extended0, 68 hashtextextended(v, 1)::bit(32) as extended1 69FROM (VALUES (NULL), ('PostgreSQL'), ('eIpUEtqmY89'), ('AXKEJBTK'), 70 ('muop28x03'), ('yi3nm0d73')) x(v) 71WHERE hashtext(v)::bit(32) != hashtextextended(v, 0)::bit(32) 72 OR hashtext(v)::bit(32) = hashtextextended(v, 1)::bit(32); 73 74SELECT v as value, hashoidvector(v)::bit(32) as standard, 75 hashoidvectorextended(v, 0)::bit(32) as extended0, 76 hashoidvectorextended(v, 1)::bit(32) as extended1 77FROM (VALUES (NULL::oidvector), ('0 1 2 3 4'), ('17 18 19 20'), 78 ('42 43 42 45'), ('550273 550273 570274'), 79 ('207112489 207112499 21512 2155 372325 1363252')) x(v) 80WHERE hashoidvector(v)::bit(32) != hashoidvectorextended(v, 0)::bit(32) 81 OR hashoidvector(v)::bit(32) = hashoidvectorextended(v, 1)::bit(32); 82 83SELECT v as value, hash_aclitem(v)::bit(32) as standard, 84 hash_aclitem_extended(v, 0)::bit(32) as extended0, 85 hash_aclitem_extended(v, 1)::bit(32) as extended1 86FROM (SELECT DISTINCT(relacl[1]) FROM pg_class LIMIT 10) x(v) 87WHERE hash_aclitem(v)::bit(32) != hash_aclitem_extended(v, 0)::bit(32) 88 OR hash_aclitem(v)::bit(32) = hash_aclitem_extended(v, 1)::bit(32); 89 90SELECT v as value, hashmacaddr(v)::bit(32) as standard, 91 hashmacaddrextended(v, 0)::bit(32) as extended0, 92 hashmacaddrextended(v, 1)::bit(32) as extended1 93FROM (VALUES (NULL::macaddr), ('08:00:2b:01:02:04'), ('08:00:2b:01:02:04'), 94 ('e2:7f:51:3e:70:49'), ('d6:a9:4a:78:1c:d5'), 95 ('ea:29:b1:5e:1f:a5')) x(v) 96WHERE hashmacaddr(v)::bit(32) != hashmacaddrextended(v, 0)::bit(32) 97 OR hashmacaddr(v)::bit(32) = hashmacaddrextended(v, 1)::bit(32); 98 99SELECT v as value, hashinet(v)::bit(32) as standard, 100 hashinetextended(v, 0)::bit(32) as extended0, 101 hashinetextended(v, 1)::bit(32) as extended1 102FROM (VALUES (NULL::inet), ('192.168.100.128/25'), ('192.168.100.0/8'), 103 ('172.168.10.126/16'), ('172.18.103.126/24'), ('192.188.13.16/32')) x(v) 104WHERE hashinet(v)::bit(32) != hashinetextended(v, 0)::bit(32) 105 OR hashinet(v)::bit(32) = hashinetextended(v, 1)::bit(32); 106 107SELECT v as value, hash_numeric(v)::bit(32) as standard, 108 hash_numeric_extended(v, 0)::bit(32) as extended0, 109 hash_numeric_extended(v, 1)::bit(32) as extended1 110FROM (VALUES (0), (1.149484958), (17.149484958), (42.149484958), 111 (149484958.550273), (2071124898672)) x(v) 112WHERE hash_numeric(v)::bit(32) != hash_numeric_extended(v, 0)::bit(32) 113 OR hash_numeric(v)::bit(32) = hash_numeric_extended(v, 1)::bit(32); 114 115SELECT v as value, hashmacaddr8(v)::bit(32) as standard, 116 hashmacaddr8extended(v, 0)::bit(32) as extended0, 117 hashmacaddr8extended(v, 1)::bit(32) as extended1 118FROM (VALUES (NULL::macaddr8), ('08:00:2b:01:02:04:36:49'), 119 ('08:00:2b:01:02:04:f0:e8'), ('e2:7f:51:3e:70:49:16:29'), 120 ('d6:a9:4a:78:1c:d5:47:32'), ('ea:29:b1:5e:1f:a5')) x(v) 121WHERE hashmacaddr8(v)::bit(32) != hashmacaddr8extended(v, 0)::bit(32) 122 OR hashmacaddr8(v)::bit(32) = hashmacaddr8extended(v, 1)::bit(32); 123 124SELECT v as value, hash_array(v)::bit(32) as standard, 125 hash_array_extended(v, 0)::bit(32) as extended0, 126 hash_array_extended(v, 1)::bit(32) as extended1 127FROM (VALUES ('{0}'::int4[]), ('{0,1,2,3,4}'), ('{17,18,19,20}'), 128 ('{42,34,65,98}'), ('{550273,590027, 870273}'), 129 ('{207112489, 807112489}')) x(v) 130WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32) 131 OR hash_array(v)::bit(32) = hash_array_extended(v, 1)::bit(32); 132 133SELECT v as value, hashbpchar(v)::bit(32) as standard, 134 hashbpcharextended(v, 0)::bit(32) as extended0, 135 hashbpcharextended(v, 1)::bit(32) as extended1 136FROM (VALUES (NULL), ('PostgreSQL'), ('eIpUEtqmY89'), ('AXKEJBTK'), 137 ('muop28x03'), ('yi3nm0d73')) x(v) 138WHERE hashbpchar(v)::bit(32) != hashbpcharextended(v, 0)::bit(32) 139 OR hashbpchar(v)::bit(32) = hashbpcharextended(v, 1)::bit(32); 140 141SELECT v as value, time_hash(v)::bit(32) as standard, 142 time_hash_extended(v, 0)::bit(32) as extended0, 143 time_hash_extended(v, 1)::bit(32) as extended1 144FROM (VALUES (NULL::time), ('11:09:59'), ('1:09:59'), ('11:59:59'), 145 ('7:9:59'), ('5:15:59')) x(v) 146WHERE time_hash(v)::bit(32) != time_hash_extended(v, 0)::bit(32) 147 OR time_hash(v)::bit(32) = time_hash_extended(v, 1)::bit(32); 148 149SELECT v as value, timetz_hash(v)::bit(32) as standard, 150 timetz_hash_extended(v, 0)::bit(32) as extended0, 151 timetz_hash_extended(v, 1)::bit(32) as extended1 152FROM (VALUES (NULL::timetz), ('00:11:52.518762-07'), ('00:11:52.51762-08'), 153 ('00:11:52.62-01'), ('00:11:52.62+01'), ('11:59:59+04')) x(v) 154WHERE timetz_hash(v)::bit(32) != timetz_hash_extended(v, 0)::bit(32) 155 OR timetz_hash(v)::bit(32) = timetz_hash_extended(v, 1)::bit(32); 156 157SELECT v as value, interval_hash(v)::bit(32) as standard, 158 interval_hash_extended(v, 0)::bit(32) as extended0, 159 interval_hash_extended(v, 1)::bit(32) as extended1 160FROM (VALUES (NULL::interval), 161 ('5 month 7 day 46 minutes'), ('1 year 7 day 46 minutes'), 162 ('1 year 7 month 20 day 46 minutes'), ('5 month'), 163 ('17 year 11 month 7 day 9 hours 46 minutes 5 seconds')) x(v) 164WHERE interval_hash(v)::bit(32) != interval_hash_extended(v, 0)::bit(32) 165 OR interval_hash(v)::bit(32) = interval_hash_extended(v, 1)::bit(32); 166 167SELECT v as value, timestamp_hash(v)::bit(32) as standard, 168 timestamp_hash_extended(v, 0)::bit(32) as extended0, 169 timestamp_hash_extended(v, 1)::bit(32) as extended1 170FROM (VALUES (NULL::timestamp), ('2017-08-22 00:09:59.518762'), 171 ('2015-08-20 00:11:52.51762-08'), 172 ('2017-05-22 00:11:52.62-01'), 173 ('2013-08-22 00:11:52.62+01'), ('2013-08-22 11:59:59+04')) x(v) 174WHERE timestamp_hash(v)::bit(32) != timestamp_hash_extended(v, 0)::bit(32) 175 OR timestamp_hash(v)::bit(32) = timestamp_hash_extended(v, 1)::bit(32); 176 177SELECT v as value, uuid_hash(v)::bit(32) as standard, 178 uuid_hash_extended(v, 0)::bit(32) as extended0, 179 uuid_hash_extended(v, 1)::bit(32) as extended1 180FROM (VALUES (NULL::uuid), ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'), 181 ('5a9ba4ac-8d6f-11e7-bb31-be2e44b06b34'), 182 ('99c6705c-d939-461c-a3c9-1690ad64ed7b'), 183 ('7deed3ca-8d6f-11e7-bb31-be2e44b06b34'), 184 ('9ad46d4f-6f2a-4edd-aadb-745993928e1e')) x(v) 185WHERE uuid_hash(v)::bit(32) != uuid_hash_extended(v, 0)::bit(32) 186 OR uuid_hash(v)::bit(32) = uuid_hash_extended(v, 1)::bit(32); 187 188SELECT v as value, pg_lsn_hash(v)::bit(32) as standard, 189 pg_lsn_hash_extended(v, 0)::bit(32) as extended0, 190 pg_lsn_hash_extended(v, 1)::bit(32) as extended1 191FROM (VALUES (NULL::pg_lsn), ('16/B374D84'), ('30/B374D84'), 192 ('255/B374D84'), ('25/B379D90'), ('900/F37FD90')) x(v) 193WHERE pg_lsn_hash(v)::bit(32) != pg_lsn_hash_extended(v, 0)::bit(32) 194 OR pg_lsn_hash(v)::bit(32) = pg_lsn_hash_extended(v, 1)::bit(32); 195 196CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); 197SELECT v as value, hashenum(v)::bit(32) as standard, 198 hashenumextended(v, 0)::bit(32) as extended0, 199 hashenumextended(v, 1)::bit(32) as extended1 200FROM (VALUES ('sad'::mood), ('ok'), ('happy')) x(v) 201WHERE hashenum(v)::bit(32) != hashenumextended(v, 0)::bit(32) 202 OR hashenum(v)::bit(32) = hashenumextended(v, 1)::bit(32); 203DROP TYPE mood; 204 205SELECT v as value, jsonb_hash(v)::bit(32) as standard, 206 jsonb_hash_extended(v, 0)::bit(32) as extended0, 207 jsonb_hash_extended(v, 1)::bit(32) as extended1 208FROM (VALUES (NULL::jsonb), 209 ('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'), 210 ('{"foo": [true, "bar"], "tags": {"e": 1, "f": null}}'), 211 ('{"g": {"h": "value"}}')) x(v) 212WHERE jsonb_hash(v)::bit(32) != jsonb_hash_extended(v, 0)::bit(32) 213 OR jsonb_hash(v)::bit(32) = jsonb_hash_extended(v, 1)::bit(32); 214 215SELECT v as value, hash_range(v)::bit(32) as standard, 216 hash_range_extended(v, 0)::bit(32) as extended0, 217 hash_range_extended(v, 1)::bit(32) as extended1 218FROM (VALUES (int4range(10, 20)), (int4range(23, 43)), 219 (int4range(5675, 550273)), 220 (int4range(550274, 1550274)), (int4range(1550275, 208112489))) x(v) 221WHERE hash_range(v)::bit(32) != hash_range_extended(v, 0)::bit(32) 222 OR hash_range(v)::bit(32) = hash_range_extended(v, 1)::bit(32); 223 224-- 225-- Check special cases for specific data types 226-- 227SELECT hashfloat4('0'::float4) = hashfloat4('-0'::float4) AS t; 228SELECT hashfloat4('NaN'::float4) = hashfloat4(-'NaN'::float4) AS t; 229SELECT hashfloat8('0'::float8) = hashfloat8('-0'::float8) AS t; 230SELECT hashfloat8('NaN'::float8) = hashfloat8(-'NaN'::float8) AS t; 231SELECT hashfloat4('NaN'::float4) = hashfloat8('NaN'::float8) AS t; 232