{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/10706","labels_url":"https://api.github.com/repos/apache/spark/issues/10706/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/10706/comments","events_url":"https://api.github.com/repos/apache/spark/issues/10706/events","html_url":"https://github.com/apache/spark/pull/10706","id":126033082,"number":10706,"title":"[SPARK-12543] [SQL] [WIP] Subquery in expression","user":{"login":"davies","id":40902,"avatar_url":"https://avatars.githubusercontent.com/u/40902?v=3","gravatar_id":"","url":"https://api.github.com/users/davies","html_url":"https://github.com/davies","followers_url":"https://api.github.com/users/davies/followers","following_url":"https://api.github.com/users/davies/following{/other_user}","gists_url":"https://api.github.com/users/davies/gists{/gist_id}","starred_url":"https://api.github.com/users/davies/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/davies/subscriptions","organizations_url":"https://api.github.com/users/davies/orgs","repos_url":"https://api.github.com/users/davies/repos","events_url":"https://api.github.com/users/davies/events{/privacy}","received_events_url":"https://api.github.com/users/davies/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2016-01-11T20:47:24Z","updated_at":"2016-01-12T08:48:59Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/10706","html_url":"https://github.com/apache/spark/pull/10706","diff_url":"https://github.com/apache/spark/pull/10706.diff","patch_url":"https://github.com/apache/spark/pull/10706.patch"},"body":"This PR brings subquery for expression (use subquery as an expression inside SELECT/WHERE/HAVING), for example:\r\n\r\n```\r\n  select (select 1) as a\r\n  select * from t where a = (select max(b) from t2)\r\n  select * from t where exists (select * from t2 where t2.b = t.b)\r\n  select max(a) as ma from t having ma = (select max(b) from t2)\r\n``` \r\n\r\nA subquery could be uncorrelated or correlated, it could be scalar subquery (returns single row and single column) or not (returns multiple rows, for EXISTS or IN).\r\n\r\nA correlated subquery or uncorrelated subquery that returns multiple rows will be rewritten as JOIN. Scalar subquery will be executed separately, result will be filled into the expression.\r\n\r\nRestrictions:\r\n\r\n1. EXISTS/ NOT EXISTS/IN/NOT IN can only be used as top level condition of WHERE/HAVING, can't be used in OR or others.\r\n\r\n2. a NOT IN subquery is not supported, if subquery is nullable, which requires a null-aware semi join.\r\n\r\n3. Correlated subquery can only be used inside WHERE/HAVING, uncorrelated scalar subquery could be used anywhere."},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/170841052","html_url":"https://github.com/apache/spark/pull/10706#issuecomment-170841052","issue_url":"https://api.github.com/repos/apache/spark/issues/10706","id":170841052,"user":{"login":"SparkQA","id":8108735,"avatar_url":"https://avatars.githubusercontent.com/u/8108735?v=3","gravatar_id":"","url":"https://api.github.com/users/SparkQA","html_url":"https://github.com/SparkQA","followers_url":"https://api.github.com/users/SparkQA/followers","following_url":"https://api.github.com/users/SparkQA/following{/other_user}","gists_url":"https://api.github.com/users/SparkQA/gists{/gist_id}","starred_url":"https://api.github.com/users/SparkQA/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SparkQA/subscriptions","organizations_url":"https://api.github.com/users/SparkQA/orgs","repos_url":"https://api.github.com/users/SparkQA/repos","events_url":"https://api.github.com/users/SparkQA/events{/privacy}","received_events_url":"https://api.github.com/users/SparkQA/received_events","type":"User","site_admin":false},"created_at":"2016-01-12T08:48:59Z","updated_at":"2016-01-12T08:48:59Z","body":"**[Test build #49232 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/49232/consoleFull)** for PR 10706 at commit [`2d88bb3`](https://github.com/apache/spark/commit/2d88bb30860b965af0fc3224c293808ef4580a8e)."}}