{"action":"created","comment":{"url":"https://api.github.com/repos/citusdata/citus/pulls/comments/115572822","pull_request_review_id":37122074,"id":115572822,"diff_hunk":"@@ -0,0 +1,155 @@\n+-- multi subquery pushdown misc aims to test subquery pushdown queries with\n+--   (i)   Prepared statements\n+--   (ii)  PL/PGSQL functions\n+--   (iii) SQL functions\n+\n+-- the tables that are used depends to multi_insert_select_behavioral_analytics_create_table.sql\n+\n+-- We don't need shard id sequence here, so commented out to prevent conflicts with concurrent tests\n+SET citus.enable_router_execution TO false;\n+\n+PREPARE prepared_subquery_1 AS\n+SELECT\n+    user_id,\n+    user_lastseen,\n+    array_length(event_array, 1)\n+FROM (\n+    SELECT\n+        user_id,\n+        max(u.time) as user_lastseen,\n+        array_agg(event_type ORDER BY u.time) AS event_array\n+    FROM (\n+        SELECT user_id, time\n+        FROM users_table\n+        WHERE\n+        user_id >= 10 AND\n+        user_id <= 70 AND\n+        users_table.value_1 > 10 AND users_table.value_1 < 12\n+        ) u LEFT JOIN LATERAL (\n+          SELECT event_type, time\n+          FROM events_table\n+          WHERE user_id = u.user_id AND\n+          events_table.event_type > 10 AND events_table.event_type < 12\n+        ) t ON true\n+        GROUP BY user_id\n+) AS shard_union\n+ORDER BY user_lastseen DESC, user_id;\n+\n+EXECUTE prepared_subquery_1;\n+\n+\n+PREPARE prepared_subquery_2(int, int) AS\n+SELECT\n+    user_id,\n+    user_lastseen,\n+    array_length(event_array, 1)\n+FROM (\n+    SELECT\n+        user_id,\n+        max(u.time) as user_lastseen,\n+        array_agg(event_type ORDER BY u.time) AS event_array\n+    FROM (\n+        SELECT user_id, time\n+        FROM users_table\n+        WHERE\n+        user_id >= $1 AND\n+        user_id <= $2 AND\n+        users_table.value_1 > 10 AND users_table.value_1 < 12\n+        ) u LEFT JOIN LATERAL (\n+          SELECT event_type, time\n+          FROM events_table\n+          WHERE user_id = u.user_id AND\n+          events_table.event_type > 10 AND events_table.event_type < 12\n+        ) t ON true\n+        GROUP BY user_id\n+) AS shard_union\n+ORDER BY user_lastseen DESC, user_id;\n+\n+-- should be fine with more than five executions\n+EXECUTE prepared_subquery_2(10, 70);\n+EXECUTE prepared_subquery_2(10, 70);\n+EXECUTE prepared_subquery_2(10, 70);\n+EXECUTE prepared_subquery_2(10, 70);\n+EXECUTE prepared_subquery_2(10, 70);\n+EXECUTE prepared_subquery_2(10, 70);\n+EXECUTE prepared_subquery_2(10, 70);\n+\n+-- prepared statements with subqueries in WHERE clause\n+PREPARE prepared_subquery_3(int, int, int, int, int, int) AS\n+SELECT user_id\n+FROM users_table\n+WHERE user_id IN (SELECT user_id FROM users_table WHERE value_1 >= $4 AND value_1 <= $3)\n+    AND user_id IN (SELECT user_id FROM users_table WHERE value_1 >= $5 AND value_1 <= $6)\n+    AND user_id IN (SELECT user_id FROM users_table WHERE  value_1 >= $1 AND value_1 <= $2)\n+GROUP BY \n+  user_id\n+ORDER BY\n+  user_id DESC\n+  LIMIT 5;\n+\n+EXECUTE prepared_subquery_3(50, 60, 20, 10, 30, 40);\n+\n+\n+CREATE FUNCTION plpgsql_subquery_test(int, int) RETURNS TABLE(count bigint) AS $$\n+DECLARE\n+BEGIN\n+     RETURN QUERY\n+\t\t\tSELECT\n+\t\t\t    count(*)\n+\t\t\tFROM\n+\t\t\t   users_table\n+\t\t\t        JOIN\n+\t\t\t   (SELECT\n+\t\t\t      ma.user_id, (GREATEST(coalesce(ma.value_4 / 250, 0.0) + GREATEST(1.0))) / 2 AS prob\n+\t\t\t    FROM\n+\t\t\t    \tusers_table AS ma, events_table as short_list\n+\t\t\t    WHERE\n+\t\t\t    \tshort_list.user_id = ma.user_id and ma.value_1 < $1 and short_list.event_type < 50\n+\t\t\t    ) temp\n+\t\t\t  ON users_table.user_id = temp.user_id\n+\t\t\t  WHERE \n+\t\t\t    users_table.value_1 < $2;\n+\n+END;\n+$$ LANGUAGE plpgsql;\n+\n+-- run more than 5 times\n+SELECT plpgsql_subquery_test(10, 20);\n+SELECT plpgsql_subquery_test(10, 20);\n+SELECT plpgsql_subquery_test(10, 20);\n+SELECT plpgsql_subquery_test(10, 20);\n+SELECT plpgsql_subquery_test(10, 20);\n+SELECT plpgsql_subquery_test(10, 20);\n+\n+-- this should also work, but should return 0 given that int = NULL is always returns false\n+SELECT plpgsql_subquery_test(10, NULL);\n+\n+CREATE FUNCTION sql_subquery_test(int, int) RETURNS bigint AS $$\n+\t\tSELECT\n+\t    count(*)\n+\tFROM\n+\t   users_table\n+\t        JOIN\n+\t   (SELECT\n+\t      ma.user_id, (GREATEST(coalesce(ma.value_4 / 250, 0.0) + GREATEST(1.0))) / 2 AS prob\n+\t    FROM\n+\t    \tusers_table AS ma, events_table as short_list\n+\t    WHERE\n+\t    \tshort_list.user_id = ma.user_id and ma.value_1 < $1 and short_list.event_type < 50\n+\t    ) temp\n+\t  ON users_table.user_id = temp.user_id\n+\t  WHERE \n+\t    users_table.value_1 < $2;\n+$$ LANGUAGE SQL;\n+\n+-- should error out\n+SELECT sql_subquery_test(5,5);\n+\n+DEALLOCATE prepared_subquery_1;\n+DEALLOCATE prepared_subquery_2;\n+DEALLOCATE prepared_subquery_3;","path":"src/test/regress/sql/multi_subquery_misc.sql","position":156,"original_position":150,"commit_id":"42845a83d7a43481187ac37dfb63be4e830205a4","original_commit_id":"d36e21ab460472dc4d74625b6800f67e4fa1c9f6","user":{"login":"anarazel","id":1598211,"avatar_url":"https://avatars2.githubusercontent.com/u/1598211?v=3","gravatar_id":"","url":"https://api.github.com/users/anarazel","html_url":"https://github.com/anarazel","followers_url":"https://api.github.com/users/anarazel/followers","following_url":"https://api.github.com/users/anarazel/following{/other_user}","gists_url":"https://api.github.com/users/anarazel/gists{/gist_id}","starred_url":"https://api.github.com/users/anarazel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/anarazel/subscriptions","organizations_url":"https://api.github.com/users/anarazel/orgs","repos_url":"https://api.github.com/users/anarazel/repos","events_url":"https://api.github.com/users/anarazel/events{/privacy}","received_events_url":"https://api.github.com/users/anarazel/received_events","type":"User","site_admin":false},"body":"Why?   I'm ok with the DROP FUNCTIONs, because they're a persistence resource, but deallocating things uselessly is a bad pattern.","created_at":"2017-05-09T18:56:37Z","updated_at":"2017-05-09T18:57:08Z","html_url":"https://github.com/citusdata/citus/pull/1356#discussion_r115572822","pull_request_url":"https://api.github.com/repos/citusdata/citus/pulls/1356","_links":{"self":{"href":"https://api.github.com/repos/citusdata/citus/pulls/comments/115572822"},"html":{"href":"https://github.com/citusdata/citus/pull/1356#discussion_r115572822"},"pull_request":{"href":"https://api.github.com/repos/citusdata/citus/pulls/1356"}}},"pull_request":{"url":"https://api.github.com/repos/citusdata/citus/pulls/1356","id":117834281,"html_url":"https://github.com/citusdata/citus/pull/1356","diff_url":"https://github.com/citusdata/citus/pull/1356.diff","patch_url":"https://github.com/citusdata/citus/pull/1356.patch","issue_url":"https://api.github.com/repos/citusdata/citus/issues/1356","number":1356,"state":"open","locked":false,"title":"Add support for parametrized execution for subquery pushdown","user":{"login":"onderkalaci","id":7829470,"avatar_url":"https://avatars1.githubusercontent.com/u/7829470?v=3","gravatar_id":"","url":"https://api.github.com/users/onderkalaci","html_url":"https://github.com/onderkalaci","followers_url":"https://api.github.com/users/onderkalaci/followers","following_url":"https://api.github.com/users/onderkalaci/following{/other_user}","gists_url":"https://api.github.com/users/onderkalaci/gists{/gist_id}","starred_url":"https://api.github.com/users/onderkalaci/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/onderkalaci/subscriptions","organizations_url":"https://api.github.com/users/onderkalaci/orgs","repos_url":"https://api.github.com/users/onderkalaci/repos","events_url":"https://api.github.com/users/onderkalaci/events{/privacy}","received_events_url":"https://api.github.com/users/onderkalaci/received_events","type":"User","site_admin":false},"body":"Distributed query planning for subquery pushdown is done on the original\r\nquery. This prevents the usage of external parameters on the execution.\r\nTo overcome this, we manually replace the parameters on the original\r\nquery.","created_at":"2017-04-26T23:58:52Z","updated_at":"2017-05-09T18:57:08Z","closed_at":null,"merged_at":null,"merge_commit_sha":"013c69aa101e371f4107eed67c0ca6472c02a430","assignee":{"login":"anarazel","id":1598211,"avatar_url":"https://avatars2.githubusercontent.com/u/1598211?v=3","gravatar_id":"","url":"https://api.github.com/users/anarazel","html_url":"https://github.com/anarazel","followers_url":"https://api.github.com/users/anarazel/followers","following_url":"https://api.github.com/users/anarazel/following{/other_user}","gists_url":"https://api.github.com/users/anarazel/gists{/gist_id}","starred_url":"https://api.github.com/users/anarazel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/anarazel/subscriptions","organizations_url":"https://api.github.com/users/anarazel/orgs","repos_url":"https://api.github.com/users/anarazel/repos","events_url":"https://api.github.com/users/anarazel/events{/privacy}","received_events_url":"https://api.github.com/users/anarazel/received_events","type":"User","site_admin":false},"assignees":[{"login":"anarazel","id":1598211,"avatar_url":"https://avatars2.githubusercontent.com/u/1598211?v=3","gravatar_id":"","url":"https://api.github.com/users/anarazel","html_url":"https://github.com/anarazel","followers_url":"https://api.github.com/users/anarazel/followers","following_url":"https://api.github.com/users/anarazel/following{/other_user}","gists_url":"https://api.github.com/users/anarazel/gists{/gist_id}","starred_url":"https://api.github.com/users/anarazel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/anarazel/subscriptions","organizations_url":"https://api.github.com/users/anarazel/orgs","repos_url":"https://api.github.com/users/anarazel/repos","events_url":"https://api.github.com/users/anarazel/events{/privacy}","received_events_url":"https://api.github.com/users/anarazel/received_events","type":"User","site_admin":false}],"requested_reviewers":[],"milestone":null,"commits_url":"https://api.github.com/repos/citusdata/citus/pulls/1356/commits","review_comments_url":"https://api.github.com/repos/citusdata/citus/pulls/1356/comments","review_comment_url":"https://api.github.com/repos/citusdata/citus/pulls/comments{/number}","comments_url":"https://api.github.com/repos/citusdata/citus/issues/1356/comments","statuses_url":"https://api.github.com/repos/citusdata/citus/statuses/42845a83d7a43481187ac37dfb63be4e830205a4","head":{"label":"citusdata:fix_parametrized_subquery_pushdown","ref":"fix_parametrized_subquery_pushdown","sha":"42845a83d7a43481187ac37dfb63be4e830205a4","user":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars1.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"repo":{"id":50874442,"name":"citus","full_name":"citusdata/citus","owner":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars1.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/citusdata/citus","description":"Scalable PostgreSQL for multi-tenant and real-time workloads","fork":false,"url":"https://api.github.com/repos/citusdata/citus","forks_url":"https://api.github.com/repos/citusdata/citus/forks","keys_url":"https://api.github.com/repos/citusdata/citus/keys{/key_id}","collaborators_url":"https://api.github.com/repos/citusdata/citus/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/citusdata/citus/teams","hooks_url":"https://api.github.com/repos/citusdata/citus/hooks","issue_events_url":"https://api.github.com/repos/citusdata/citus/issues/events{/number}","events_url":"https://api.github.com/repos/citusdata/citus/events","assignees_url":"https://api.github.com/repos/citusdata/citus/assignees{/user}","branches_url":"https://api.github.com/repos/citusdata/citus/branches{/branch}","tags_url":"https://api.github.com/repos/citusdata/citus/tags","blobs_url":"https://api.github.com/repos/citusdata/citus/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/citusdata/citus/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/citusdata/citus/git/refs{/sha}","trees_url":"https://api.github.com/repos/citusdata/citus/git/trees{/sha}","statuses_url":"https://api.github.com/repos/citusdata/citus/statuses/{sha}","languages_url":"https://api.github.com/repos/citusdata/citus/languages","stargazers_url":"https://api.github.com/repos/citusdata/citus/stargazers","contributors_url":"https://api.github.com/repos/citusdata/citus/contributors","subscribers_url":"https://api.github.com/repos/citusdata/citus/subscribers","subscription_url":"https://api.github.com/repos/citusdata/citus/subscription","commits_url":"https://api.github.com/repos/citusdata/citus/commits{/sha}","git_commits_url":"https://api.github.com/repos/citusdata/citus/git/commits{/sha}","comments_url":"https://api.github.com/repos/citusdata/citus/comments{/number}","issue_comment_url":"https://api.github.com/repos/citusdata/citus/issues/comments{/number}","contents_url":"https://api.github.com/repos/citusdata/citus/contents/{+path}","compare_url":"https://api.github.com/repos/citusdata/citus/compare/{base}...{head}","merges_url":"https://api.github.com/repos/citusdata/citus/merges","archive_url":"https://api.github.com/repos/citusdata/citus/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/citusdata/citus/downloads","issues_url":"https://api.github.com/repos/citusdata/citus/issues{/number}","pulls_url":"https://api.github.com/repos/citusdata/citus/pulls{/number}","milestones_url":"https://api.github.com/repos/citusdata/citus/milestones{/number}","notifications_url":"https://api.github.com/repos/citusdata/citus/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/citusdata/citus/labels{/name}","releases_url":"https://api.github.com/repos/citusdata/citus/releases{/id}","deployments_url":"https://api.github.com/repos/citusdata/citus/deployments","created_at":"2016-02-01T21:58:39Z","updated_at":"2017-05-09T11:35:56Z","pushed_at":"2017-05-09T17:08:11Z","git_url":"git://github.com/citusdata/citus.git","ssh_url":"git@github.com:citusdata/citus.git","clone_url":"https://github.com/citusdata/citus.git","svn_url":"https://github.com/citusdata/citus","homepage":"https://www.citusdata.com","size":6368,"stargazers_count":2031,"watchers_count":2031,"language":"C","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":141,"mirror_url":null,"open_issues_count":452,"forks":141,"open_issues":452,"watchers":2031,"default_branch":"master"}},"base":{"label":"citusdata:master","ref":"master","sha":"97334a912365e724836f304f54aff4f91961a0f3","user":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars1.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"repo":{"id":50874442,"name":"citus","full_name":"citusdata/citus","owner":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars1.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/citusdata/citus","description":"Scalable PostgreSQL for multi-tenant and real-time workloads","fork":false,"url":"https://api.github.com/repos/citusdata/citus","forks_url":"https://api.github.com/repos/citusdata/citus/forks","keys_url":"https://api.github.com/repos/citusdata/citus/keys{/key_id}","collaborators_url":"https://api.github.com/repos/citusdata/citus/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/citusdata/citus/teams","hooks_url":"https://api.github.com/repos/citusdata/citus/hooks","issue_events_url":"https://api.github.com/repos/citusdata/citus/issues/events{/number}","events_url":"https://api.github.com/repos/citusdata/citus/events","assignees_url":"https://api.github.com/repos/citusdata/citus/assignees{/user}","branches_url":"https://api.github.com/repos/citusdata/citus/branches{/branch}","tags_url":"https://api.github.com/repos/citusdata/citus/tags","blobs_url":"https://api.github.com/repos/citusdata/citus/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/citusdata/citus/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/citusdata/citus/git/refs{/sha}","trees_url":"https://api.github.com/repos/citusdata/citus/git/trees{/sha}","statuses_url":"https://api.github.com/repos/citusdata/citus/statuses/{sha}","languages_url":"https://api.github.com/repos/citusdata/citus/languages","stargazers_url":"https://api.github.com/repos/citusdata/citus/stargazers","contributors_url":"https://api.github.com/repos/citusdata/citus/contributors","subscribers_url":"https://api.github.com/repos/citusdata/citus/subscribers","subscription_url":"https://api.github.com/repos/citusdata/citus/subscription","commits_url":"https://api.github.com/repos/citusdata/citus/commits{/sha}","git_commits_url":"https://api.github.com/repos/citusdata/citus/git/commits{/sha}","comments_url":"https://api.github.com/repos/citusdata/citus/comments{/number}","issue_comment_url":"https://api.github.com/repos/citusdata/citus/issues/comments{/number}","contents_url":"https://api.github.com/repos/citusdata/citus/contents/{+path}","compare_url":"https://api.github.com/repos/citusdata/citus/compare/{base}...{head}","merges_url":"https://api.github.com/repos/citusdata/citus/merges","archive_url":"https://api.github.com/repos/citusdata/citus/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/citusdata/citus/downloads","issues_url":"https://api.github.com/repos/citusdata/citus/issues{/number}","pulls_url":"https://api.github.com/repos/citusdata/citus/pulls{/number}","milestones_url":"https://api.github.com/repos/citusdata/citus/milestones{/number}","notifications_url":"https://api.github.com/repos/citusdata/citus/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/citusdata/citus/labels{/name}","releases_url":"https://api.github.com/repos/citusdata/citus/releases{/id}","deployments_url":"https://api.github.com/repos/citusdata/citus/deployments","created_at":"2016-02-01T21:58:39Z","updated_at":"2017-05-09T11:35:56Z","pushed_at":"2017-05-09T17:08:11Z","git_url":"git://github.com/citusdata/citus.git","ssh_url":"git@github.com:citusdata/citus.git","clone_url":"https://github.com/citusdata/citus.git","svn_url":"https://github.com/citusdata/citus","homepage":"https://www.citusdata.com","size":6368,"stargazers_count":2031,"watchers_count":2031,"language":"C","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":141,"mirror_url":null,"open_issues_count":452,"forks":141,"open_issues":452,"watchers":2031,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/citusdata/citus/pulls/1356"},"html":{"href":"https://github.com/citusdata/citus/pull/1356"},"issue":{"href":"https://api.github.com/repos/citusdata/citus/issues/1356"},"comments":{"href":"https://api.github.com/repos/citusdata/citus/issues/1356/comments"},"review_comments":{"href":"https://api.github.com/repos/citusdata/citus/pulls/1356/comments"},"review_comment":{"href":"https://api.github.com/repos/citusdata/citus/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/citusdata/citus/pulls/1356/commits"},"statuses":{"href":"https://api.github.com/repos/citusdata/citus/statuses/42845a83d7a43481187ac37dfb63be4e830205a4"}}}}
{"actor":{"display_login":"anarazel"}}