partition_wise_join_collation.sql

/*
*/
CREATE COLLATION case_insensitive (provider = icu,locale = 'und-u-ks-level2',deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = '@colStrength=primary;colCaseLevel=yes', deterministic = false);
CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
set max_parallel_workers_per_gather = 0;
set enable_partitionwise_join = on;
drop function if exists partition_wise_join_test;
create or replace function partition_wise_join_test() returns boolean as $$
    declare 
        cnt1 bigint := 0; 
        cnt2 bigint := 0;
begin        
    set enable_partitionwise_join = on;
    select count(*) into cnt1 from part_tab t1 join part_tab_copy t2 on t1.c = t2.c;
    set enable_partitionwise_join = off;
    select count(*) into cnt2 from part_tab t1 join part_tab_copy t2 on t1.c = t2.c;
    if (cnt1 != cnt2) then
        raise notice 'enable_partitionwise_join guc on count is %, off count is %', cnt1, cnt2;
        return false;
    else
        return true;
    end if;
end; $$ language plpgsql;
------------------------------------------------------------------------------------------------------------------------------
--partitionkey collation same as column collation, both are deterministic
drop table if exists part_tab;
drop table if exists part_tab_copy;

CREATE TABLE part_tab (c text collate case_insensitive) PARTITION BY LIST(c collate case_insensitive);
CREATE TABLE part_tab_p1 PARTITION OF part_tab FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_p2 PARTITION OF part_tab FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_p3 PARTITION OF part_tab FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_p4 PARTITION OF part_tab default;

CREATE TABLE part_tab_copy (c text collate case_insensitive) PARTITION BY LIST(c collate case_insensitive);
CREATE TABLE part_tab_copy_p1 PARTITION OF part_tab_copy FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_copy_p2 PARTITION OF part_tab_copy FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_copy_p3 PARTITION OF part_tab_copy FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_copy_p4 PARTITION OF part_tab_copy default;
INSERT INTO part_tab SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
INSERT INTO part_tab_copy SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
set enable_partitionwise_join = on;
explain(costs off, settings) select count(*) from part_tab t1 join part_tab_copy t2 on t1.c = t2.c;
select  partition_wise_join_test() as expect_true;
------------------------------------------------------------------------------------------------------------------------------
-- column collation indeterministic, partitionkey collation deterministic

drop table if exists part_tab;
drop table if exists part_tab_copy;

CREATE TABLE part_tab (c text collate case_insensitive) PARTITION BY LIST(c collate "C");
CREATE TABLE part_tab_p1 PARTITION OF part_tab FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_p2 PARTITION OF part_tab FOR VALUES IN ('e', 'f', 'A');
CREATE TABLE part_tab_p3 PARTITION OF part_tab FOR VALUES IN ('B', 'C','D', 'E');

CREATE TABLE part_tab_copy (c text collate case_insensitive) PARTITION BY LIST(c collate "C");
CREATE TABLE part_tab_copy_p1 PARTITION OF part_tab_copy FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_copy_p2 PARTITION OF part_tab_copy FOR VALUES IN ('e', 'f', 'A');
CREATE TABLE part_tab_copy_p3 PARTITION OF part_tab_copy FOR VALUES IN ('B', 'C','D', 'E');
INSERT INTO part_tab SELECT substr('abcdeABCDE', (i % 10) +1 , 1) FROM generate_series(0, 19) i;
INSERT INTO part_tab_copy SELECT substr('abcdeABCDE', (i % 10) +1 , 1) FROM generate_series(0, 19) i;
set enable_partitionwise_join = on;
explain(costs off, settings) select count(*) from part_tab t1 join part_tab_copy t2 on t1.c = t2.c;
select  partition_wise_join_test() as expect_true;
------------------------------------------------------------------------------------------------------------------------------
-- column collation deterministic, partitionkey collation deterministic, but different

drop table if exists part_tab;
drop table if exists part_tab_copy;
CREATE TABLE part_tab (c text collate "POSIX") PARTITION BY LIST(c collate "C");
CREATE TABLE part_tab_p1 PARTITION OF part_tab FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_p2 PARTITION OF part_tab FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_p3 PARTITION OF part_tab FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_p4 PARTITION OF part_tab default;
CREATE TABLE part_tab_copy (c text collate "POSIX") PARTITION BY LIST(c collate "C");
CREATE TABLE part_tab_copy_p1 PARTITION OF part_tab_copy FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_copy_p2 PARTITION OF part_tab_copy FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_copy_p3 PARTITION OF part_tab_copy FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_copy_p4 PARTITION OF part_tab_copy default;
INSERT INTO part_tab SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
INSERT INTO part_tab_copy SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
set enable_partitionwise_join = on;
explain(costs off, settings) select count(*) from part_tab t1 join part_tab_copy t2 on t1.c = t2.c;
select  partition_wise_join_test() as expect_true;
------------------------------------------------------------------------------------------------------------------------------
-- column collation deterministic, partitionkey collation indeterministic.

drop table if exists part_tab;
drop table if exists part_tab_copy;
CREATE TABLE part_tab (c text collate "C") PARTITION BY LIST(c collate case_insensitive);
CREATE TABLE part_tab_p1 PARTITION OF part_tab FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_p2 PARTITION OF part_tab FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_p3 PARTITION OF part_tab FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_p4 PARTITION OF part_tab default;
INSERT INTO part_tab SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
CREATE TABLE part_tab_copy (c text collate "C") PARTITION BY LIST(c collate case_insensitive);
CREATE TABLE part_tab_copy_p1 PARTITION OF part_tab_copy FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_copy_p2 PARTITION OF part_tab_copy FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_copy_p3 PARTITION OF part_tab_copy FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_copy_p4 PARTITION OF part_tab_copy default;

INSERT INTO part_tab_copy SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
set enable_partitionwise_join = on;
explain(costs off, settings) select count(*) from part_tab t1 join part_tab_copy t2 on t1.c = t2.c;
select  partition_wise_join_test() as expect_true;
------------------------------------------------------------------------------------------------------------------------------'
-- column collation indeterministic, partitionkey collation indeterministic, but different.
drop table if exists part_tab;
drop table if exists part_tab_copy;
CREATE TABLE part_tab (c text collate case_insensitive) PARTITION BY LIST(c collate ignore_accents);
CREATE TABLE part_tab_p1 PARTITION OF part_tab FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_p2 PARTITION OF part_tab FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_p3 PARTITION OF part_tab FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_p4 PARTITION OF part_tab default;
INSERT INTO part_tab SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
CREATE TABLE part_tab_copy (c text collate case_insensitive) PARTITION BY LIST(c collate ignore_accents);
CREATE TABLE part_tab_copy_p1 PARTITION OF part_tab_copy FOR VALUES IN ('a', 'b', 'c', 'd');
CREATE TABLE part_tab_copy_p2 PARTITION OF part_tab_copy FOR VALUES IN ('e', 'f');
CREATE TABLE part_tab_copy_p3 PARTITION OF part_tab_copy FOR VALUES IN ('g', 'h');
CREATE TABLE part_tab_copy_p4 PARTITION OF part_tab_copy default;
INSERT INTO part_tab_copy SELECT substr('abcdeABCDEfgh', (i % 13) +1 , 1) FROM generate_series(0, 19) i;
set enable_partitionwise_join = on;
explain(costs off, settings) select count(*) from part_tab t1 join part_tab_copy t2 on t1.c = t2.c;
select  partition_wise_join_test() as expect_true;