{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/9055","labels_url":"https://api.github.com/repos/apache/spark/issues/9055/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/9055/comments","events_url":"https://api.github.com/repos/apache/spark/issues/9055/events","html_url":"https://github.com/apache/spark/pull/9055","id":110758751,"number":9055,"title":"[SPARK-4226][SQL]Add subquery (not) in/exists support","user":{"login":"chenghao-intel","id":2367031,"avatar_url":"https://avatars.githubusercontent.com/u/2367031?v=3","gravatar_id":"","url":"https://api.github.com/users/chenghao-intel","html_url":"https://github.com/chenghao-intel","followers_url":"https://api.github.com/users/chenghao-intel/followers","following_url":"https://api.github.com/users/chenghao-intel/following{/other_user}","gists_url":"https://api.github.com/users/chenghao-intel/gists{/gist_id}","starred_url":"https://api.github.com/users/chenghao-intel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chenghao-intel/subscriptions","organizations_url":"https://api.github.com/users/chenghao-intel/orgs","repos_url":"https://api.github.com/users/chenghao-intel/repos","events_url":"https://api.github.com/users/chenghao-intel/events{/privacy}","received_events_url":"https://api.github.com/users/chenghao-intel/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-10-10T01:11:31Z","updated_at":"2015-10-10T01:33:27Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/9055","html_url":"https://github.com/apache/spark/pull/9055","diff_url":"https://github.com/apache/spark/pull/9055.diff","patch_url":"https://github.com/apache/spark/pull/9055.patch"},"body":"Some known feature that we don't support right now, but will add it later.\r\n\r\nWe don't support the outer UDAF function used in the correlated query, combined with\r\nouter having clause, which requires the implicit projection change for the outer query.\r\n```sql\r\nselect b.key, min(b.value)\r\nfrom src b\r\ngroup by b.key\r\nhaving exists (\r\n    select a.key\r\n    from src a\r\n     where a.value > 'val_9' and a.value = min(b.value) -- min(b.value) implicits requires the outer query to add more field in the projection.\r\n)\r\n```\r\n\r\nWe don't support the multiple references for the outer query in both the subquery in both projection and filter clause.\r\n```sql\r\nselect key, value\r\nfrom src b\r\nwhere value in\r\n    (select s1.key+ b.key\r\n     from src s1 \r\n     where s1.key > '9'  and s1.value = b.value) -- both b.value and b.key present in the subquery, but in projection and filter clause respectively.\r\n```"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/147020843","html_url":"https://github.com/apache/spark/pull/9055#issuecomment-147020843","issue_url":"https://api.github.com/repos/apache/spark/issues/9055","id":147020843,"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":"2015-10-10T01:33:27Z","updated_at":"2015-10-10T01:33:27Z","body":"  [Test build #43506 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/43506/console) for   PR 9055 at commit [`e3aa255`](https://github.com/apache/spark/commit/e3aa2553cc3eeb78f8bd15a5f97ccd97032bf954).\n * This patch **fails Spark unit tests**.\n * This patch merges cleanly.\n * This patch adds the following public classes _(experimental)_:\n  * `trait SubQueryExpression extends Unevaluable `\n  * `case class Exists(subquery: LogicalPlan, positive: Boolean)`\n  * `case class InSubquery(child: Expression, subquery: LogicalPlan, positive: Boolean)`\n"}}