perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Relation Name')::text,

'"runtime_test_1_1"',

'wrong partition');

select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans') into num;

perform test.pathman_equal(num::text, '2', 'expected 2 child plans for custom scan');

return 'ok';

end;

$$ language plpgsql;

plan jsonb;

num int;

begin

plan = test.pathman_test('select * from test.runtime_test_1 where id = any (select * from test.run_values limit6)');

plan = test.pathman_test('select * from test.runtime_test_1 where id = any (select * from test.run_values limit4)');

perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Nested Loop"',

'wrong plan provider');

select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;

perform test.pathman_equal(num::text, '6', 'expected6 child plans for custom scan');

perform test.pathman_equal(num::text, '4', 'expected4 child plans for custom scan');

for i in 0..5 loop

for i in 0..3 loop

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->i->'Relation Name')::text,

format('"runtime_test_1_%s"', i + 1),

'wrong partition');

'wrong plan provider');

select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;

perform test.pathman_equal(num::text, '128', 'expected128 child plans for custom scan');

perform test.pathman_equal(num::text, '6', 'expected6 child plans for custom scan');

for i in 0..127 loop

for i in 0..5 loop

num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';

perform test.pathman_assert(num <=79, 'expected no more than79 loops');

perform test.pathman_assert(num> 0 and num<=1667, 'expected no more than1667 loops');

end loop;

return 'ok';

num int;

begin

plan = test.pathman_test('select * from test.category c, lateral' ||

'(select * from test.runtime_test_2 g where g.category_id = c.id order by rating limit10) as tg');

'(select * from test.runtime_test_2 g where g.category_id = c.id order by rating limit4) as tg');

perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Nested Loop"',

'wrong plan provider');

select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans') into num;

perform test.pathman_equal(num::text, '10', 'expected10 child plans for custom scan');

perform test.pathman_equal(num::text, '4', 'expected4 child plans for custom scan');

for i in 0..9 loop

for i in 0..3 loop

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Relation Name')::text,

format('"runtime_test_2_%s"', i + 1),

'wrong partition');

create table test.run_values as select generate_series(1, 10000) val;

create table test.runtime_test_1(id serial primary key, val real);

insert into test.runtime_test_1 select generate_series(1, 10000), random();

select pathman.create_hash_partitions('test.runtime_test_1', 'id',128);

select pathman.create_hash_partitions('test.runtime_test_1', 'id',6);

NOTICE: function test.runtime_test_1_insert_trigger_func() does not exist, skipping

NOTICE: function test.runtime_test_1_update_trigger_func() does not exist, skipping

NOTICE: Copying data to partitions...

create_hash_partitions

------------------------

(1 row)

create table test.category as (select id, 'cat' || id::text as name from generate_series(1,10) id);

create table test.category as (select id, 'cat' || id::text as name from generate_series(1,4) id);

create table test.runtime_test_2 (id serial, category_id int not null, name text, rating real);

insert into test.runtime_test_2 (select id, (id %10) + 1 as category_id, 'good' || id::text as name, random() as rating from generate_series(1,1000000) id);

insert into test.runtime_test_2 (select id, (id %6) + 1 as category_id, 'good' || id::text as name, random() as rating from generate_series(1,100000) id);

create index on test.runtime_test_2 (category_id, rating);

select pathman.create_hash_partitions('test.runtime_test_2', 'category_id',128);

select pathman.create_hash_partitions('test.runtime_test_2', 'category_id',6);

NOTICE: function test.runtime_test_2_insert_trigger_func() does not exist, skipping

NOTICE: function test.runtime_test_2_update_trigger_func() does not exist, skipping

NOTICE: Copying data to partitions...

create_hash_partitions

------------------------

(1 row)

analyze test.run_values;

set enable_mergejoin = on;

set enable_hashjoin = on;

drop table test.run_values, test.runtime_test_1, test.runtime_test_2 cascade;

NOTICE: drop cascades to256 other objects

NOTICE: drop cascades to12 other objects

/*

* Test split and merge

*/

performtest.pathman_equal((plan->0->'Plan'->'Plans'->1->'Relation Name')::text,

'"runtime_test_1_1"',

'wrong partition');

selectcount(*)from jsonb_array_elements_text(plan->0->'Plan'->'Plans') into num;

performtest.pathman_equal(num::text,'2','expected 2 child plans for custom scan');

return'ok';

end;

$$ language plpgsql;

plan jsonb;

numint;

plan=test.pathman_test('select * from test.runtime_test_1 where id = any (select * from test.run_values limit6)');

plan=test.pathman_test('select * from test.runtime_test_1 where id = any (select * from test.run_values limit4)');

performtest.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Nested Loop"',

'wrong plan provider');

selectcount(*)from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;

performtest.pathman_equal(num::text,'6','expected6 child plans for custom scan');

performtest.pathman_equal(num::text,'4','expected4 child plans for custom scan');

for iin0..5 loop

for iin0..3 loop

performtest.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->i->'Relation Name')::text,

format('"runtime_test_1_%s"', i+1),

'wrong partition');

'wrong plan provider');

selectcount(*)from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;

performtest.pathman_equal(num::text,'128','expected128 child plans for custom scan');

performtest.pathman_equal(num::text,'6','expected6 child plans for custom scan');

for iin0..127 loop

for iin0..5 loop

num= plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';

performtest.pathman_assert(num<=79,'expected no more than79 loops');

performtest.pathman_assert(num>0and num<=1667,'expected no more than1667 loops');

end loop;

return'ok';

numint;

plan=test.pathman_test('select * from test.category c, lateral'||

'(select * from test.runtime_test_2 g where g.category_id = c.id order by rating limit10) as tg');

'(select * from test.runtime_test_2 g where g.category_id = c.id order by rating limit4) as tg');

performtest.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Nested Loop"',

'wrong plan provider');

selectcount(*)from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans') into num;

performtest.pathman_equal(num::text,'10','expected10 child plans for custom scan');

performtest.pathman_equal(num::text,'4','expected4 child plans for custom scan');

for iin0..9 loop

for iin0..3 loop

performtest.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Relation Name')::text,

format('"runtime_test_2_%s"', i+1),

'wrong partition');

createtabletest.run_valuesasselect generate_series(1,10000) val;

createtabletest.runtime_test_1(idserialprimary key, valreal);

insert intotest.runtime_test_1select generate_series(1,10000), random();

selectpathman.create_hash_partitions('test.runtime_test_1','id',128);

selectpathman.create_hash_partitions('test.runtime_test_1','id',6);

createtabletest.categoryas (select id,'cat'|| id::textas namefrom generate_series(1,10) id);

createtabletest.categoryas (select id,'cat'|| id::textas namefrom generate_series(1,4) id);

createtabletest.runtime_test_2 (idserial, category_idintnot null, nametext, ratingreal);

insert intotest.runtime_test_2 (select id, (id %10)+1as category_id,'good'|| id::textas name, random()as ratingfrom generate_series(1,1000000) id);

insert intotest.runtime_test_2 (select id, (id %6)+1as category_id,'good'|| id::textas name, random()as ratingfrom generate_series(1,100000) id);

createindexontest.runtime_test_2 (category_id, rating);

selectpathman.create_hash_partitions('test.runtime_test_2','category_id',128);

selectpathman.create_hash_partitions('test.runtime_test_2','category_id',6);

analyzetest.run_values;

analyzetest.runtime_test_1;