{"action":"created","comment":{"url":"https://api.github.com/repos/apache/spark/pulls/comments/228262819","pull_request_review_id":168495942,"id":228262819,"node_id":"MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDIyODI2MjgxOQ==","diff_hunk":"@@ -202,7 +209,11 @@ case class InSubquery(values: Seq[Expression], query: ListQuery)\n  */\n // scalastyle:off line.size.limit\n @ExpressionDescription(\n-  usage = \"expr1 _FUNC_(expr2, expr3, ...) - Returns true if `expr` equals to any valN.\",\n+  usage = \"\"\"\n+    expr1 _FUNC_(expr2, expr3, ...) - Returns true if `expr` equals to any valN. Otherwise, if\n+      spark.sql.legacy.inOperator.falseForNullField is false and any of the elements or fields of\n+      the elements is null it returns null, else it returns false.","path":"sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala","position":53,"original_position":53,"commit_id":"321b9a8b9011eacaca0a0c5bd15eef9d0b2277ec","original_commit_id":"321b9a8b9011eacaca0a0c5bd15eef9d0b2277ec","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},"body":"I think the point here is that only a string literal works, so it doesn't work concat and/or interpolation. This just puts the string on different lines, ie. the output is:\r\n```\r\nscala> sql(\"DESCRIBE FUNCTION IN\").show(false)\r\n+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n|function_desc                                                                                                                                                                                                                                                             |\r\n+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n|Function: in                                                                                                                                                                                                                                                              |\r\n|Class: org.apache.spark.sql.catalyst.expressions.In                                                                                                                                                                                                                       |\r\n|Usage: \r\n    expr1 in(expr2, expr3, ...) - Returns true if `expr` equals to any valN. Otherwise, if\r\n      spark.sql.legacy.inOperator.falseForNullField is false and any of the elements or fields of\r\n      the elements is null it returns null, else it returns false.\r\n  |\r\n+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n```","created_at":"2018-10-25T17:21:43Z","updated_at":"2018-10-25T17:21:43Z","html_url":"https://github.com/apache/spark/pull/22029#discussion_r228262819","pull_request_url":"https://api.github.com/repos/apache/spark/pulls/22029","author_association":"CONTRIBUTOR","_links":{"self":{"href":"https://api.github.com/repos/apache/spark/pulls/comments/228262819"},"html":{"href":"https://github.com/apache/spark/pull/22029#discussion_r228262819"},"pull_request":{"href":"https://api.github.com/repos/apache/spark/pulls/22029"}},"in_reply_to_id":228245697},"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/22029","id":206749781,"node_id":"MDExOlB1bGxSZXF1ZXN0MjA2NzQ5Nzgx","html_url":"https://github.com/apache/spark/pull/22029","diff_url":"https://github.com/apache/spark/pull/22029.diff","patch_url":"https://github.com/apache/spark/pull/22029.patch","issue_url":"https://api.github.com/repos/apache/spark/issues/22029","number":22029,"state":"open","locked":false,"title":"[SPARK-24395][SQL] IN operator should return NULL when comparing struct with NULL fields","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},"body":"## What changes were proposed in this pull request?\r\n\r\nSpark's IN operator behaves different from other RDBMS (Oracle, Postgres) when structs containing NULL fields are involved. In this case, Spark returns `false`, while other RDBMS return `NULL`. This is critical especially when there are NOT IN filters, as Spark doesn't filter rows containing NULLs in that scenario (instead other RDBMS do).\r\n\r\nThe PR proposes to change Spark's IN operator behavior in order to align with the behavior of other RDBMS and introduces a flag which can be used by users to switch back to the previous behavior.\r\n\r\nIn particular, the proposed change affects only the IN operator when the values are structs (ie. every value is a list of more than one element) and NULL are involved. The proposed behavior can be summarized as follows:\r\n - If the value before IN contains a NULL element, IN evaluates to `null`;\r\n - If the list of values contains a values with a NULL element, then: If the list contains also the value looked for, `true` is returned; otherwise, `null` is returned\r\n\r\nPlease notice that Hive and Presto, instead, behave as Spark **before this change** for this specific scenario. But Presto behaves in the same way also when there are subqueries in the IN operator (while Hive doesn't support this kind of subqueries). So **before this change** Spark is anyway the only system where the IN operator behaves incoherently between IN + subquery and IN + literals.\r\n\r\nSummarizing:\r\n\r\n - Spark is the only framework which behaves differently with the same data when IN contains literals and subquery;\r\n - Hive and Presto behave as Spark before the PR when literals are involved (but Presto throws exception when null is present in both sides...);\r\n - Oracle, Postgres behaves like Spark after the PR;\r\n - Hive doesn't support In with subqueries containing more than 1 output field and Presto behaves differently from Spark, Oracle, Postgres when IN is used with subqueries (but it is coherent with its behavior with literals).\r\n\r\nThe PR introduces also a the config flag `spark.sql.legacy.inOperator.falseForNullField` which can be used to turn on/off the new behavior. The new behavior **by default is turned off**.\r\n\r\n\r\n## How was this patch tested?\r\n\r\nadded UTs\r\n","created_at":"2018-08-07T16:04:56Z","updated_at":"2018-10-25T17:21:43Z","closed_at":null,"merged_at":null,"merge_commit_sha":"abcd613cccff6dfcfd5ffe4fa60753d00a4f59d2","assignee":null,"assignees":[],"requested_reviewers":[],"requested_teams":[],"labels":[],"milestone":null,"commits_url":"https://api.github.com/repos/apache/spark/pulls/22029/commits","review_comments_url":"https://api.github.com/repos/apache/spark/pulls/22029/comments","review_comment_url":"https://api.github.com/repos/apache/spark/pulls/comments{/number}","comments_url":"https://api.github.com/repos/apache/spark/issues/22029/comments","statuses_url":"https://api.github.com/repos/apache/spark/statuses/321b9a8b9011eacaca0a0c5bd15eef9d0b2277ec","head":{"label":"mgaido91:SPARK-24395","ref":"SPARK-24395","sha":"321b9a8b9011eacaca0a0c5bd15eef9d0b2277ec","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},"repo":{"id":94518128,"node_id":"MDEwOlJlcG9zaXRvcnk5NDUxODEyOA==","name":"spark","full_name":"mgaido91/spark","private":false,"owner":{"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},"html_url":"https://github.com/mgaido91/spark","description":"Mirror of Apache Spark","fork":true,"url":"https://api.github.com/repos/mgaido91/spark","forks_url":"https://api.github.com/repos/mgaido91/spark/forks","keys_url":"https://api.github.com/repos/mgaido91/spark/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mgaido91/spark/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mgaido91/spark/teams","hooks_url":"https://api.github.com/repos/mgaido91/spark/hooks","issue_events_url":"https://api.github.com/repos/mgaido91/spark/issues/events{/number}","events_url":"https://api.github.com/repos/mgaido91/spark/events","assignees_url":"https://api.github.com/repos/mgaido91/spark/assignees{/user}","branches_url":"https://api.github.com/repos/mgaido91/spark/branches{/branch}","tags_url":"https://api.github.com/repos/mgaido91/spark/tags","blobs_url":"https://api.github.com/repos/mgaido91/spark/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mgaido91/spark/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mgaido91/spark/git/refs{/sha}","trees_url":"https://api.github.com/repos/mgaido91/spark/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mgaido91/spark/statuses/{sha}","languages_url":"https://api.github.com/repos/mgaido91/spark/languages","stargazers_url":"https://api.github.com/repos/mgaido91/spark/stargazers","contributors_url":"https://api.github.com/repos/mgaido91/spark/contributors","subscribers_url":"https://api.github.com/repos/mgaido91/spark/subscribers","subscription_url":"https://api.github.com/repos/mgaido91/spark/subscription","commits_url":"https://api.github.com/repos/mgaido91/spark/commits{/sha}","git_commits_url":"https://api.github.com/repos/mgaido91/spark/git/commits{/sha}","comments_url":"https://api.github.com/repos/mgaido91/spark/comments{/number}","issue_comment_url":"https://api.github.com/repos/mgaido91/spark/issues/comments{/number}","contents_url":"https://api.github.com/repos/mgaido91/spark/contents/{+path}","compare_url":"https://api.github.com/repos/mgaido91/spark/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mgaido91/spark/merges","archive_url":"https://api.github.com/repos/mgaido91/spark/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mgaido91/spark/downloads","issues_url":"https://api.github.com/repos/mgaido91/spark/issues{/number}","pulls_url":"https://api.github.com/repos/mgaido91/spark/pulls{/number}","milestones_url":"https://api.github.com/repos/mgaido91/spark/milestones{/number}","notifications_url":"https://api.github.com/repos/mgaido91/spark/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mgaido91/spark/labels{/name}","releases_url":"https://api.github.com/repos/mgaido91/spark/releases{/id}","deployments_url":"https://api.github.com/repos/mgaido91/spark/deployments","created_at":"2017-06-16T07:34:38Z","updated_at":"2017-06-16T07:35:05Z","pushed_at":"2018-10-25T16:18:15Z","git_url":"git://github.com/mgaido91/spark.git","ssh_url":"git@github.com:mgaido91/spark.git","clone_url":"https://github.com/mgaido91/spark.git","svn_url":"https://github.com/mgaido91/spark","homepage":null,"size":284953,"stargazers_count":0,"watchers_count":0,"language":"Scala","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"archived":false,"open_issues_count":0,"license":{"key":"apache-2.0","name":"Apache License 2.0","spdx_id":"Apache-2.0","url":"https://api.github.com/licenses/apache-2.0","node_id":"MDc6TGljZW5zZTI="},"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"apache:master","ref":"master","sha":"a853a80202032083ad411eec5ec97b304f732a61","user":{"login":"apache","id":47359,"node_id":"MDEyOk9yZ2FuaXphdGlvbjQ3MzU5","avatar_url":"https://avatars0.githubusercontent.com/u/47359?v=4","gravatar_id":"","url":"https://api.github.com/users/apache","html_url":"https://github.com/apache","followers_url":"https://api.github.com/users/apache/followers","following_url":"https://api.github.com/users/apache/following{/other_user}","gists_url":"https://api.github.com/users/apache/gists{/gist_id}","starred_url":"https://api.github.com/users/apache/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/apache/subscriptions","organizations_url":"https://api.github.com/users/apache/orgs","repos_url":"https://api.github.com/users/apache/repos","events_url":"https://api.github.com/users/apache/events{/privacy}","received_events_url":"https://api.github.com/users/apache/received_events","type":"Organization","site_admin":false},"repo":{"id":17165658,"node_id":"MDEwOlJlcG9zaXRvcnkxNzE2NTY1OA==","name":"spark","full_name":"apache/spark","private":false,"owner":{"login":"apache","id":47359,"node_id":"MDEyOk9yZ2FuaXphdGlvbjQ3MzU5","avatar_url":"https://avatars0.githubusercontent.com/u/47359?v=4","gravatar_id":"","url":"https://api.github.com/users/apache","html_url":"https://github.com/apache","followers_url":"https://api.github.com/users/apache/followers","following_url":"https://api.github.com/users/apache/following{/other_user}","gists_url":"https://api.github.com/users/apache/gists{/gist_id}","starred_url":"https://api.github.com/users/apache/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/apache/subscriptions","organizations_url":"https://api.github.com/users/apache/orgs","repos_url":"https://api.github.com/users/apache/repos","events_url":"https://api.github.com/users/apache/events{/privacy}","received_events_url":"https://api.github.com/users/apache/received_events","type":"Organization","site_admin":false},"html_url":"https://github.com/apache/spark","description":"Mirror of Apache Spark","fork":false,"url":"https://api.github.com/repos/apache/spark","forks_url":"https://api.github.com/repos/apache/spark/forks","keys_url":"https://api.github.com/repos/apache/spark/keys{/key_id}","collaborators_url":"https://api.github.com/repos/apache/spark/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/apache/spark/teams","hooks_url":"https://api.github.com/repos/apache/spark/hooks","issue_events_url":"https://api.github.com/repos/apache/spark/issues/events{/number}","events_url":"https://api.github.com/repos/apache/spark/events","assignees_url":"https://api.github.com/repos/apache/spark/assignees{/user}","branches_url":"https://api.github.com/repos/apache/spark/branches{/branch}","tags_url":"https://api.github.com/repos/apache/spark/tags","blobs_url":"https://api.github.com/repos/apache/spark/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/apache/spark/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/apache/spark/git/refs{/sha}","trees_url":"https://api.github.com/repos/apache/spark/git/trees{/sha}","statuses_url":"https://api.github.com/repos/apache/spark/statuses/{sha}","languages_url":"https://api.github.com/repos/apache/spark/languages","stargazers_url":"https://api.github.com/repos/apache/spark/stargazers","contributors_url":"https://api.github.com/repos/apache/spark/contributors","subscribers_url":"https://api.github.com/repos/apache/spark/subscribers","subscription_url":"https://api.github.com/repos/apache/spark/subscription","commits_url":"https://api.github.com/repos/apache/spark/commits{/sha}","git_commits_url":"https://api.github.com/repos/apache/spark/git/commits{/sha}","comments_url":"https://api.github.com/repos/apache/spark/comments{/number}","issue_comment_url":"https://api.github.com/repos/apache/spark/issues/comments{/number}","contents_url":"https://api.github.com/repos/apache/spark/contents/{+path}","compare_url":"https://api.github.com/repos/apache/spark/compare/{base}...{head}","merges_url":"https://api.github.com/repos/apache/spark/merges","archive_url":"https://api.github.com/repos/apache/spark/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/apache/spark/downloads","issues_url":"https://api.github.com/repos/apache/spark/issues{/number}","pulls_url":"https://api.github.com/repos/apache/spark/pulls{/number}","milestones_url":"https://api.github.com/repos/apache/spark/milestones{/number}","notifications_url":"https://api.github.com/repos/apache/spark/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/apache/spark/labels{/name}","releases_url":"https://api.github.com/repos/apache/spark/releases{/id}","deployments_url":"https://api.github.com/repos/apache/spark/deployments","created_at":"2014-02-25T08:00:08Z","updated_at":"2018-10-25T15:09:10Z","pushed_at":"2018-10-25T17:19:27Z","git_url":"git://github.com/apache/spark.git","ssh_url":"git@github.com:apache/spark.git","clone_url":"https://github.com/apache/spark.git","svn_url":"https://github.com/apache/spark","homepage":null,"size":285293,"stargazers_count":19227,"watchers_count":19227,"language":"Scala","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":17236,"mirror_url":null,"archived":false,"open_issues_count":514,"license":{"key":"apache-2.0","name":"Apache License 2.0","spdx_id":"Apache-2.0","url":"https://api.github.com/licenses/apache-2.0","node_id":"MDc6TGljZW5zZTI="},"forks":17236,"open_issues":514,"watchers":19227,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/apache/spark/pulls/22029"},"html":{"href":"https://github.com/apache/spark/pull/22029"},"issue":{"href":"https://api.github.com/repos/apache/spark/issues/22029"},"comments":{"href":"https://api.github.com/repos/apache/spark/issues/22029/comments"},"review_comments":{"href":"https://api.github.com/repos/apache/spark/pulls/22029/comments"},"review_comment":{"href":"https://api.github.com/repos/apache/spark/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/apache/spark/pulls/22029/commits"},"statuses":{"href":"https://api.github.com/repos/apache/spark/statuses/321b9a8b9011eacaca0a0c5bd15eef9d0b2277ec"}},"author_association":"CONTRIBUTOR"}}
{"actor":{"display_login":"mgaido91"}}