CREATE SCHEMA fast_path_router_select;
SET search_path TO fast_path_router_select;
SET citus.next_shard_id TO 1840000;
-- all the tests in this file is intended for testing fast-path
-- router planner, so we're explicitly enabling itin this file.
-- We've bunch of other tests that triggers non-fast-path-router
-- planner (note this is already true by default)
SET citus.enable_fast_path_router_planner TO true;
-- ===================================================================
-- test router planner functionality for via fast path on
-- single shard select queries
-- ===================================================================
CREATE TABLE articles_hash (
	id bigint NOT NULL,
	author_id bigint NOT NULL,
	title varchar(20) NOT NULL,
	word_count integer
);
CREATE TABLE articles_range (
	id bigint NOT NULL,
	author_id bigint NOT NULL,
	title varchar(20) NOT NULL,
	word_count integer
);
CREATE TABLE articles_append (
	id bigint NOT NULL,
	author_id bigint NOT NULL,
	title varchar(20) NOT NULL,
	word_count integer
);
-- Check for the existence of line 'DEBUG:  Creating router plan'
-- to determine if router planner is used.
-- this table is used in a CTE test
CREATE TABLE authors_hash ( name varchar(20), id bigint );
CREATE TABLE authors_range ( name varchar(20), id bigint );
SET citus.shard_replication_factor TO 1;
SET citus.shard_count TO 2;
SELECT create_distributed_table('articles_hash', 'author_id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

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

(1 row)

-- create a bunch of test data
INSERT INTO articles_hash VALUES (1,  1, 'arsenous', 9572), (2,  2, 'abducing', 13642),( 3,  3, 'asternal', 10480),( 4,  4, 'altdorfer', 14551),( 5,  5, 'aruru', 11389),
								 (6,  6, 'atlases', 15459),(7,  7, 'aseptic', 12298),( 8,  8, 'agatized', 16368),(9,  9, 'alligate', 438),
								 (10, 10, 'aggrandize', 17277),(11,  1, 'alamo', 1347),(12,  2, 'archiblast', 18185),
								 (13,  3, 'aseyev', 2255),(14,  4, 'andesite', 19094),(15,  5, 'adversa', 3164),
								 (16,  6, 'allonym', 2),(17,  7, 'auriga', 4073),(18,  8, 'assembly', 911),(19,  9, 'aubergiste', 4981),
								 (20, 10, 'absentness', 1820),(21,  1, 'arcading', 5890),(22,  2, 'antipope', 2728),(23,  3, 'abhorring', 6799),
								 (24,  4, 'audacious', 3637),(25,  5, 'antehall', 7707),(26,  6, 'abington', 4545),(27,  7, 'arsenous', 8616),
								 (28,  8, 'aerophyte', 5454),(29,  9, 'amateur', 9524),(30, 10, 'andelee', 6363),(31,  1, 'athwartships', 7271),
								 (32,  2, 'amazon', 11342),(33,  3, 'autochrome', 8180),(34,  4, 'amnestied', 12250),(35,  5, 'aminate', 9089),
								 (36,  6, 'ablation', 13159),(37,  7, 'archduchies', 9997),(38,  8, 'anatine', 14067),(39,  9, 'anchises', 10906),
								 (40, 10, 'attemper', 14976),(41,  1, 'aznavour', 11814),(42,  2, 'ausable', 15885),(43,  3, 'affixal', 12723),
								 (44,  4, 'anteport', 16793),(45,  5, 'afrasia', 864),(46,  6, 'atlanta', 17702),(47,  7, 'abeyance', 1772),
								 (48,  8, 'alkylic', 18610),(49,  9, 'anyone', 2681),(50, 10, 'anjanette', 19519);
SET client_min_messages TO 'DEBUG2';
-- test simple select for a single row
SELECT * FROM articles_hash WHERE author_id = 10 AND id = 50;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 10
 id | author_id |   title   | word_count
---------------------------------------------------------------------
 50 |        10 | anjanette |      19519
(1 row)

-- get all titles by a single author
SELECT title FROM articles_hash WHERE author_id = 10;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 10
   title
---------------------------------------------------------------------
 aggrandize
 absentness
 andelee
 attemper
 anjanette
(5 rows)

-- try ordering them by word count
SELECT title, word_count FROM articles_hash
	WHERE author_id = 10
	ORDER BY word_count DESC NULLS LAST;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 10
   title    | word_count
---------------------------------------------------------------------
 anjanette  |      19519
 aggrandize |      17277
 attemper   |      14976
 andelee    |       6363
 absentness |       1820
(5 rows)

-- look at last two articles by an author
SELECT title, id FROM articles_hash
	WHERE author_id = 5
	ORDER BY id
	LIMIT 2;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 5
  title  | id
---------------------------------------------------------------------
 aruru   |  5
 adversa | 15
(2 rows)

-- find all articles by two authors in same shard
-- but plan is not fast path router plannable due to
-- two distribution columns in the query
SELECT title, author_id FROM articles_hash
	WHERE author_id = 7 OR author_id = 8
	ORDER BY author_id ASC, id;
DEBUG:  Creating router plan
    title    | author_id
---------------------------------------------------------------------
 aseptic     |         7
 auriga      |         7
 arsenous    |         7
 archduchies |         7
 abeyance    |         7
 agatized    |         8
 assembly    |         8
 aerophyte   |         8
 anatine     |         8
 alkylic     |         8
(10 rows)

-- having clause is supported if it goes to a single shard
-- and single dist. key on the query
SELECT author_id, sum(word_count) AS corpus_size FROM articles_hash
	WHERE author_id = 1
	GROUP BY author_id
	HAVING sum(word_count) > 1000
	ORDER BY sum(word_count) DESC;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 author_id | corpus_size
---------------------------------------------------------------------
         1 |       35894
(1 row)

-- fast path planner only support = operator
SELECT * FROM articles_hash WHERE author_id <= 1;
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

SELECT * FROM articles_hash WHERE author_id IN (1, 3);
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
  3 |         3 | asternal     |      10480
 11 |         1 | alamo        |       1347
 13 |         3 | aseyev       |       2255
 21 |         1 | arcading     |       5890
 23 |         3 | abhorring    |       6799
 31 |         1 | athwartships |       7271
 33 |         3 | autochrome   |       8180
 41 |         1 | aznavour     |      11814
 43 |         3 | affixal      |      12723
(10 rows)

-- queries with CTEs cannot go through fast-path planning
WITH first_author AS ( SELECT id FROM articles_hash WHERE author_id = 1)
SELECT * FROM first_author;
DEBUG:  CTE first_author is going to be inlined via distributed planning
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id
---------------------------------------------------------------------
  1
 11
 21
 31
 41
(5 rows)

-- two CTE joins also cannot go through fast-path planning
WITH id_author AS ( SELECT id, author_id FROM articles_hash WHERE author_id = 1),
id_title AS (SELECT id, title from articles_hash WHERE author_id = 1)
SELECT * FROM id_author, id_title WHERE id_author.id = id_title.id;
DEBUG:  CTE id_author is going to be inlined via distributed planning
DEBUG:  CTE id_title is going to be inlined via distributed planning
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id | id |    title
---------------------------------------------------------------------
  1 |         1 |  1 | arsenous
 11 |         1 | 11 | alamo
 21 |         1 | 21 | arcading
 31 |         1 | 31 | athwartships
 41 |         1 | 41 | aznavour
(5 rows)

-- this is a different case where each CTE is recursively planned and those goes
-- through the fast-path router planner, but the top level join is not
WITH id_author AS ( SELECT id, author_id FROM articles_hash WHERE author_id = 1),
id_title AS (SELECT id, title from articles_hash WHERE author_id = 2)
SELECT * FROM id_author, id_title WHERE id_author.id = id_title.id;
DEBUG:  CTE id_author is going to be inlined via distributed planning
DEBUG:  CTE id_title is going to be inlined via distributed planning
DEBUG:  cannot run command which targets multiple shards
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 2
DEBUG:  generating subplan XXX_1 for subquery SELECT id, title FROM fast_path_router_select.articles_hash WHERE (author_id OPERATOR(pg_catalog.=) 2)
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT id_author.id, id_author.author_id, id_title.id, id_title.title FROM (SELECT articles_hash.id, articles_hash.author_id FROM fast_path_router_select.articles_hash WHERE (articles_hash.author_id OPERATOR(pg_catalog.=) 1)) id_author, (SELECT intermediate_result.id, intermediate_result.title FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id bigint, title character varying(20))) id_title WHERE (id_author.id OPERATOR(pg_catalog.=) id_title.id)
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id | id | title
---------------------------------------------------------------------
(0 rows)

