diff --git a/src/test/regress/expected/append_pullup.out b/src/test/regress/expected/append_pullup.out new file mode 100644 index 0000000..614a826 --- /dev/null +++ b/src/test/regress/expected/append_pullup.out @@ -0,0 +1,209 @@ +-- +-- Append pull-up across Join +-- +-- +-- Build a table for testing +-- +-- CREATE Partition Table (Modulation is used for dividing) +create temp table check_test_div ( +id integer, +data_x float8, +data_y float8 +); +create temp table check_test_div_0 ( +check(id % 3 = 0) +) inherits(check_test_div); +create temp table check_test_div_1 ( +check(id % 3 = 1) +) inherits(check_test_div); +create temp table check_test_div_2 ( +check(id % 3 = 2) +) inherits(check_test_div); +-- CREATE table for inner relation +create temp table inner_t as +select generate_series(0,3000)::integer as id, ceil(random()*10000)::integer as num; +begin; +insert INTO check_test_div_0 +select (ceil(random()*1000)*3)::integer as id, random(), random() as data +from generate_series(0,5000); +insert INTO check_test_div_1 +select (ceil(random()*1000)*3+1)::integer as id, random(), random() as data +from generate_series(0,5000); +insert INTO check_test_div_2 +select (ceil(random()*1000)*3+2)::integer as id, random(), random() as data +from generate_series(0,5000); +commit; +-- CREATE table for verifying +create temp table test_appended ( +data_x float8, +data_y float8, +num integer +); +begin; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from only check_test_div join inner_t on check_test_div.id = inner_t.id; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_0 join inner_t on check_test_div_0.id = inner_t.id; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_1 join inner_t on check_test_div_1.id = inner_t.id; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_2 join inner_t on check_test_div_2.id = inner_t.id; +commit; +set enable_hashjoin to on; +set enable_mergejoin to off; +set enable_nestloop to off; +-- +-- Check plan +-- +explain (costs off) +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id; + QUERY PLAN +------------------------------------------------------- + Append + -> Hash Join + Hash Cond: (inner_t.id = check_test_div.id) + -> Seq Scan on inner_t + -> Hash + -> Seq Scan on check_test_div + -> Hash Join + Hash Cond: (check_test_div_0.id = inner_t.id) + -> Seq Scan on check_test_div_0 + -> Hash + -> Seq Scan on inner_t + Filter: ((id % 3) = 0) + -> Hash Join + Hash Cond: (check_test_div_1.id = inner_t.id) + -> Seq Scan on check_test_div_1 + -> Hash + -> Seq Scan on inner_t + Filter: ((id % 3) = 1) + -> Hash Join + Hash Cond: (check_test_div_2.id = inner_t.id) + -> Seq Scan on check_test_div_2 + -> Hash + -> Seq Scan on inner_t + Filter: ((id % 3) = 2) +(24 rows) + +-- +-- Verify its results +-- +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +except (select * from test_appended); + data_x | data_y | num +--------+--------+----- +(0 rows) + +select * from test_appended +except ( +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +); + data_x | data_y | num +--------+--------+----- +(0 rows) + +drop table check_test_div cascade; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table check_test_div_0 +drop cascades to table check_test_div_1 +drop cascades to table check_test_div_2 +drop table test_appended; +-- +-- Build a table for testing +-- +-- CREATE Partition Table (Simple; Greater-than/Less-than marks are used for dividing) +create temp table check_test_div ( +id integer, +data_x float8, +data_y float8 +); +create temp table check_test_div_0 ( +check(id < 1000) +) inherits(check_test_div); +create temp table check_test_div_1 ( +check(id between 1000 and 1999) +) inherits(check_test_div); +create temp table check_test_div_2 ( +check(id > 1999) +) inherits(check_test_div); +-- Table for inner relation is already created. +begin; +insert INTO check_test_div_0 +select (ceil(random()*999))::integer as id, random(), random() as data +from generate_series(0,5000); +insert INTO check_test_div_1 +select (ceil(random()*999)+1000)::integer as id, random(), random() as data +from generate_series(0,5000); +insert INTO check_test_div_2 +select (ceil(random()*999)+2000)::integer as id, random(), random() as data +from generate_series(0,5000); +commit; +-- CREATE table for verifying +create temp table test_appended ( +data_x float8, +data_y float8, +num integer +); +begin; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from only check_test_div join inner_t on check_test_div.id = inner_t.id; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_0 join inner_t on check_test_div_0.id = inner_t.id; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_1 join inner_t on check_test_div_1.id = inner_t.id; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_2 join inner_t on check_test_div_2.id = inner_t.id; +commit; +set enable_hashjoin to on; +set enable_mergejoin to off; +set enable_nestloop to off; +-- +-- Check plan +-- +explain (costs off) +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id; + QUERY PLAN +------------------------------------------------------------- + Append + -> Hash Join + Hash Cond: (inner_t.id = check_test_div.id) + -> Seq Scan on inner_t + -> Hash + -> Seq Scan on check_test_div + -> Hash Join + Hash Cond: (check_test_div_0.id = inner_t.id) + -> Seq Scan on check_test_div_0 + -> Hash + -> Seq Scan on inner_t + Filter: (id < 1000) + -> Hash Join + Hash Cond: (check_test_div_1.id = inner_t.id) + -> Seq Scan on check_test_div_1 + -> Hash + -> Seq Scan on inner_t + Filter: ((id >= 1000) AND (id <= 1999)) + -> Hash Join + Hash Cond: (check_test_div_2.id = inner_t.id) + -> Seq Scan on check_test_div_2 + -> Hash + -> Seq Scan on inner_t + Filter: (id > 1999) +(24 rows) + +-- +-- Verify its results +-- +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +except (select * from test_appended); + data_x | data_y | num +--------+--------+----- +(0 rows) + +select * from test_appended +except ( +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +); + data_x | data_y | num +--------+--------+----- +(0 rows) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 3987b4c..eb2ee84 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -60,7 +60,7 @@ test: create_index create_view # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes +test: create_aggregate create_function_3 create_cast constraints triggers inherit append_pullup create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 379f272..ec37de3 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -67,6 +67,7 @@ test: create_cast test: constraints test: triggers test: inherit +test: append_pullup test: create_table_like test: typed_table test: vacuum diff --git a/src/test/regress/sql/append_pullup.sql b/src/test/regress/sql/append_pullup.sql new file mode 100644 index 0000000..51a8606 --- /dev/null +++ b/src/test/regress/sql/append_pullup.sql @@ -0,0 +1,172 @@ +-- +-- Append pull-up across Join +-- + +-- +-- Build a table for testing +-- +-- CREATE Partition Table (Modulation is used for dividing) +create temp table check_test_div ( +id integer, +data_x float8, +data_y float8 +); + +create temp table check_test_div_0 ( +check(id % 3 = 0) +) inherits(check_test_div); + +create temp table check_test_div_1 ( +check(id % 3 = 1) +) inherits(check_test_div); + +create temp table check_test_div_2 ( +check(id % 3 = 2) +) inherits(check_test_div); + +-- CREATE table for inner relation +create temp table inner_t as +select generate_series(0,3000)::integer as id, ceil(random()*10000)::integer as num; + +begin; + +insert INTO check_test_div_0 +select (ceil(random()*1000)*3)::integer as id, random(), random() as data +from generate_series(0,5000); + +insert INTO check_test_div_1 +select (ceil(random()*1000)*3+1)::integer as id, random(), random() as data +from generate_series(0,5000); + +insert INTO check_test_div_2 +select (ceil(random()*1000)*3+2)::integer as id, random(), random() as data +from generate_series(0,5000); + +commit; + +-- CREATE table for verifying +create temp table test_appended ( +data_x float8, +data_y float8, +num integer +); + +begin; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from only check_test_div join inner_t on check_test_div.id = inner_t.id; + +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_0 join inner_t on check_test_div_0.id = inner_t.id; + +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_1 join inner_t on check_test_div_1.id = inner_t.id; + +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_2 join inner_t on check_test_div_2.id = inner_t.id; +commit; + +set enable_hashjoin to on; +set enable_mergejoin to off; +set enable_nestloop to off; + +-- +-- Check plan +-- +explain (costs off) +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id; + +-- +-- Verify its results +-- +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +except (select * from test_appended); + +select * from test_appended +except ( +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +); + +drop table check_test_div cascade; +drop table test_appended; + +-- +-- Build a table for testing +-- +-- CREATE Partition Table (Simple; Greater-than/Less-than marks are used for dividing) +create temp table check_test_div ( +id integer, +data_x float8, +data_y float8 +); + +create temp table check_test_div_0 ( +check(id < 1000) +) inherits(check_test_div); + +create temp table check_test_div_1 ( +check(id between 1000 and 1999) +) inherits(check_test_div); + +create temp table check_test_div_2 ( +check(id > 1999) +) inherits(check_test_div); + +-- Table for inner relation is already created. + +begin; + +insert INTO check_test_div_0 +select (ceil(random()*999))::integer as id, random(), random() as data +from generate_series(0,5000); + +insert INTO check_test_div_1 +select (ceil(random()*999)+1000)::integer as id, random(), random() as data +from generate_series(0,5000); + +insert INTO check_test_div_2 +select (ceil(random()*999)+2000)::integer as id, random(), random() as data +from generate_series(0,5000); + +commit; + +-- CREATE table for verifying +create temp table test_appended ( +data_x float8, +data_y float8, +num integer +); + +begin; +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from only check_test_div join inner_t on check_test_div.id = inner_t.id; + +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_0 join inner_t on check_test_div_0.id = inner_t.id; + +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_1 join inner_t on check_test_div_1.id = inner_t.id; + +insert into test_appended (data_x, data_y, num) +select data_x, data_y, num from check_test_div_2 join inner_t on check_test_div_2.id = inner_t.id; +commit; + +set enable_hashjoin to on; +set enable_mergejoin to off; +set enable_nestloop to off; + +-- +-- Check plan +-- +explain (costs off) +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id; + +-- +-- Verify its results +-- +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +except (select * from test_appended); + +select * from test_appended +except ( +select data_x, data_y, num from check_test_div join inner_t on check_test_div.id = inner_t.id +);