{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/9451","labels_url":"https://api.github.com/repos/apache/spark/issues/9451/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/9451/comments","events_url":"https://api.github.com/repos/apache/spark/issues/9451/events","html_url":"https://github.com/apache/spark/pull/9451","id":114932016,"number":9451,"title":"[SPARK-9372] [SQL] Filter nulls in Inner joins (null-skew)","user":{"login":"vidma","id":213426,"avatar_url":"https://avatars.githubusercontent.com/u/213426?v=3","gravatar_id":"","url":"https://api.github.com/users/vidma","html_url":"https://github.com/vidma","followers_url":"https://api.github.com/users/vidma/followers","following_url":"https://api.github.com/users/vidma/following{/other_user}","gists_url":"https://api.github.com/users/vidma/gists{/gist_id}","starred_url":"https://api.github.com/users/vidma/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vidma/subscriptions","organizations_url":"https://api.github.com/users/vidma/orgs","repos_url":"https://api.github.com/users/vidma/repos","events_url":"https://api.github.com/users/vidma/events{/privacy}","received_events_url":"https://api.github.com/users/vidma/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2015-11-03T23:13:07Z","updated_at":"2015-11-08T12:42:08Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/9451","html_url":"https://github.com/apache/spark/pull/9451","diff_url":"https://github.com/apache/spark/pull/9451.diff","patch_url":"https://github.com/apache/spark/pull/9451.patch"},"body":"**Do not merge yet: Work in progress / waiting for comments**\r\n\r\nDraft of first step in optimizing skew in joins (it is quite common to have skew in data, and lots of `nulls` on either side of join is quite common (for us), especially with left join, say when joining `dimensions` to `fact` tables)\r\n\r\nfeel free to propose a better approach / add commits.\r\n\r\nany ideas for an easy way to check if the rule was already applied?  After adding a `isNotNull` filter `someAttribute.nullable`  still returns `true`. I couldn't come up with anything better than simply doing a separate batch of 1 iteration.\r\n\r\n@marmbrus  (as discussed at Spark Summit EU)\r\n\r\n---\r\n\r\nthe next more serious step will be to fight skew in left join, where most helpers of this PR will be reused.\r\n\r\nhere is a [rather simple implementation with DataFrames](https://gist.github.com/vidma/98332db0f82e7e5b09e5), solves the null skew, and don't seem to add lots of overhead (though tried only on subset of all our joins which used another abstraction of ours).\r\n\r\nhowever this, so far, seems harder to express in optimizer rules:\r\n- need to add \"fake\" colums. no idea yet how to do this to be able to refer to the added column in join conditions\r\n```scala\r\nval leftNullsSprayValue = CaseWhen(\r\n      Seq(\r\n        nullableJoinKeys(left).map(IsNull).reduceLeft(Or), // if any join keys are null\r\n        Cast(Multiply(new Rand(), Literal(100000)), IntegerType),\r\n        Literal(0) // otherwise\r\n      ))\r\n// but how to add this column to left & right relations?\r\n// e.g. this fails, saying it's not `resolved`\r\nAlias(leftNullsSprayValue)(\"leftNullsSprayKey\")()\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/154821753","html_url":"https://github.com/apache/spark/pull/9451#issuecomment-154821753","issue_url":"https://api.github.com/repos/apache/spark/issues/9451","id":154821753,"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-11-08T12:42:08Z","updated_at":"2015-11-08T12:42:08Z","body":"**[Test build #45301 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/45301/consoleFull)** for PR 9451 at commit [`0fa27c4`](https://github.com/apache/spark/commit/0fa27c46b7e423ad43261f625ed6d9a007465882)."}}