{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/9120","labels_url":"https://api.github.com/repos/apache/spark/issues/9120/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/9120/comments","events_url":"https://api.github.com/repos/apache/spark/issues/9120/events","html_url":"https://github.com/apache/spark/pull/9120","id":111483393,"number":9120,"title":"[SPARK-11111] [SQL] fast null-safe join","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":4,"created_at":"2015-10-14T20:16:36Z","updated_at":"2015-10-16T20:32:13Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/9120","html_url":"https://github.com/apache/spark/pull/9120","diff_url":"https://github.com/apache/spark/pull/9120.diff","patch_url":"https://github.com/apache/spark/pull/9120.patch"},"body":"Currently, we use CartesianProduct for join with null-safe-equal condition.\r\n```\r\nscala> sqlContext.sql(\"select * from t a join t b on (a.i <=> b.i)\").explain\r\n== Physical Plan ==\r\nTungstenProject [i#2,j#3,i#7,j#8]\r\n Filter (i#2 <=> i#7)\r\n  CartesianProduct\r\n   LocalTableScan [i#2,j#3], [[1,1]]\r\n   LocalTableScan [i#7,j#8], [[1,1]]\r\n```\r\nActually, we can have an equal-join condition as  `coalesce(i, default) = coalesce(b.i, default)`, then an partitioned join algorithm could be used.\r\n\r\nAfter this PR, the plan will become:\r\n```\r\n>>> sqlContext.sql(\"select * from a join b ON a.id <=> b.id\").explain()\r\nTungstenProject [id#0L,id#1L]\r\n Filter (id#0L <=> id#1L)\r\n  SortMergeJoin [coalesce(id#0L,0)], [coalesce(id#1L,0)]\r\n   TungstenSort [coalesce(id#0L,0) ASC], false, 0\r\n    TungstenExchange hashpartitioning(coalesce(id#0L,0),200)\r\n     ConvertToUnsafe\r\n      Scan PhysicalRDD[id#0L]\r\n   TungstenSort [coalesce(id#1L,0) ASC], false, 0\r\n    TungstenExchange hashpartitioning(coalesce(id#1L,0),200)\r\n     ConvertToUnsafe\r\n      Scan PhysicalRDD[id#1L]\r\n```"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/148826592","html_url":"https://github.com/apache/spark/pull/9120#issuecomment-148826592","issue_url":"https://api.github.com/repos/apache/spark/issues/9120","id":148826592,"user":{"login":"AmplabJenkins","id":4031291,"avatar_url":"https://avatars.githubusercontent.com/u/4031291?v=3","gravatar_id":"","url":"https://api.github.com/users/AmplabJenkins","html_url":"https://github.com/AmplabJenkins","followers_url":"https://api.github.com/users/AmplabJenkins/followers","following_url":"https://api.github.com/users/AmplabJenkins/following{/other_user}","gists_url":"https://api.github.com/users/AmplabJenkins/gists{/gist_id}","starred_url":"https://api.github.com/users/AmplabJenkins/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/AmplabJenkins/subscriptions","organizations_url":"https://api.github.com/users/AmplabJenkins/orgs","repos_url":"https://api.github.com/users/AmplabJenkins/repos","events_url":"https://api.github.com/users/AmplabJenkins/events{/privacy}","received_events_url":"https://api.github.com/users/AmplabJenkins/received_events","type":"User","site_admin":false},"created_at":"2015-10-16T20:32:12Z","updated_at":"2015-10-16T20:32:12Z","body":" Merged build triggered."}}