{"action":"created","comment":{"url":"https://api.github.com/repos/cmu-db/peloton/pulls/comments/172078509","pull_request_review_id":101032451,"id":172078509,"diff_hunk":"@@ -790,7 +792,45 @@ TEST_F(OptimizerSQLTests, NestedQueryWithAggregationTest) {\n       \"s.sid < 4;\",\n       {\"Patrick\", \"4\", \"David\", \"4\", \"Alice\", \"2\"}, false);\n }\n-*/\n+\n+TEST_F(OptimizerSQLTests, NestedQueryInHavingTest) {\n+  //  TestingSQLUtil::ExecuteSQLQuery(\"INSERT INTO test VALUES (1, 22, 333);\");\n+  //  TestingSQLUtil::ExecuteSQLQuery(\"INSERT INTO test VALUES (2, 11, 000);\");\n+  //  TestingSQLUtil::ExecuteSQLQuery(\"INSERT INTO test VALUES (3, 33, 444);\");\n+  //  TestingSQLUtil::ExecuteSQLQuery(\"INSERT INTO test VALUES (4, 00, 555);\");","path":"test/sql/optimizer_sql_test.cpp","position":54,"original_position":37,"commit_id":"2f9bb9d6d2b1db183b8c336e2f1db7784d4831f7","original_commit_id":"dcabc33ac6d683ec43e1bd68628d0cafa9d61807","user":{"login":"malin1993ml","id":5907405,"avatar_url":"https://avatars1.githubusercontent.com/u/5907405?v=4","gravatar_id":"","url":"https://api.github.com/users/malin1993ml","html_url":"https://github.com/malin1993ml","followers_url":"https://api.github.com/users/malin1993ml/followers","following_url":"https://api.github.com/users/malin1993ml/following{/other_user}","gists_url":"https://api.github.com/users/malin1993ml/gists{/gist_id}","starred_url":"https://api.github.com/users/malin1993ml/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/malin1993ml/subscriptions","organizations_url":"https://api.github.com/users/malin1993ml/orgs","repos_url":"https://api.github.com/users/malin1993ml/repos","events_url":"https://api.github.com/users/malin1993ml/events{/privacy}","received_events_url":"https://api.github.com/users/malin1993ml/received_events","type":"User","site_admin":false},"body":"I see... Then you could add a comment above those queries, otherwise it's easy to miss this.","created_at":"2018-03-05T02:11:49Z","updated_at":"2018-03-05T02:19:44Z","html_url":"https://github.com/cmu-db/peloton/pull/1147#discussion_r172078509","pull_request_url":"https://api.github.com/repos/cmu-db/peloton/pulls/1147","author_association":"OWNER","_links":{"self":{"href":"https://api.github.com/repos/cmu-db/peloton/pulls/comments/172078509"},"html":{"href":"https://github.com/cmu-db/peloton/pull/1147#discussion_r172078509"},"pull_request":{"href":"https://api.github.com/repos/cmu-db/peloton/pulls/1147"}},"in_reply_to_id":171763489},"pull_request":{"url":"https://api.github.com/repos/cmu-db/peloton/pulls/1147","id":168693141,"html_url":"https://github.com/cmu-db/peloton/pull/1147","diff_url":"https://github.com/cmu-db/peloton/pull/1147.diff","patch_url":"https://github.com/cmu-db/peloton/pull/1147.patch","issue_url":"https://api.github.com/repos/cmu-db/peloton/issues/1147","number":1147,"state":"open","locked":false,"title":"Add unnesting for correlated subquery in where clause with aggregation","user":{"login":"chenboy","id":6124070,"avatar_url":"https://avatars2.githubusercontent.com/u/6124070?v=4","gravatar_id":"","url":"https://api.github.com/users/chenboy","html_url":"https://github.com/chenboy","followers_url":"https://api.github.com/users/chenboy/followers","following_url":"https://api.github.com/users/chenboy/following{/other_user}","gists_url":"https://api.github.com/users/chenboy/gists{/gist_id}","starred_url":"https://api.github.com/users/chenboy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chenboy/subscriptions","organizations_url":"https://api.github.com/users/chenboy/orgs","repos_url":"https://api.github.com/users/chenboy/repos","events_url":"https://api.github.com/users/chenboy/events{/privacy}","received_events_url":"https://api.github.com/users/chenboy/received_events","type":"User","site_admin":false},"body":"This PR adds support for unnesting for correlated subquery in where clause with aggregation. For example, for query `SELECT t.a From test AS t WHERE EXISTS (SELECT count(b) FROM test WHERE a = t.a)` we'll pull up the correlated predicate, the transformed query would look like: `SELECT t.a From test AS t, (SELECT count(b) FROM test GROUP BY a)  WHERE test.a = t.a`.\r\n\r\nThis PR will also fix #1146 because now we support aggregate expressions in predicates.\r\n\r\nI also add error handling logic in the optimizer to throw an exception if see a query not supported.\r\n\r\nI think Patrick is the best person to review this, I'll try to contact him.","created_at":"2018-02-12T21:30:27Z","updated_at":"2018-03-05T02:19:44Z","closed_at":null,"merged_at":null,"merge_commit_sha":"6b4536e44814a5300c83f9f53652403db4cdf3e2","assignee":{"login":"chenboy","id":6124070,"avatar_url":"https://avatars2.githubusercontent.com/u/6124070?v=4","gravatar_id":"","url":"https://api.github.com/users/chenboy","html_url":"https://github.com/chenboy","followers_url":"https://api.github.com/users/chenboy/followers","following_url":"https://api.github.com/users/chenboy/following{/other_user}","gists_url":"https://api.github.com/users/chenboy/gists{/gist_id}","starred_url":"https://api.github.com/users/chenboy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chenboy/subscriptions","organizations_url":"https://api.github.com/users/chenboy/orgs","repos_url":"https://api.github.com/users/chenboy/repos","events_url":"https://api.github.com/users/chenboy/events{/privacy}","received_events_url":"https://api.github.com/users/chenboy/received_events","type":"User","site_admin":false},"assignees":[{"login":"chenboy","id":6124070,"avatar_url":"https://avatars2.githubusercontent.com/u/6124070?v=4","gravatar_id":"","url":"https://api.github.com/users/chenboy","html_url":"https://github.com/chenboy","followers_url":"https://api.github.com/users/chenboy/followers","following_url":"https://api.github.com/users/chenboy/following{/other_user}","gists_url":"https://api.github.com/users/chenboy/gists{/gist_id}","starred_url":"https://api.github.com/users/chenboy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chenboy/subscriptions","organizations_url":"https://api.github.com/users/chenboy/orgs","repos_url":"https://api.github.com/users/chenboy/repos","events_url":"https://api.github.com/users/chenboy/events{/privacy}","received_events_url":"https://api.github.com/users/chenboy/received_events","type":"User","site_admin":false}],"requested_reviewers":[],"requested_teams":[],"labels":[{"id":712539152,"url":"https://api.github.com/repos/cmu-db/peloton/labels/ready_for_review","name":"ready_for_review","color":"0e8a16","default":false}],"milestone":null,"commits_url":"https://api.github.com/repos/cmu-db/peloton/pulls/1147/commits","review_comments_url":"https://api.github.com/repos/cmu-db/peloton/pulls/1147/comments","review_comment_url":"https://api.github.com/repos/cmu-db/peloton/pulls/comments{/number}","comments_url":"https://api.github.com/repos/cmu-db/peloton/issues/1147/comments","statuses_url":"https://api.github.com/repos/cmu-db/peloton/statuses/2f9bb9d6d2b1db183b8c336e2f1db7784d4831f7","head":{"label":"chenboy:nested_query","ref":"nested_query","sha":"2f9bb9d6d2b1db183b8c336e2f1db7784d4831f7","user":{"login":"chenboy","id":6124070,"avatar_url":"https://avatars2.githubusercontent.com/u/6124070?v=4","gravatar_id":"","url":"https://api.github.com/users/chenboy","html_url":"https://github.com/chenboy","followers_url":"https://api.github.com/users/chenboy/followers","following_url":"https://api.github.com/users/chenboy/following{/other_user}","gists_url":"https://api.github.com/users/chenboy/gists{/gist_id}","starred_url":"https://api.github.com/users/chenboy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chenboy/subscriptions","organizations_url":"https://api.github.com/users/chenboy/orgs","repos_url":"https://api.github.com/users/chenboy/repos","events_url":"https://api.github.com/users/chenboy/events{/privacy}","received_events_url":"https://api.github.com/users/chenboy/received_events","type":"User","site_admin":false},"repo":{"id":81688982,"name":"peloton","full_name":"chenboy/peloton","owner":{"login":"chenboy","id":6124070,"avatar_url":"https://avatars2.githubusercontent.com/u/6124070?v=4","gravatar_id":"","url":"https://api.github.com/users/chenboy","html_url":"https://github.com/chenboy","followers_url":"https://api.github.com/users/chenboy/followers","following_url":"https://api.github.com/users/chenboy/following{/other_user}","gists_url":"https://api.github.com/users/chenboy/gists{/gist_id}","starred_url":"https://api.github.com/users/chenboy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chenboy/subscriptions","organizations_url":"https://api.github.com/users/chenboy/orgs","repos_url":"https://api.github.com/users/chenboy/repos","events_url":"https://api.github.com/users/chenboy/events{/privacy}","received_events_url":"https://api.github.com/users/chenboy/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/chenboy/peloton","description":"The Self-Driving Database Management System","fork":true,"url":"https://api.github.com/repos/chenboy/peloton","forks_url":"https://api.github.com/repos/chenboy/peloton/forks","keys_url":"https://api.github.com/repos/chenboy/peloton/keys{/key_id}","collaborators_url":"https://api.github.com/repos/chenboy/peloton/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/chenboy/peloton/teams","hooks_url":"https://api.github.com/repos/chenboy/peloton/hooks","issue_events_url":"https://api.github.com/repos/chenboy/peloton/issues/events{/number}","events_url":"https://api.github.com/repos/chenboy/peloton/events","assignees_url":"https://api.github.com/repos/chenboy/peloton/assignees{/user}","branches_url":"https://api.github.com/repos/chenboy/peloton/branches{/branch}","tags_url":"https://api.github.com/repos/chenboy/peloton/tags","blobs_url":"https://api.github.com/repos/chenboy/peloton/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/chenboy/peloton/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/chenboy/peloton/git/refs{/sha}","trees_url":"https://api.github.com/repos/chenboy/peloton/git/trees{/sha}","statuses_url":"https://api.github.com/repos/chenboy/peloton/statuses/{sha}","languages_url":"https://api.github.com/repos/chenboy/peloton/languages","stargazers_url":"https://api.github.com/repos/chenboy/peloton/stargazers","contributors_url":"https://api.github.com/repos/chenboy/peloton/contributors","subscribers_url":"https://api.github.com/repos/chenboy/peloton/subscribers","subscription_url":"https://api.github.com/repos/chenboy/peloton/subscription","commits_url":"https://api.github.com/repos/chenboy/peloton/commits{/sha}","git_commits_url":"https://api.github.com/repos/chenboy/peloton/git/commits{/sha}","comments_url":"https://api.github.com/repos/chenboy/peloton/comments{/number}","issue_comment_url":"https://api.github.com/repos/chenboy/peloton/issues/comments{/number}","contents_url":"https://api.github.com/repos/chenboy/peloton/contents/{+path}","compare_url":"https://api.github.com/repos/chenboy/peloton/compare/{base}...{head}","merges_url":"https://api.github.com/repos/chenboy/peloton/merges","archive_url":"https://api.github.com/repos/chenboy/peloton/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/chenboy/peloton/downloads","issues_url":"https://api.github.com/repos/chenboy/peloton/issues{/number}","pulls_url":"https://api.github.com/repos/chenboy/peloton/pulls{/number}","milestones_url":"https://api.github.com/repos/chenboy/peloton/milestones{/number}","notifications_url":"https://api.github.com/repos/chenboy/peloton/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/chenboy/peloton/labels{/name}","releases_url":"https://api.github.com/repos/chenboy/peloton/releases{/id}","deployments_url":"https://api.github.com/repos/chenboy/peloton/deployments","created_at":"2017-02-11T23:11:31Z","updated_at":"2017-02-11T23:11:35Z","pushed_at":"2018-03-04T18:22:01Z","git_url":"git://github.com/chenboy/peloton.git","ssh_url":"git@github.com:chenboy/peloton.git","clone_url":"https://github.com/chenboy/peloton.git","svn_url":"https://github.com/chenboy/peloton","homepage":"http://pelotondb.org","size":65192,"stargazers_count":0,"watchers_count":0,"language":"C++","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"archived":false,"open_issues_count":0,"license":null,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"cmu-db:master","ref":"master","sha":"b7a035b5824a17993e9f64976c245881dd54be86","user":{"login":"cmu-db","id":6220241,"avatar_url":"https://avatars0.githubusercontent.com/u/6220241?v=4","gravatar_id":"","url":"https://api.github.com/users/cmu-db","html_url":"https://github.com/cmu-db","followers_url":"https://api.github.com/users/cmu-db/followers","following_url":"https://api.github.com/users/cmu-db/following{/other_user}","gists_url":"https://api.github.com/users/cmu-db/gists{/gist_id}","starred_url":"https://api.github.com/users/cmu-db/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cmu-db/subscriptions","organizations_url":"https://api.github.com/users/cmu-db/orgs","repos_url":"https://api.github.com/users/cmu-db/repos","events_url":"https://api.github.com/users/cmu-db/events{/privacy}","received_events_url":"https://api.github.com/users/cmu-db/received_events","type":"Organization","site_admin":false},"repo":{"id":28202609,"name":"peloton","full_name":"cmu-db/peloton","owner":{"login":"cmu-db","id":6220241,"avatar_url":"https://avatars0.githubusercontent.com/u/6220241?v=4","gravatar_id":"","url":"https://api.github.com/users/cmu-db","html_url":"https://github.com/cmu-db","followers_url":"https://api.github.com/users/cmu-db/followers","following_url":"https://api.github.com/users/cmu-db/following{/other_user}","gists_url":"https://api.github.com/users/cmu-db/gists{/gist_id}","starred_url":"https://api.github.com/users/cmu-db/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cmu-db/subscriptions","organizations_url":"https://api.github.com/users/cmu-db/orgs","repos_url":"https://api.github.com/users/cmu-db/repos","events_url":"https://api.github.com/users/cmu-db/events{/privacy}","received_events_url":"https://api.github.com/users/cmu-db/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/cmu-db/peloton","description":"The Self-Driving Database Management System","fork":false,"url":"https://api.github.com/repos/cmu-db/peloton","forks_url":"https://api.github.com/repos/cmu-db/peloton/forks","keys_url":"https://api.github.com/repos/cmu-db/peloton/keys{/key_id}","collaborators_url":"https://api.github.com/repos/cmu-db/peloton/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/cmu-db/peloton/teams","hooks_url":"https://api.github.com/repos/cmu-db/peloton/hooks","issue_events_url":"https://api.github.com/repos/cmu-db/peloton/issues/events{/number}","events_url":"https://api.github.com/repos/cmu-db/peloton/events","assignees_url":"https://api.github.com/repos/cmu-db/peloton/assignees{/user}","branches_url":"https://api.github.com/repos/cmu-db/peloton/branches{/branch}","tags_url":"https://api.github.com/repos/cmu-db/peloton/tags","blobs_url":"https://api.github.com/repos/cmu-db/peloton/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/cmu-db/peloton/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/cmu-db/peloton/git/refs{/sha}","trees_url":"https://api.github.com/repos/cmu-db/peloton/git/trees{/sha}","statuses_url":"https://api.github.com/repos/cmu-db/peloton/statuses/{sha}","languages_url":"https://api.github.com/repos/cmu-db/peloton/languages","stargazers_url":"https://api.github.com/repos/cmu-db/peloton/stargazers","contributors_url":"https://api.github.com/repos/cmu-db/peloton/contributors","subscribers_url":"https://api.github.com/repos/cmu-db/peloton/subscribers","subscription_url":"https://api.github.com/repos/cmu-db/peloton/subscription","commits_url":"https://api.github.com/repos/cmu-db/peloton/commits{/sha}","git_commits_url":"https://api.github.com/repos/cmu-db/peloton/git/commits{/sha}","comments_url":"https://api.github.com/repos/cmu-db/peloton/comments{/number}","issue_comment_url":"https://api.github.com/repos/cmu-db/peloton/issues/comments{/number}","contents_url":"https://api.github.com/repos/cmu-db/peloton/contents/{+path}","compare_url":"https://api.github.com/repos/cmu-db/peloton/compare/{base}...{head}","merges_url":"https://api.github.com/repos/cmu-db/peloton/merges","archive_url":"https://api.github.com/repos/cmu-db/peloton/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/cmu-db/peloton/downloads","issues_url":"https://api.github.com/repos/cmu-db/peloton/issues{/number}","pulls_url":"https://api.github.com/repos/cmu-db/peloton/pulls{/number}","milestones_url":"https://api.github.com/repos/cmu-db/peloton/milestones{/number}","notifications_url":"https://api.github.com/repos/cmu-db/peloton/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/cmu-db/peloton/labels{/name}","releases_url":"https://api.github.com/repos/cmu-db/peloton/releases{/id}","deployments_url":"https://api.github.com/repos/cmu-db/peloton/deployments","created_at":"2014-12-18T21:35:23Z","updated_at":"2018-03-04T04:32:45Z","pushed_at":"2018-03-05T00:41:00Z","git_url":"git://github.com/cmu-db/peloton.git","ssh_url":"git@github.com:cmu-db/peloton.git","clone_url":"https://github.com/cmu-db/peloton.git","svn_url":"https://github.com/cmu-db/peloton","homepage":"http://pelotondb.io","size":65799,"stargazers_count":1137,"watchers_count":1137,"language":"C++","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":419,"mirror_url":null,"archived":false,"open_issues_count":120,"license":{"key":"apache-2.0","name":"Apache License 2.0","spdx_id":"Apache-2.0","url":"https://api.github.com/licenses/apache-2.0"},"forks":419,"open_issues":120,"watchers":1137,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/cmu-db/peloton/pulls/1147"},"html":{"href":"https://github.com/cmu-db/peloton/pull/1147"},"issue":{"href":"https://api.github.com/repos/cmu-db/peloton/issues/1147"},"comments":{"href":"https://api.github.com/repos/cmu-db/peloton/issues/1147/comments"},"review_comments":{"href":"https://api.github.com/repos/cmu-db/peloton/pulls/1147/comments"},"review_comment":{"href":"https://api.github.com/repos/cmu-db/peloton/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/cmu-db/peloton/pulls/1147/commits"},"statuses":{"href":"https://api.github.com/repos/cmu-db/peloton/statuses/2f9bb9d6d2b1db183b8c336e2f1db7784d4831f7"}},"author_association":"CONTRIBUTOR"}}
{"actor":{"display_login":"malin1993ml"}}