CREATE SCHEMA recursive_dml_with_different_planner_executors;
SET search_path TO recursive_dml_with_different_planner_executors, public;
CREATE TABLE distributed_table (tenant_id text, dept int, info jsonb);
SELECT create_distributed_table('distributed_table', 'tenant_id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE TABLE second_distributed_table (tenant_id text, dept int, info jsonb);
SELECT create_distributed_table('second_distributed_table', 'tenant_id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE TABLE reference_table (id text, name text);
SELECT create_reference_table('reference_table');
 create_reference_table
---------------------------------------------------------------------

(1 row)

INSERT INTO distributed_table SELECT i::text, i % 10, row_to_json(row(i, i*i)) FROM generate_series (0, 100) i;
INSERT INTO second_distributed_table SELECT i::text, i % 10, row_to_json(row(i, i*i)) FROM generate_series (0, 100) i;
SET client_min_messages TO DEBUG1;
-- subquery with router planner
-- joined with a real-time query
UPDATE
	distributed_table
SET dept = foo.dept FROM
	(SELECT tenant_id, dept FROM second_distributed_table WHERE dept = 1 ) as foo,
	(SELECT tenant_id FROM second_distributed_table WHERE dept IN (1, 2, 3, 4) OFFSET 0) as bar
	WHERE foo.tenant_id = bar.tenant_id
	AND distributed_table.tenant_id = bar.tenant_id;
DEBUG:  generating subplan XXX_1 for subquery SELECT tenant_id FROM recursive_dml_with_different_planner_executors.second_distributed_table WHERE (dept OPERATOR(pg_catalog.=) ANY (ARRAY[1, 2, 3, 4])) OFFSET 0
DEBUG:  Plan XXX query after replacing subqueries and CTEs: UPDATE recursive_dml_with_different_planner_executors.distributed_table SET dept = foo.dept FROM (SELECT second_distributed_table.tenant_id, second_distributed_table.dept FROM recursive_dml_with_different_planner_executors.second_distributed_table WHERE (second_distributed_table.dept OPERATOR(pg_catalog.=) 1)) foo, (SELECT intermediate_result.tenant_id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(tenant_id text)) bar WHERE ((foo.tenant_id OPERATOR(pg_catalog.=) bar.tenant_id) AND (distributed_table.tenant_id OPERATOR(pg_catalog.=) bar.tenant_id))
-- a non colocated subquery inside the UPDATE
UPDATE distributed_table SET dept = foo.max_dept FROM
(
	SELECT
		max(dept) as max_dept
	FROM
		(SELECT DISTINCT tenant_id, dept FROM distributed_table) as distributed_table
	WHERE tenant_id NOT IN
				(SELECT tenant_id FROM second_distributed_table WHERE dept IN (1, 2, 3, 4))
) as  foo WHERE foo.max_dept > dept * 3;
DEBUG:  generating subplan XXX_1 for subquery SELECT tenant_id FROM recursive_dml_with_different_planner_executors.second_distributed_table WHERE (dept OPERATOR(pg_catalog.=) ANY (ARRAY[1, 2, 3, 4]))
DEBUG:  generating subplan XXX_2 for subquery SELECT max(dept) AS max_dept FROM (SELECT DISTINCT distributed_table_1.tenant_id, distributed_table_1.dept FROM recursive_dml_with_different_planner_executors.distributed_table distributed_table_1) distributed_table WHERE (NOT (tenant_id OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.tenant_id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(tenant_id text))))
DEBUG:  Plan XXX query after replacing subqueries and CTEs: UPDATE recursive_dml_with_different_planner_executors.distributed_table SET dept = foo.max_dept FROM (SELECT intermediate_result.max_dept FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(max_dept integer)) foo WHERE (foo.max_dept OPERATOR(pg_catalog.>) (distributed_table.dept OPERATOR(pg_catalog.*) 3))
-- subquery with repartition query
SET citus.enable_repartition_joins to ON;
UPDATE distributed_table SET dept = foo.some_tenants::int FROM
(
	SELECT
	 	DISTINCT second_distributed_table.tenant_id as some_tenants
	 FROM second_distributed_table, distributed_table WHERE second_distributed_table.dept = distributed_table.dept
) as foo;
DEBUG:  generating subplan XXX_1 for subquery SELECT DISTINCT second_distributed_table.tenant_id AS some_tenants FROM recursive_dml_with_different_planner_executors.second_distributed_table, recursive_dml_with_different_planner_executors.distributed_table WHERE (second_distributed_table.dept OPERATOR(pg_catalog.=) distributed_table.dept)
DEBUG:  Plan XXX query after replacing subqueries and CTEs: UPDATE recursive_dml_with_different_planner_executors.distributed_table SET dept = (foo.some_tenants)::integer FROM (SELECT intermediate_result.some_tenants FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(some_tenants text)) foo
SET citus.enable_repartition_joins to OFF;
-- final query is router
UPDATE distributed_table SET dept = foo.max_dept FROM
(
	SELECT
		max(dept) as max_dept
	FROM
		(SELECT DISTINCT tenant_id, dept FROM distributed_table) as distributed_table
	WHERE tenant_id IN
				(SELECT tenant_id FROM second_distributed_table WHERE dept IN (1, 2, 3, 4))
) as  foo WHERE foo.max_dept >= dept and tenant_id = '8';
DEBUG:  generating subplan XXX_1 for subquery SELECT max(dept) AS max_dept FROM (SELECT DISTINCT distributed_table_1.tenant_id, distributed_table_1.dept FROM recursive_dml_with_different_planner_executors.distributed_table distributed_table_1) distributed_table WHERE (tenant_id OPERATOR(pg_catalog.=) ANY (SELECT second_distributed_table.tenant_id FROM recursive_dml_with_different_planner_executors.second_distributed_table WHERE (second_distributed_table.dept OPERATOR(pg_catalog.=) ANY (ARRAY[1, 2, 3, 4]))))
DEBUG:  Plan XXX query after replacing subqueries and CTEs: UPDATE recursive_dml_with_different_planner_executors.distributed_table SET dept = foo.max_dept FROM (SELECT intermediate_result.max_dept FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(max_dept integer)) foo WHERE ((foo.max_dept OPERATOR(pg_catalog.>=) distributed_table.dept) AND (distributed_table.tenant_id OPERATOR(pg_catalog.=) '8'::text))
RESET client_min_messages;
DROP SCHEMA recursive_dml_with_different_planner_executors CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table distributed_table
drop cascades to table second_distributed_table
drop cascades to table reference_table
SET search_path TO public;