Postgres Pro
Downloads
Home   >   mailing lists

BUG #17158: Distinct ROW fails with Postgres 14 - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #17158: Distinct ROW fails with Postgres 14
Date
Msg-id 17158-8a2ba823982537a4@postgresql.org
Whole thread Raw
Responses Re: BUG #17158: Distinct ROW fails with Postgres 14  (David Rowley <dgrowleyml@gmail.com>)
List pgsql-bugs
Tree view 
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.

pgsql-bugs by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: BUG #17156: pg_restore: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used
Next
From: David Rowley
Date:
Subject: Re: BUG #17158: Distinct ROW fails with Postgres 14