pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: sait.nisanci@microsoft.com
Subject: BUG #17158: Distinct ROW fails with Postgres 14
Date: Tue, 24 Aug 2021 08:46:46 +0000
Message-ID: <17158-8a2ba823982537a4@postgresql.org> (raw )
The following bug has been logged on the website:
Bug reference: 17158
Logged by: sait talha nisanci
Email address: sait.nisanci@microsoft.com
PostgreSQL version: 14beta3
Operating system: Ubuntu 20.04
Description:
Hi,
```
Create or replace function test_jsonb() returns jsonb as
$$
begin
return '{"test_json": "test"}';
end;
$$ language plpgsql;
CREATE TABLE local
(
dist_key bigint PRIMARY KEY,
col1 int[], col2 int[][], col3 int [][][],
col4 varchar[], col5 varchar[][], col6 varchar [][][],
col70 bit, col7 bit[], col8 bit[][], col9 bit [][][],
col10 bit varying(10),
col11 bit varying(10)[], col12 bit varying(10)[][], col13 bit
varying(10)[][][],
col14 bytea, col15 bytea[], col16 bytea[][], col17 bytea[][][],
col18 boolean, col19 boolean[], col20 boolean[][], col21 boolean[][][],
col22 inet, col23 inet[], col24 inet[][], col25 inet[][][],
col26 macaddr, col27 macaddr[], col28 macaddr[][], col29 macaddr[][][],
col30 numeric, col32 numeric[], col33 numeric[][], col34 numeric[][][],
col35 jsonb, col36 jsonb[], col37 jsonb[][], col38 jsonb[][][]
);
INSERT INTO local (dist_key,col1, col2, col3, col4, col5, col6, col70, col7,
col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18,
col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29,
col30, col32, col33, col34, col35, col36, col37, col38)
VALUES (1,ARRAY[1], ARRAY[ARRAY[0,0,0]], ARRAY[ARRAY[ARRAY[0,0,0]]],
ARRAY['1'], ARRAY[ARRAY['0','0','0']], ARRAY[ARRAY[ARRAY['0','0','0']]],
'1', ARRAY[b'1'], ARRAY[ARRAY[b'0',b'0',b'0']],
ARRAY[ARRAY[ARRAY[b'0',b'0',b'0']]], '11101',ARRAY[b'1'],
ARRAY[ARRAY[b'01',b'01',b'01']], ARRAY[ARRAY[ARRAY[b'011',b'110',b'0000']]],
'\xb4a8e04c0b', ARRAY['\xb4a8e04c0b'::BYTEA],
ARRAY[ARRAY['\xb4a8e04c0b'::BYTEA, '\xb4a8e04c0b'::BYTEA,
'\xb4a8e04c0b'::BYTEA]],
ARRAY[ARRAY[ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA]]],
'1', ARRAY[TRUE], ARRAY[ARRAY[1::boolean,TRUE,FALSE]],
ARRAY[ARRAY[ARRAY[1::boolean,TRUE,FALSE]]], INET '192.168.1/24', ARRAY[INET
'192.168.1.1'], ARRAY[ARRAY[INET '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1',
'192.168.1/24']], ARRAY[ARRAY[ARRAY[INET '0.0.0.0', '0.0.0.0/32',
'::ffff:fff0:1', '192.168.1/24']]],MACADDR '08:00:2b:01:02:03',
ARRAY[MACADDR '08:00:2b:01:02:03'], ARRAY[ARRAY[MACADDR '08002b-010203',
MACADDR '08002b-010203', '08002b010203']], ARRAY[ARRAY[ARRAY[MACADDR
'08002b-010203', MACADDR '08002b-010203', '08002b010203']]], 690,
ARRAY[1.1], ARRAY[ARRAY[0,0.111,0.15]], ARRAY[ARRAY[ARRAY[0,0,0]]],
test_jsonb(), ARRAY[test_jsonb()],
ARRAY[ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()]],
ARRAY[ARRAY[ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()]]]),
(2,ARRAY[1,2,3], ARRAY[ARRAY[1,2,3], ARRAY[5,6,7]],
ARRAY[ARRAY[ARRAY[1,2,3]], ARRAY[ARRAY[5,6,7]], ARRAY[ARRAY[1,2,3]],
ARRAY[ARRAY[5,6,7]]], ARRAY['1','2','3'], ARRAY[ARRAY['1','2','3'],
ARRAY['5','6','7']], ARRAY[ARRAY[ARRAY['1','2','3']],
ARRAY[ARRAY['5','6','7']], ARRAY[ARRAY['1','2','3']],
ARRAY[ARRAY['5','6','7']]], '0', ARRAY[b'1',b'0',b'0'],
ARRAY[ARRAY[b'1',b'1',b'0'], ARRAY[b'0',b'0',b'1']],
ARRAY[ARRAY[ARRAY[b'1',b'1',b'1']], ARRAY[ARRAY[b'1','0','0']],
ARRAY[ARRAY[b'1','1','1']], ARRAY[ARRAY[b'0','0','0']]], '00010',
ARRAY[b'11',b'10',b'01'], ARRAY[ARRAY[b'11',b'010',b'101'],
ARRAY[b'101',b'01111',b'1000001']],
ARRAY[ARRAY[ARRAY[b'10000',b'111111',b'1101010101']],
ARRAY[ARRAY[b'1101010','0','1']], ARRAY[ARRAY[b'1','1','11111111']],
ARRAY[ARRAY[b'0000000','0','0']]], '\xb4a8e04c0b',
ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA],
ARRAY[ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA],
ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA]],
ARRAY[ARRAY[ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA]],
ARRAY[ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA]],
ARRAY[ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA]],
ARRAY[ARRAY['\xb4a8e04c0b'::BYTEA,'\x18a232a678'::BYTEA,'\x38b2697632'::BYTEA]]],
'true', ARRAY[1::boolean,TRUE,FALSE], ARRAY[ARRAY[1::boolean,TRUE,FALSE],
ARRAY[1::boolean,TRUE,FALSE]], ARRAY[ARRAY[ARRAY[1::boolean,TRUE,FALSE]],
ARRAY[ARRAY[1::boolean,TRUE,FALSE]], ARRAY[ARRAY[1::boolean,TRUE,FALSE]],
ARRAY[ARRAY[1::boolean,TRUE,FALSE]]],'0.0.0.0/32', ARRAY[INET '0.0.0.0',
'0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24'], ARRAY[ARRAY[INET '0.0.0.0',
'0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']], ARRAY[ARRAY[ARRAY[INET
'0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']], ARRAY[ARRAY[INET
'0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']], ARRAY[ARRAY[INET
'0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']], ARRAY[ARRAY[INET
'0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']]],
'0800.2b01.0203', ARRAY[MACADDR '08002b-010203', MACADDR '08002b-010203',
'08002b010203'], ARRAY[ARRAY[MACADDR '08002b-010203', MACADDR
'08002b-010203', '08002b010203']], ARRAY[ARRAY[ARRAY[MACADDR
'08002b-010203', MACADDR '08002b-010203', '08002b010203']],
ARRAY[ARRAY[MACADDR '08002b-010203', MACADDR '08002b-010203',
'08002b010203']], ARRAY[ARRAY[MACADDR '08002b-010203', MACADDR
'08002b-010203', '08002b010203']], ARRAY[ARRAY[MACADDR '08002b-010203',
MACADDR '08002b-010203', '08002b010203']]], 0.99, ARRAY[1.1,2.22,3.33],
ARRAY[ARRAY[1.55,2.66,3.88], ARRAY[11.5,10101.6,7111.1]],
ARRAY[ARRAY[ARRAY[1,2,3]], ARRAY[ARRAY[5,6,7]], ARRAY[ARRAY[1.1,2.1,3]],
ARRAY[ARRAY[5.0,6.0,7.0]]],test_jsonb(),
ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()],
ARRAY[ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()],
ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()]],
ARRAY[ARRAY[ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()]],
ARRAY[ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()]],
ARRAY[ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()]],
ARRAY[ARRAY[test_jsonb(),test_jsonb(),test_jsonb(),test_jsonb()]]]);
-- This works fine before pg14.
SELECT DISTINCT ROW(col1, col2, col3, col4, col5, col6, col70, col7, col8,
col9, col10,
col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21,
col22, col23, col24, col25,
col26, col27, col28, col29, col32, col33, col34, col35, col36, col37, col38)
AS "row" FROM local WHERE true;
ERROR: could not identify a hash function for type bit
```
The `SELECT DISTINCT ROW` works fine prior to Postgres 14, so it seems like
there might be some problem with Postgres14. Is this expected?
Best,
Talha.
view thread (14+ messages) latest in thread
Message-ID: <17158-8a2ba823982537a4@postgresql.org>
Permalink: ../17158-8a2ba823982537a4@postgresql.org/
Also on: postgresql.org/message-id/17158-8a2ba823982537a4@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, sait.nisanci@microsoft.com
Subject: Re: BUG #17158: Distinct ROW fails with Postgres 14
In-Reply-To: <17158-8a2ba823982537a4@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox