{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/21403","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/21403/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/21403/comments","events_url":"https://api.github.com/repos/apache/spark/issues/21403/events","html_url":"https://github.com/apache/spark/pull/21403","id":325483588,"node_id":"MDExOlB1bGxSZXF1ZXN0MTg5ODA4MzQ2","number":21403,"title":"[SPARK-24341][SQL] Support only IN subqueries with the same number of items per row","user":{"login":"mgaido91","id":8821783,"node_id":"MDQ6VXNlcjg4MjE3ODM=","avatar_url":"https://avatars1.githubusercontent.com/u/8821783?v=4","gravatar_id":"","url":"https://api.github.com/users/mgaido91","html_url":"https://github.com/mgaido91","followers_url":"https://api.github.com/users/mgaido91/followers","following_url":"https://api.github.com/users/mgaido91/following{/other_user}","gists_url":"https://api.github.com/users/mgaido91/gists{/gist_id}","starred_url":"https://api.github.com/users/mgaido91/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mgaido91/subscriptions","organizations_url":"https://api.github.com/users/mgaido91/orgs","repos_url":"https://api.github.com/users/mgaido91/repos","events_url":"https://api.github.com/users/mgaido91/events{/privacy}","received_events_url":"https://api.github.com/users/mgaido91/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":58,"created_at":"2018-05-22T22:19:02Z","updated_at":"2018-09-19T07:11:16Z","closed_at":"2018-08-07T07:48:01Z","author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/21403","html_url":"https://github.com/apache/spark/pull/21403","diff_url":"https://github.com/apache/spark/pull/21403.diff","patch_url":"https://github.com/apache/spark/pull/21403.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\nUsing struct types in subqueries with the `IN` clause can generate invalid plans in `RewritePredicateSubquery`. Indeed, we are not handling clearly the cases when the outer value is a struct or the output of the inner subquery is a struct.\r\n\r\nThe PR aims to make Spark's behavior the same as the one of the other RDBMS - namely Oracle and Postgres behavior were checked. So we consider valid only queries having the same number of fields in the outer value and in the subquery. This means that:\r\n\r\n - `(a, b) IN (select c, d from ...)` is a valid query;\r\n - `(a, b) IN (select (c, d) from ...)` throws an AnalysisException, as in the subquery we have only one field of type struct while in the outer value we have 2 fields;\r\n - `a IN (select (c, d) from ...)` - where `a` is a struct - is a valid query.\r\n\r\n## How was this patch tested?\r\n\r\nAdded UT\r\n"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/422683425","html_url":"https://github.com/apache/spark/pull/21403#issuecomment-422683425","issue_url":"https://api.github.com/repos/apache/spark/issues/21403","id":422683425,"node_id":"MDEyOklzc3VlQ29tbWVudDQyMjY4MzQyNQ==","user":{"login":"mgaido91","id":8821783,"node_id":"MDQ6VXNlcjg4MjE3ODM=","avatar_url":"https://avatars1.githubusercontent.com/u/8821783?v=4","gravatar_id":"","url":"https://api.github.com/users/mgaido91","html_url":"https://github.com/mgaido91","followers_url":"https://api.github.com/users/mgaido91/followers","following_url":"https://api.github.com/users/mgaido91/following{/other_user}","gists_url":"https://api.github.com/users/mgaido91/gists{/gist_id}","starred_url":"https://api.github.com/users/mgaido91/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mgaido91/subscriptions","organizations_url":"https://api.github.com/users/mgaido91/orgs","repos_url":"https://api.github.com/users/mgaido91/repos","events_url":"https://api.github.com/users/mgaido91/events{/privacy}","received_events_url":"https://api.github.com/users/mgaido91/received_events","type":"User","site_admin":false},"created_at":"2018-09-19T07:11:16Z","updated_at":"2018-09-19T07:11:16Z","author_association":"CONTRIBUTOR","body":"@cloud-fan, no, it introduces a behavior change when structs are involved. The two queries [here](https://github.com/apache/spark/pull/21403/files/eb1dfb7e0873b8479ea54d223b7fde3dcefa4834#diff-b324aa60ed6de5866aebafc3c9b80391R10) would fail before this query, while the version written like this would work (and after the PR doesn't work instead):\r\n```\r\nselect count(*) from struct_tab where record in (select a2, b2 from tab_b);\r\nselect count(*) from struct_tab where record not in (select a2, b2 from tab_b);\r\n```\r\nSince before the PR any struct before the IN operator behaves like having `(f1, f2, ...)`, while after the PR a struct there is considered as a field."}}
{"actor":{"display_login":"mgaido91"}}