CREATE TABLE company_employees (company_id int, employee_id int, manager_id int);
SET citus.shard_replication_factor TO 1;
SELECT create_distributed_table('company_employees', 'company_id', 'hash');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO company_employees values(1, 1, 0);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
INSERT INTO company_employees values(1, 2, 1);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
INSERT INTO company_employees values(1, 3, 1);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
INSERT INTO company_employees values(1, 4, 2);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
INSERT INTO company_employees values(1, 5, 4);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
INSERT INTO company_employees values(3, 1, 0);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 3
INSERT INTO company_employees values(3, 15, 1);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 3
INSERT INTO company_employees values(3, 3, 1);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 3
-- recursive CTEs are also cannot go through fast
-- path planning
WITH RECURSIVE hierarchy as (
	SELECT *, 1 AS level
		FROM company_employees
		WHERE company_id = 1 and manager_id = 0
	UNION
	SELECT ce.*, (h.level+1)
		FROM hierarchy h JOIN company_employees ce
			ON (h.employee_id = ce.manager_id AND
				h.company_id = ce.company_id AND
				ce.company_id = 1))
SELECT * FROM hierarchy WHERE LEVEL <= 2;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 company_id | employee_id | manager_id | level
---------------------------------------------------------------------
          1 |           1 |          0 |     1
          1 |           2 |          1 |     2
          1 |           3 |          1 |     2
(3 rows)

WITH update_article AS (
    UPDATE articles_hash SET word_count = 10 WHERE id = 1 AND word_count = 9 RETURNING *
)
SELECT * FROM update_article;
DEBUG:  Router planner cannot handle multi-shard select queries
DEBUG:  generating subplan XXX_1 for CTE update_article: UPDATE fast_path_router_select.articles_hash SET word_count = 10 WHERE ((id OPERATOR(pg_catalog.=) 1) AND (word_count OPERATOR(pg_catalog.=) 9)) RETURNING id, author_id, title, word_count
DEBUG:  Creating router plan
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT id, author_id, title, word_count FROM (SELECT intermediate_result.id, intermediate_result.author_id, intermediate_result.title, intermediate_result.word_count FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id bigint, author_id bigint, title character varying(20), word_count integer)) update_article
DEBUG:  Creating router plan
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

WITH delete_article AS (
    DELETE FROM articles_hash WHERE id = 1 AND word_count = 10 RETURNING *
)
SELECT * FROM delete_article;
DEBUG:  Router planner cannot handle multi-shard select queries
DEBUG:  generating subplan XXX_1 for CTE delete_article: DELETE FROM fast_path_router_select.articles_hash WHERE ((id OPERATOR(pg_catalog.=) 1) AND (word_count OPERATOR(pg_catalog.=) 10)) RETURNING id, author_id, title, word_count
DEBUG:  Creating router plan
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT id, author_id, title, word_count FROM (SELECT intermediate_result.id, intermediate_result.author_id, intermediate_result.title, intermediate_result.word_count FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id bigint, author_id bigint, title character varying(20), word_count integer)) delete_article
DEBUG:  Creating router plan
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- grouping sets are supported via fast-path
SELECT
	id, substring(title, 2, 1) AS subtitle, count(*)
	FROM articles_hash
	WHERE author_id = 1
	GROUP BY GROUPING SETS ((id),(subtitle))
	ORDER BY id, subtitle;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | subtitle | count
---------------------------------------------------------------------
  1 |          |     1
 11 |          |     1
 21 |          |     1
 31 |          |     1
 41 |          |     1
    | l        |     1
    | r        |     2
    | t        |     1
    | z        |     1
(9 rows)

-- grouping sets are not supported with multiple quals
SELECT
	id, substring(title, 2, 1) AS subtitle, count(*)
	FROM articles_hash
	WHERE author_id = 1 or author_id = 2
	GROUP BY GROUPING SETS ((id),(subtitle))
	ORDER BY id, subtitle;
DEBUG:  Router planner cannot handle multi-shard select queries
ERROR:  could not run distributed query with GROUPING SETS, CUBE, or ROLLUP
HINT:  Consider using an equality filter on the distributed table's partition column.
-- queries which involve functions in FROM clause are not supported via fast path planning
SELECT * FROM articles_hash, position('om' in 'Thomas') WHERE author_id = 1;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count | position
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572 |        3
 11 |         1 | alamo        |       1347 |        3
 21 |         1 | arcading     |       5890 |        3
 31 |         1 | athwartships |       7271 |        3
 41 |         1 | aznavour     |      11814 |        3
(5 rows)

-- sublinks are not supported via fast path planning
SELECT * FROM articles_hash
WHERE author_id IN (SELECT author_id FROM articles_hash WHERE author_id = 2)
ORDER BY articles_hash.id;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 2
 id | author_id |   title    | word_count
---------------------------------------------------------------------
  2 |         2 | abducing   |      13642
 12 |         2 | archiblast |      18185
 22 |         2 | antipope   |       2728
 32 |         2 | amazon     |      11342
 42 |         2 | ausable    |      15885
(5 rows)

-- subqueries are not supported via fast path planning
SELECT articles_hash.id,test.word_count
FROM articles_hash, (SELECT id, word_count FROM articles_hash) AS test WHERE test.id = articles_hash.id
ORDER BY test.word_count DESC, articles_hash.id LIMIT 5;
DEBUG:  Router planner cannot handle multi-shard select queries
DEBUG:  Router planner cannot handle multi-shard select queries
DEBUG:  generating subplan XXX_1 for subquery SELECT id, word_count FROM fast_path_router_select.articles_hash
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT articles_hash.id, test.word_count FROM fast_path_router_select.articles_hash, (SELECT intermediate_result.id, intermediate_result.word_count FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id bigint, word_count integer)) test WHERE (test.id OPERATOR(pg_catalog.=) articles_hash.id) ORDER BY test.word_count DESC, articles_hash.id LIMIT 5
DEBUG:  Router planner cannot handle multi-shard select queries
DEBUG:  push down of limit count: 5
 id | word_count
---------------------------------------------------------------------
 50 |      19519
 14 |      19094
 48 |      18610
 12 |      18185
 46 |      17702
(5 rows)

SELECT articles_hash.id,test.word_count
FROM articles_hash, (SELECT id, word_count FROM articles_hash) AS test
WHERE test.id = articles_hash.id and articles_hash.author_id = 1
ORDER BY articles_hash.id;
DEBUG:  Router planner cannot handle multi-shard select queries
DEBUG:  Router planner cannot handle multi-shard select queries
DEBUG:  generating subplan XXX_1 for subquery SELECT id, word_count FROM fast_path_router_select.articles_hash
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT articles_hash.id, test.word_count FROM fast_path_router_select.articles_hash, (SELECT intermediate_result.id, intermediate_result.word_count FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id bigint, word_count integer)) test WHERE ((test.id OPERATOR(pg_catalog.=) articles_hash.id) AND (articles_hash.author_id OPERATOR(pg_catalog.=) 1)) ORDER BY articles_hash.id
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

-- subqueries are not supported in SELECT clause
SELECT a.title AS name, (SELECT a2.id FROM articles_hash a2 WHERE a.id = a2.id  LIMIT 1)
						 AS special_price FROM articles_hash a;
DEBUG:  Router planner cannot handle multi-shard select queries
ERROR:  complex joins are only supported when all distributed tables are co-located and joined on their distribution columns
-- simple lookup query just works
SELECT *
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- below query hits a single shard but with multiple filters
-- so cannot go via fast-path
SELECT *
	FROM articles_hash
	WHERE author_id = 1 OR author_id = 17;
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- rename the output columns
SELECT id as article_id, word_count * id as random_value
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 article_id | random_value
---------------------------------------------------------------------
          1 |         9572
         11 |        14817
         21 |       123690
         31 |       225401
         41 |       484374
(5 rows)

-- joins do not go through fast-path planning
SELECT a.author_id as first_author, b.word_count as second_word_count
	FROM articles_hash a, articles_hash b
	WHERE a.author_id = 10 and a.author_id = b.author_id
	LIMIT 3;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 10
 first_author | second_word_count
---------------------------------------------------------------------
           10 |             17277
           10 |              1820
           10 |              6363
(3 rows)

-- single shard select with limit goes through fast-path planning
SELECT *
	FROM articles_hash
	WHERE author_id = 1
	LIMIT 3;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |  title   | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous |       9572
 11 |         1 | alamo    |       1347
 21 |         1 | arcading |       5890
(3 rows)

-- single shard select with limit + offset goes through fast-path planning
SELECT *
	FROM articles_hash
	WHERE author_id = 1
	LIMIT 2
	OFFSET 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |  title   | word_count
---------------------------------------------------------------------
 11 |         1 | alamo    |       1347
 21 |         1 | arcading |       5890
(2 rows)

-- single shard select with limit + offset + order by goes through fast-path planning
SELECT *
	FROM articles_hash
	WHERE author_id = 1
	ORDER BY id desc
	LIMIT 2
	OFFSET 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
 31 |         1 | athwartships |       7271
 21 |         1 | arcading     |       5890
(2 rows)

-- single shard select with group by on non-partition column goes through fast-path planning
SELECT id
	FROM articles_hash
	WHERE author_id = 1
	GROUP BY id
	ORDER BY id;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id
---------------------------------------------------------------------
  1
 11
 21
 31
 41
(5 rows)

-- single shard select with distinct goes through fast-path planning
SELECT DISTINCT id
	FROM articles_hash
	WHERE author_id = 1
	ORDER BY id;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id
---------------------------------------------------------------------
  1
 11
 21
 31
 41
(5 rows)

-- single shard aggregate goes through fast-path planning
SELECT avg(word_count)
	FROM articles_hash
	WHERE author_id = 2;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 2
        avg
---------------------------------------------------------------------
 12356.400000000000
(1 row)

-- max, min, sum, count goes through fast-path planning
SELECT max(word_count) as max, min(word_count) as min,
	   sum(word_count) as sum, count(word_count) as cnt
	FROM articles_hash
	WHERE author_id = 2;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 2
  max  | min  |  sum  | cnt
---------------------------------------------------------------------
 18185 | 2728 | 61782 |   5
(1 row)

-- queries with aggregates and group by goes through fast-path planning
SELECT max(word_count)
	FROM articles_hash
	WHERE author_id = 1
	GROUP BY author_id;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
  max
---------------------------------------------------------------------
 11814
(1 row)

-- set operations are not supported via fast-path planning
SELECT * FROM (
	SELECT * FROM articles_hash WHERE author_id = 1
	UNION
	SELECT * FROM articles_hash WHERE author_id = 3
) AS combination
ORDER BY id;
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
  3 |         3 | asternal     |      10480
 11 |         1 | alamo        |       1347
 13 |         3 | aseyev       |       2255
 21 |         1 | arcading     |       5890
 23 |         3 | abhorring    |       6799
 31 |         1 | athwartships |       7271
 33 |         3 | autochrome   |       8180
 41 |         1 | aznavour     |      11814
 43 |         3 | affixal      |      12723
(10 rows)

-- function calls in the target list is supported via fast path
SELECT LEFT(title, 1) FROM articles_hash WHERE author_id = 1
-- top-level union queries are supported through recursive planning
SET client_min_messages to 'NOTICE';
ERROR:  syntax error at or near "SET"
-- unions in subqueries are not supported via fast-path planning
SELECT * FROM (
	(SELECT * FROM articles_hash WHERE author_id = 1)
	UNION
	(SELECT * FROM articles_hash WHERE author_id = 1)) uu
ORDER BY 1, 2
LIMIT 5;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- Test various filtering options for router plannable check
SET client_min_messages to 'DEBUG2';
-- cannot go through fast-path if there is
-- explicit coercion
SELECT *
	FROM articles_hash
	WHERE author_id = 1::bigint;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- can go through fast-path if there is
-- implicit coercion
-- This doesn't work see the related issue
-- reported https://github.com/citusdata/citus/issues/2605
-- SELECT *
--	FROM articles_hash
--	WHERE author_id = 1.0;
SELECT *
	FROM articles_hash
	WHERE author_id = 68719476736; -- this is bigint
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 68719476736
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- cannot go through fast-path due to
-- multiple filters on the dist. key
SELECT *
	FROM articles_hash
	WHERE author_id = 1 and author_id >= 1;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- cannot go through fast-path due to
-- multiple filters on the dist. key
SELECT *
	FROM articles_hash
	WHERE author_id = 1 or id = 1;
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- goes through fast-path planning because
-- the dist. key is ANDed with the rest of the
-- filters
SELECT *
	FROM articles_hash
	WHERE author_id = 1 and (id = 1 or id = 41);
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |  title   | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous |       9572
 41 |         1 | aznavour |      11814
(2 rows)

-- this time there is an OR clause which prevents
-- router planning at all
SELECT *
	FROM articles_hash
	WHERE author_id = 1 and id = 1 or id = 41;
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id |  title   | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous |       9572
 41 |         1 | aznavour |      11814
(2 rows)

-- goes through fast-path planning because
-- the dist. key is ANDed with the rest of the
-- filters
SELECT *
	FROM articles_hash
	WHERE author_id = 1 and (id = random()::int  * 0);
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- not router plannable due to function call on the right side
SELECT *
	FROM articles_hash
	WHERE author_id = (random()::int  * 0 + 1);
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- Citus does not qualify this as a fast-path because
-- dist_key = func()
SELECT *
	FROM articles_hash
	WHERE author_id = abs(-1);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- Citus does not qualify this as a fast-path because
-- dist_key = func()
SELECT *
	FROM articles_hash
	WHERE 1 = abs(author_id);
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- Citus does not qualify this as a fast-path because
-- dist_key = func()
SELECT *
	FROM articles_hash
	WHERE author_id = abs(author_id - 2);
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- the function is not on the dist. key, so qualify as
-- fast-path
SELECT *
	FROM articles_hash
	WHERE author_id = 1 and (id = abs(id - 2));
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |  title   | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous |       9572
(1 row)

-- not router plannable due to is true
SELECT *
	FROM articles_hash
	WHERE (author_id = 1) is true;
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- router plannable, (boolean expression) = true is collapsed to (boolean expression)
SELECT *
	FROM articles_hash
	WHERE (author_id = 1) = true;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- some more complex quals
SELECT count(*) FROM articles_hash WHERE (author_id = 15) AND (id = 1 OR word_count > 5);
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 15
 count
---------------------------------------------------------------------
     0
(1 row)

SELECT count(*) FROM articles_hash WHERE (author_id = 15) OR (id = 1 AND word_count > 5);
DEBUG:  Router planner cannot handle multi-shard select queries
 count
---------------------------------------------------------------------
     1
(1 row)

SELECT count(*) FROM articles_hash WHERE (id = 15) OR (author_id = 1 AND word_count > 5);
DEBUG:  Router planner cannot handle multi-shard select queries
 count
---------------------------------------------------------------------
     6
(1 row)

SELECT count(*) FROM articles_hash WHERE (id = 15) AND (author_id = 1 OR word_count > 5);
DEBUG:  Router planner cannot handle multi-shard select queries
 count
---------------------------------------------------------------------
     1
(1 row)

SELECT count(*) FROM articles_hash WHERE (id = 15) AND (author_id = 1 AND (word_count > 5 OR id  = 2));
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 count
---------------------------------------------------------------------
     0
(1 row)

SELECT count(*) FROM articles_hash WHERE (id = 15) AND (title ilike 'a%' AND (word_count > 5 OR author_id  = 2));
DEBUG:  Router planner cannot handle multi-shard select queries
 count
---------------------------------------------------------------------
     1
(1 row)

SELECT count(*) FROM articles_hash WHERE (id = 15) AND (title ilike 'a%' AND (word_count > 5 AND author_id  = 2));
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 2
 count
---------------------------------------------------------------------
     0
(1 row)

SELECT count(*) FROM articles_hash WHERE (id = 15) AND (title ilike 'a%' AND ((word_count > 5 OR title ilike 'b%' ) AND (author_id  = 2 AND word_count > 50)));
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 2
 count
---------------------------------------------------------------------
     0
(1 row)

-- fast-path router plannable, between operator is on another column
SELECT *
	FROM articles_hash
	WHERE (author_id = 1) and id between 0 and 20;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |  title   | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous |       9572
 11 |         1 | alamo    |       1347
(2 rows)

-- fast-path router plannable, partition column expression is and'ed to rest
SELECT *
	FROM articles_hash
	WHERE (author_id = 1) and (id = 1 or id = 31) and title like '%s';
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 31 |         1 | athwartships |       7271
(2 rows)

-- fast-path router plannable, order is changed
SELECT *
	FROM articles_hash
	WHERE (id = 1 or id = 31) and title like '%s' and (author_id = 1);
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 31 |         1 | athwartships |       7271
(2 rows)

-- fast-path router plannable
SELECT *
	FROM articles_hash
	WHERE (title like '%s' or title like 'a%') and (author_id = 1);
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- fast-path router plannable
SELECT *
	FROM articles_hash
	WHERE (title like '%s' or title like 'a%') and (author_id = 1) and (word_count < 3000 or word_count > 8000);
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |  title   | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous |       9572
 11 |         1 | alamo    |       1347
 41 |         1 | aznavour |      11814
(3 rows)

-- window functions are supported with fast-path router plannable
SELECT LAG(title, 1) over (ORDER BY word_count) prev, title, word_count
	FROM articles_hash
	WHERE author_id = 5;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 5
   prev   |  title   | word_count
---------------------------------------------------------------------
          | afrasia  |        864
 afrasia  | adversa  |       3164
 adversa  | antehall |       7707
 antehall | aminate  |       9089
 aminate  | aruru    |      11389
(5 rows)

SELECT LAG(title, 1) over (ORDER BY word_count) prev, title, word_count
	FROM articles_hash
	WHERE author_id = 5
	ORDER BY word_count DESC;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 5
   prev   |  title   | word_count
---------------------------------------------------------------------
 aminate  | aruru    |      11389
 antehall | aminate  |       9089
 adversa  | antehall |       7707
 afrasia  | adversa  |       3164
          | afrasia  |        864
(5 rows)

SELECT id, MIN(id) over (order by word_count)
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | min
---------------------------------------------------------------------
 11 |  11
 21 |  11
 31 |  11
  1 |   1
 41 |   1
(5 rows)

SELECT id, word_count, AVG(word_count) over (order by word_count)
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | word_count |          avg
---------------------------------------------------------------------
 11 |       1347 | 1347.0000000000000000
 21 |       5890 | 3618.5000000000000000
 31 |       7271 | 4836.0000000000000000
  1 |       9572 | 6020.0000000000000000
 41 |      11814 | 7178.8000000000000000
(5 rows)

SELECT word_count, rank() OVER (PARTITION BY author_id ORDER BY word_count)
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 word_count | rank
---------------------------------------------------------------------
       1347 |    1
       5890 |    2
       7271 |    3
       9572 |    4
      11814 |    5
(5 rows)

-- some more tests on complex target lists
SELECT DISTINCT ON (author_id, id) author_id, id,
	MIN(id) over (order by avg(word_count)) * AVG(id * 5.2 + (1.0/max(word_count))) over (order by max(word_count)) as t1,
	count(*) FILTER (WHERE title LIKE 'al%') as cnt_with_filter,
	count(*) FILTER (WHERE '0300030' LIKE '%3%') as cnt_with_filter_2,
	avg(case when id > 2 then char_length(word_count::text) * (id * strpos(word_count::text, '1')) end) as case_cnt,
	COALESCE(strpos(avg(word_count)::text, '1'), 20)
	FROM articles_hash as aliased_table
	WHERE author_id = 1
	GROUP BY author_id, id
	HAVING count(DISTINCT title) > 0
	ORDER BY author_id, id, sum(word_count) - avg(char_length(title)) DESC, COALESCE(array_upper(ARRAY[max(id)],1) * 5,0) DESC;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 author_id | id |              t1              | cnt_with_filter | cnt_with_filter_2 |        case_cnt        | coalesce
---------------------------------------------------------------------
         1 |  1 |      83.20028854345579490574 |               0 |                 1 |                        |        0
         1 | 11 |     629.20816629547141796586 |               1 |                 1 |    44.0000000000000000 |        1
         1 | 21 |     915.20501693381380745499 |               0 |                 1 | 0.00000000000000000000 |        0
         1 | 31 |    1201.20384890897723321000 |               0 |                 1 |   496.0000000000000000 |        4
         1 | 41 | 109.200247763831844321405335 |               0 |                 1 |   205.0000000000000000 |        1
(5 rows)

-- where false queries are router plannable but not fast-path
SELECT *
	FROM articles_hash
	WHERE false;
DEBUG:  Creating router plan
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- fast-path with false
SELECT *
	FROM articles_hash
	WHERE author_id = 1 and false;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- fast-path with false
SELECT *
	FROM articles_hash
	WHERE author_id = 1 and 1=0;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

SELECT *
	FROM articles_hash
	WHERE null and author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- we cannot qualify dist_key = X operator Y via
-- fast-path planning
SELECT *
	FROM articles_hash
	WHERE author_id = 1 + 1;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 2
 id | author_id |   title    | word_count
---------------------------------------------------------------------
  2 |         2 | abducing   |      13642
 12 |         2 | archiblast |      18185
 22 |         2 | antipope   |       2728
 32 |         2 | amazon     |      11342
 42 |         2 | ausable    |      15885
(5 rows)

-- where false with immutable function returning false
-- goes through fast-path
SELECT *
	FROM articles_hash a
	WHERE a.author_id = 10 and int4eq(1, 2);
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 10
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- partition_column is null clause does not prune out any shards,
-- all shards remain after shard pruning, not router plannable
-- not fast-path router either
SELECT *
	FROM articles_hash a
	WHERE a.author_id is null;
DEBUG:  Router planner cannot handle multi-shard select queries
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- partition_column equals to null clause prunes out all shards
-- no shards after shard pruning, router plannable
-- not fast-path router either
SELECT *
	FROM articles_hash a
	WHERE a.author_id = null;
DEBUG:  Creating router plan
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- union/difference /intersection with where false
-- this query was not originally router plannable, addition of 1=0
-- makes it router plannable but not fast-path
SELECT * FROM (
	SELECT * FROM articles_hash WHERE author_id = 1
	UNION
	SELECT * FROM articles_hash WHERE author_id = 2 and 1=0
) AS combination
ORDER BY id;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- same with the above, but with WHERE false
SELECT * FROM (
	SELECT * FROM articles_hash WHERE author_id = 1
	UNION
	SELECT * FROM articles_hash WHERE author_id = 2 and 1=0
) AS combination WHERE false
ORDER BY id;
DEBUG:  Creating router plan
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

-- window functions with where false
SELECT word_count, rank() OVER (PARTITION BY author_id ORDER BY word_count)
	FROM articles_hash
	WHERE author_id = 1 and 1=0;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 word_count | rank
---------------------------------------------------------------------
(0 rows)

-- create a dummy function to be used in filtering
SET citus.enable_metadata_sync TO OFF;
CREATE OR REPLACE FUNCTION someDummyFunction(regclass)
    RETURNS text AS
$$
BEGIN
    RETURN md5($1::text);
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
-- we execute the query within a function to consolidate the error messages
-- between different executors
CREATE FUNCTION raise_failed_execution_f_router(query text) RETURNS void AS $$
BEGIN
        EXECUTE query;
        EXCEPTION WHEN OTHERS THEN
        IF SQLERRM LIKE '%failed to execute task%' THEN
                RAISE 'Task failed to execute';
        ELSIF SQLERRM LIKE '%does not exist%' THEN
          RAISE 'Task failed to execute';
        ELSIF SQLERRM LIKE '%could not receive query results%' THEN
          	RAISE 'Task failed to execute';
        END IF;
END;
$$LANGUAGE plpgsql;
RESET citus.enable_metadata_sync;
SET client_min_messages TO ERROR;
\set VERBOSITY terse
-- fast path router plannable, but errors
SELECT raise_failed_execution_f_router($$
	SELECT * FROM articles_hash
		WHERE
			someDummyFunction('articles_hash') = md5('articles_hash') AND author_id = 1
		ORDER BY
			author_id, id
		LIMIT 5;
$$);
ERROR:  Task failed to execute
\set VERBOSITY DEFAULT
SET client_min_messages TO DEFAULT;
-- temporarily turn off debug messages before dropping the function
SET client_min_messages TO 'NOTICE';
DROP FUNCTION someDummyFunction(regclass);
SET client_min_messages TO 'DEBUG2';
-- complex query hitting a single shard and a fast-path
SELECT
	count(DISTINCT CASE
			WHEN
				word_count > 100
			THEN
				id
			ELSE
				NULL
			END) as c
	FROM
		articles_hash
	WHERE
		author_id = 5;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 5
 c
---------------------------------------------------------------------
 5
(1 row)

-- queries inside transactions can be fast-path router plannable
BEGIN;
SELECT *
	FROM articles_hash
	WHERE author_id = 1
	ORDER BY id;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

END;
-- queries inside read-only transactions can be fast-path router plannable
SET TRANSACTION READ ONLY;
WARNING:  SET TRANSACTION can only be used in transaction blocks
SELECT *
	FROM articles_hash
	WHERE author_id = 1
	ORDER BY id;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

END;
WARNING:  there is no transaction in progress
-- cursor queries are fast-path router plannable
BEGIN;
DECLARE test_cursor CURSOR FOR
	SELECT *
		FROM articles_hash
		WHERE author_id = 1
		ORDER BY id;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
FETCH test_cursor;
 id | author_id |  title   | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous |       9572
(1 row)

FETCH ALL test_cursor;
 id | author_id |    title     | word_count
---------------------------------------------------------------------
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(4 rows)

FETCH test_cursor; -- fetch one row after the last
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

FETCH BACKWARD test_cursor;
 id | author_id |  title   | word_count
---------------------------------------------------------------------
 41 |         1 | aznavour |      11814
(1 row)

END;
-- queries inside copy can be router plannable
COPY (
	SELECT *
	FROM articles_hash
	WHERE author_id = 1
	ORDER BY id) TO STDOUT;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
1	1	arsenous	9572
11	1	alamo	1347
21	1	arcading	5890
31	1	athwartships	7271
41	1	aznavour	11814
-- table creation queries inside can be fast-path router plannable
CREATE TEMP TABLE temp_articles_hash as
	SELECT *
	FROM articles_hash
	WHERE author_id = 1
	ORDER BY id;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
-- fast-path router plannable queries may include filter for aggregates
SELECT count(*), count(*) FILTER (WHERE id < 3)
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 count | count
---------------------------------------------------------------------
     5 |     1
(1 row)

-- prepare queries can be router plannable
PREPARE author_1_articles as
	SELECT *
	FROM articles_hash
	WHERE author_id = 1;
EXECUTE author_1_articles;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_1_articles;
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_1_articles;
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_1_articles;
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_1_articles;
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_1_articles;
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- parametric prepare queries can be router plannable
PREPARE author_articles(int) as
	SELECT *
	FROM articles_hash
	WHERE author_id = $1;
EXECUTE author_articles(1);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_articles(1);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_articles(1);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_articles(1);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_articles(1);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_articles(1);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

EXECUTE author_articles(NULL);
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

EXECUTE author_articles(NULL);
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

EXECUTE author_articles(NULL);
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

EXECUTE author_articles(NULL);
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

EXECUTE author_articles(NULL);
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

EXECUTE author_articles(NULL);
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

EXECUTE author_articles(NULL);
 id | author_id | title | word_count
---------------------------------------------------------------------
(0 rows)

PREPARE author_articles_update(int) AS
	UPDATE articles_hash SET title = 'test' WHERE author_id = $1;
EXECUTE author_articles_update(NULL);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
EXECUTE author_articles_update(NULL);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
EXECUTE author_articles_update(NULL);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
EXECUTE author_articles_update(NULL);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
EXECUTE author_articles_update(NULL);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
EXECUTE author_articles_update(NULL);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
EXECUTE author_articles_update(NULL);
-- queries inside plpgsql functions could be router plannable
CREATE OR REPLACE FUNCTION author_articles_max_id() RETURNS int AS $$
DECLARE
  max_id integer;
BEGIN
	SELECT MAX(id) FROM articles_hash ah
		WHERE author_id = 1
		into max_id;
	return max_id;
END;
$$ LANGUAGE plpgsql;
DEBUG:  switching to sequential query execution mode
DETAIL:  A command for a distributed function is run. To make sure subsequent commands see the function correctly we need to make sure to use only one connection for all future commands
-- we don't want too many details. though we're omitting
-- "DETAIL:  distribution column value:", we see it acceptable
-- since the query results verifies the correctness
\set VERBOSITY terse
SELECT author_articles_max_id();
DEBUG:  function does not have co-located tables
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id();
DEBUG:  function does not have co-located tables
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id();
DEBUG:  function does not have co-located tables
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id();
DEBUG:  function does not have co-located tables
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id();
DEBUG:  function does not have co-located tables
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id();
DEBUG:  function does not have co-located tables
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

-- queries inside plpgsql functions could be router plannable
CREATE OR REPLACE FUNCTION author_articles_max_id(int) RETURNS int AS $$
DECLARE
  max_id integer;
BEGIN
	SELECT MAX(id) FROM articles_hash ah
		WHERE author_id = $1
		into max_id;
	return max_id;
END;
$$ LANGUAGE plpgsql;
DEBUG:  switching to sequential query execution mode
SELECT author_articles_max_id(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

SELECT author_articles_max_id(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 author_articles_max_id
---------------------------------------------------------------------
                     41
(1 row)

-- check that function returning setof query are router plannable
CREATE OR REPLACE FUNCTION author_articles_id_word_count() RETURNS TABLE(id bigint, word_count int) AS $$
DECLARE
BEGIN
	RETURN QUERY
		SELECT ah.id, ah.word_count
		FROM articles_hash ah
		WHERE author_id = 1;

END;
$$ LANGUAGE plpgsql;
DEBUG:  switching to sequential query execution mode
SELECT * FROM author_articles_id_word_count();
DEBUG:  function does not have co-located tables
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count();
DEBUG:  function does not have co-located tables
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count();
DEBUG:  function does not have co-located tables
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count();
DEBUG:  function does not have co-located tables
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count();
DEBUG:  function does not have co-located tables
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count();
DEBUG:  function does not have co-located tables
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

-- check that function returning setof query are router plannable
CREATE OR REPLACE FUNCTION author_articles_id_word_count(int) RETURNS TABLE(id bigint, word_count int) AS $$
DECLARE
BEGIN
	RETURN QUERY
		SELECT ah.id, ah.word_count
		FROM articles_hash ah
		WHERE author_id = $1;

END;
$$ LANGUAGE plpgsql;
DEBUG:  switching to sequential query execution mode
SELECT * FROM author_articles_id_word_count(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

SELECT * FROM author_articles_id_word_count(1);
DEBUG:  function does not have co-located tables
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 id | word_count
---------------------------------------------------------------------
  1 |       9572
 11 |       1347
 21 |       5890
 31 |       7271
 41 |      11814
(5 rows)

\set VERBOSITY default
-- insert .. select via coordinator could also
-- use fast-path queries
PREPARE insert_sel(int, int) AS
INSERT INTO articles_hash
	SELECT * FROM articles_hash WHERE author_id = $2 AND word_count = $1 OFFSET 0;
EXECUTE insert_sel(1,1);
DEBUG:  OFFSET clauses are not allowed in distributed INSERT ... SELECT queries
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  Collecting INSERT ... SELECT results on coordinator
EXECUTE insert_sel(1,1);
DEBUG:  OFFSET clauses are not allowed in distributed INSERT ... SELECT queries
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  Collecting INSERT ... SELECT results on coordinator
EXECUTE insert_sel(1,1);
DEBUG:  OFFSET clauses are not allowed in distributed INSERT ... SELECT queries
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  Collecting INSERT ... SELECT results on coordinator
EXECUTE insert_sel(1,1);
DEBUG:  OFFSET clauses are not allowed in distributed INSERT ... SELECT queries
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  Collecting INSERT ... SELECT results on coordinator
EXECUTE insert_sel(1,1);
DEBUG:  OFFSET clauses are not allowed in distributed INSERT ... SELECT queries
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  Collecting INSERT ... SELECT results on coordinator
EXECUTE insert_sel(1,1);
DEBUG:  OFFSET clauses are not allowed in distributed INSERT ... SELECT queries
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  Collecting INSERT ... SELECT results on coordinator
-- one final interesting preperad statement
-- where one of the filters is on the target list
PREPARE fast_path_agg_filter(int, int) AS
	SELECT
		count(*) FILTER (WHERE word_count=$1)
	FROM
		articles_hash
	WHERE author_id = $2;
EXECUTE fast_path_agg_filter(1,1);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 count
---------------------------------------------------------------------
     0
(1 row)

EXECUTE fast_path_agg_filter(2,2);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 count
---------------------------------------------------------------------
     0
(1 row)

EXECUTE fast_path_agg_filter(3,3);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 count
---------------------------------------------------------------------
     0
(1 row)

EXECUTE fast_path_agg_filter(4,4);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 count
---------------------------------------------------------------------
     0
(1 row)

EXECUTE fast_path_agg_filter(5,5);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 count
---------------------------------------------------------------------
     0
(1 row)

EXECUTE fast_path_agg_filter(6,6);
DEBUG:  Deferred pruning for a fast-path router query
DEBUG:  Creating router plan
 count
---------------------------------------------------------------------
     0
(1 row)

-- views internally become subqueries, so not fast-path router query
CREATE VIEW test_view AS
	SELECT * FROM articles_hash WHERE author_id = 1;
SELECT * FROM test_view;
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

-- materialized views can be created for fast-path router plannable queries
CREATE MATERIALIZED VIEW mv_articles_hash_empty AS
	SELECT * FROM articles_hash WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
SELECT * FROM mv_articles_hash_empty;
 id | author_id |    title     | word_count
---------------------------------------------------------------------
  1 |         1 | arsenous     |       9572
 11 |         1 | alamo        |       1347
 21 |         1 | arcading     |       5890
 31 |         1 | athwartships |       7271
 41 |         1 | aznavour     |      11814
(5 rows)

SELECT id
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id
---------------------------------------------------------------------
  1
 11
 21
 31
 41
(5 rows)

INSERT INTO articles_hash VALUES (51, 1, 'amateus', 1814), (52, 1, 'second amateus', 2824);
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
-- verify insert is successfull (not router plannable and executable)
SELECT id
	FROM articles_hash
	WHERE author_id = 1;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 1
 id
---------------------------------------------------------------------
  1
 11
 21
 31
 41
 51
 52
(7 rows)

SET client_min_messages to 'NOTICE';
-- finally, some tests with partitioned tables
CREATE TABLE collections_list (
	key bigint,
	ts timestamptz,
	collection_id integer,
	value numeric
) PARTITION BY LIST (collection_id );
CREATE TABLE collections_list_1
	PARTITION OF collections_list (key, ts, collection_id, value)
	FOR VALUES IN ( 1 );
CREATE TABLE collections_list_2
	PARTITION OF collections_list (key, ts, collection_id, value)
	FOR VALUES IN ( 2 );
-- we don't need many shards
SET citus.shard_count TO 2;
SELECT create_distributed_table('collections_list', 'key');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO collections_list SELECT i % 10, now(), (i % 2) + 1, i*i FROM generate_series(0, 50)i;
SET client_min_messages to 'DEBUG2';
SELECT count(*) FROM collections_list WHERE key = 4;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 4
 count
---------------------------------------------------------------------
     5
(1 row)

SELECT count(*) FROM collections_list_1 WHERE key = 4;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 4
 count
---------------------------------------------------------------------
     5
(1 row)

SELECT count(*) FROM collections_list_2 WHERE key = 4;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 4
 count
---------------------------------------------------------------------
     0
(1 row)

UPDATE collections_list SET value = 15 WHERE key = 4;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 4
SELECT count(*) FILTER (where value = 15) FROM collections_list WHERE key = 4;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 4
 count
---------------------------------------------------------------------
     5
(1 row)

SELECT count(*) FILTER (where value = 15) FROM collections_list_1 WHERE key = 4;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 4
 count
---------------------------------------------------------------------
     5
(1 row)

SELECT count(*) FILTER (where value = 15) FROM collections_list_2 WHERE key = 4;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
DEBUG:  query has a single distribution column value: 4
 count
---------------------------------------------------------------------
     0
(1 row)

-- test INSERT using values from generate_series() and repeat() functions
INSERT INTO authors_reference (id, name) VALUES (generate_series(1, 10), repeat('Migjeni', 3));
DEBUG:  Creating router plan
SELECT * FROM authors_reference ORDER BY 1, 2;
DEBUG:  Distributed planning for a fast-path router query
DEBUG:  Creating router plan
 id |         name
---------------------------------------------------------------------
  1 | MigjeniMigjeniMigjeni
  2 | MigjeniMigjeniMigjeni
  3 | MigjeniMigjeniMigjeni
  4 | MigjeniMigjeniMigjeni
  5 | MigjeniMigjeniMigjeni
  6 | MigjeniMigjeniMigjeni
  7 | MigjeniMigjeniMigjeni
  8 | MigjeniMigjeniMigjeni
  9 | MigjeniMigjeniMigjeni
 10 | MigjeniMigjeniMigjeni
(10 rows)

SET client_min_messages to 'NOTICE';
DROP FUNCTION author_articles_max_id();
DROP FUNCTION author_articles_id_word_count();
DROP MATERIALIZED VIEW mv_articles_hash_empty;
DROP MATERIALIZED VIEW mv_articles_hash_data;
ERROR:  materialized view "mv_articles_hash_data" does not exist
DROP TABLE articles_hash;
ERROR:  cannot drop table articles_hash because other objects depend on it
DETAIL:  view test_view depends on table articles_hash
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE authors_hash;
DROP TABLE authors_range;
DROP TABLE authors_reference;
DROP TABLE company_employees;
DROP TABLE articles_range;
DROP TABLE articles_append;
DROP TABLE collections_list;
RESET search_path;
DROP SCHEMA fast_path_router_select CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table fast_path_router_select.articles_hash
drop cascades to function fast_path_router_select.raise_failed_execution_f_router(text)
drop cascades to function fast_path_router_select.author_articles_max_id(integer)
drop cascades to function fast_path_router_select.author_articles_id_word_count(integer)
drop cascades to view fast_path_router_select.test_view