{"action":"created","comment":{"url":"https://api.github.com/repos/apache/spark/pulls/comments/49236301","id":49236301,"diff_hunk":"@@ -37,6 +37,8 @@ abstract class Optimizer extends RuleExecutor[LogicalPlan] {\n     // SubQueries are only needed for analysis and can be removed before execution.\n     Batch(\"Remove SubQueries\", FixedPoint(100),\n       EliminateSubQueries) ::\n+    Batch(\"Compute Current Time\", Once,\n+      ComputeCurrentTime) ::","path":"sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala","position":5,"original_position":5,"commit_id":"97cd39e146ce1a4b49e3ca01b8a44906d7b19351","original_commit_id":"97cd39e146ce1a4b49e3ca01b8a44906d7b19351","user":{"login":"yhuai","id":2072857,"avatar_url":"https://avatars.githubusercontent.com/u/2072857?v=3","gravatar_id":"","url":"https://api.github.com/users/yhuai","html_url":"https://github.com/yhuai","followers_url":"https://api.github.com/users/yhuai/followers","following_url":"https://api.github.com/users/yhuai/following{/other_user}","gists_url":"https://api.github.com/users/yhuai/gists{/gist_id}","starred_url":"https://api.github.com/users/yhuai/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yhuai/subscriptions","organizations_url":"https://api.github.com/users/yhuai/orgs","repos_url":"https://api.github.com/users/yhuai/repos","events_url":"https://api.github.com/users/yhuai/events{/privacy}","received_events_url":"https://api.github.com/users/yhuai/received_events","type":"User","site_admin":false},"body":"Let's add a comment to explain it in the follow-up pr.","created_at":"2016-01-08T20:47:45Z","updated_at":"2016-01-08T20:47:45Z","html_url":"https://github.com/apache/spark/pull/10541#discussion_r49236301","pull_request_url":"https://api.github.com/repos/apache/spark/pulls/10541","_links":{"self":{"href":"https://api.github.com/repos/apache/spark/pulls/comments/49236301"},"html":{"href":"https://github.com/apache/spark/pull/10541#discussion_r49236301"},"pull_request":{"href":"https://api.github.com/repos/apache/spark/pulls/10541"}}},"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/10541","id":54862897,"html_url":"https://github.com/apache/spark/pull/10541","diff_url":"https://github.com/apache/spark/pull/10541.diff","patch_url":"https://github.com/apache/spark/pull/10541.patch","issue_url":"https://api.github.com/repos/apache/spark/issues/10541","number":10541,"state":"open","locked":false,"title":"[SPARK-12593][SQL] Converts resolved logical plan back to SQL","user":{"login":"liancheng","id":230655,"avatar_url":"https://avatars.githubusercontent.com/u/230655?v=3","gravatar_id":"","url":"https://api.github.com/users/liancheng","html_url":"https://github.com/liancheng","followers_url":"https://api.github.com/users/liancheng/followers","following_url":"https://api.github.com/users/liancheng/following{/other_user}","gists_url":"https://api.github.com/users/liancheng/gists{/gist_id}","starred_url":"https://api.github.com/users/liancheng/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/liancheng/subscriptions","organizations_url":"https://api.github.com/users/liancheng/orgs","repos_url":"https://api.github.com/users/liancheng/repos","events_url":"https://api.github.com/users/liancheng/events{/privacy}","received_events_url":"https://api.github.com/users/liancheng/received_events","type":"User","site_admin":false},"body":"This PR tries to enable Spark SQL to convert resolved logical plans back to SQL query strings.  For now, the major use case is to canonicalize Spark SQL native view support.  The major entry point is `SQLBuilder.toSQL`, which returns an `Option[String]` if the logical plan is recognized.\r\n\r\nThe current version is still in WIP status, and is quite limited.  Known limitations include:\r\n\r\n1.  The logical plan must be analyzed but not optimized\r\n\r\n    The optimizer erases `Subquery` operators, which contain necessary scope information for SQL generation.  Future versions should be able to recover erased scope information by inserting subqueries when necessary.\r\n\r\n1.  The logical plan must be created using HiveQL query string\r\n\r\n    Query plans generated by composing arbitrary DataFrame API combinations are not supported yet.  Operators within these query plans need to be rearranged into a canonical form that is more suitable for direct SQL generation.  For example, the following query plan\r\n\r\n    ```\r\n    Filter (a#1 < 10)\r\n     +- MetastoreRelation default, src, None\r\n    ```\r\n\r\n    need to be canonicalized into the following form before SQL generation:\r\n\r\n    ```\r\n    Project [a#1, b#2, c#3]\r\n     +- Filter (a#1 < 10)\r\n         +- MetastoreRelation default, src, None\r\n    ```\r\n\r\n    Otherwise, the SQL generation process will have to handle a large number of special cases.\r\n\r\n1.  Only a fraction of expressions and basic logical plan operators are supported in this PR\r\n\r\n    Currently, 95.7% (1720 out of 1798) query plans in `HiveCompatibilitySuite` can be successfully converted to SQL query strings.\r\n\r\n    Known unsupported components are:\r\n\r\n    - Expressions\r\n      - Part of math expressions\r\n      - Part of string expressions (buggy?)\r\n      - Null expressions\r\n      - Calendar interval literal\r\n      - Part of date time expressions\r\n      - Complex type creators\r\n      - Special `NOT` expressions, e.g. `NOT LIKE` and `NOT IN`\r\n    - Logical plan operators/patterns\r\n      - Cube, rollup, and grouping set\r\n      - Script transformation\r\n      - Generator\r\n      - Distinct aggregation patterns that fit `DistinctAggregationRewriter` analysis rule\r\n      - Window functions\r\n\r\n    Support for window functions, generators, and cubes etc. will be added in follow-up PRs.\r\n\r\nThis PR leverages `HiveCompatibilitySuite` for testing SQL generation in a \"round-trip\" manner:\r\n\r\n*   For all select queries, we try to convert it back to SQL\r\n*   If the query plan is convertible, we parse the generated SQL into a new logical plan\r\n*   Run the new logical plan instead of the original one\r\n\r\nIf the query plan is inconvertible, the test case simply falls back to the original logic.\r\n\r\nTODO\r\n\r\n- [x] Fix failed test cases\r\n- [x] Support for more basic expressions and logical plan operators (e.g. distinct aggregation etc.)\r\n- [x] Comments and documentation","created_at":"2015-12-31T12:50:51Z","updated_at":"2016-01-08T20:47:45Z","closed_at":null,"merged_at":null,"merge_commit_sha":"e83332b41a5726187773fce3804b249a9bbb5c97","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/apache/spark/pulls/10541/commits","review_comments_url":"https://api.github.com/repos/apache/spark/pulls/10541/comments","review_comment_url":"https://api.github.com/repos/apache/spark/pulls/comments{/number}","comments_url":"https://api.github.com/repos/apache/spark/issues/10541/comments","statuses_url":"https://api.github.com/repos/apache/spark/statuses/97cd39e146ce1a4b49e3ca01b8a44906d7b19351","head":{"label":"liancheng:sql-generation","ref":"sql-generation","sha":"97cd39e146ce1a4b49e3ca01b8a44906d7b19351","user":{"login":"liancheng","id":230655,"avatar_url":"https://avatars.githubusercontent.com/u/230655?v=3","gravatar_id":"","url":"https://api.github.com/users/liancheng","html_url":"https://github.com/liancheng","followers_url":"https://api.github.com/users/liancheng/followers","following_url":"https://api.github.com/users/liancheng/following{/other_user}","gists_url":"https://api.github.com/users/liancheng/gists{/gist_id}","starred_url":"https://api.github.com/users/liancheng/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/liancheng/subscriptions","organizations_url":"https://api.github.com/users/liancheng/orgs","repos_url":"https://api.github.com/users/liancheng/repos","events_url":"https://api.github.com/users/liancheng/events{/privacy}","received_events_url":"https://api.github.com/users/liancheng/received_events","type":"User","site_admin":false},"repo":{"id":17232535,"name":"spark","full_name":"liancheng/spark","owner":{"login":"liancheng","id":230655,"avatar_url":"https://avatars.githubusercontent.com/u/230655?v=3","gravatar_id":"","url":"https://api.github.com/users/liancheng","html_url":"https://github.com/liancheng","followers_url":"https://api.github.com/users/liancheng/followers","following_url":"https://api.github.com/users/liancheng/following{/other_user}","gists_url":"https://api.github.com/users/liancheng/gists{/gist_id}","starred_url":"https://api.github.com/users/liancheng/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/liancheng/subscriptions","organizations_url":"https://api.github.com/users/liancheng/orgs","repos_url":"https://api.github.com/users/liancheng/repos","events_url":"https://api.github.com/users/liancheng/events{/privacy}","received_events_url":"https://api.github.com/users/liancheng/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/liancheng/spark","description":"Mirror of Apache Spark","fork":true,"url":"https://api.github.com/repos/liancheng/spark","forks_url":"https://api.github.com/repos/liancheng/spark/forks","keys_url":"https://api.github.com/repos/liancheng/spark/keys{/key_id}","collaborators_url":"https://api.github.com/repos/liancheng/spark/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/liancheng/spark/teams","hooks_url":"https://api.github.com/repos/liancheng/spark/hooks","issue_events_url":"https://api.github.com/repos/liancheng/spark/issues/events{/number}","events_url":"https://api.github.com/repos/liancheng/spark/events","assignees_url":"https://api.github.com/repos/liancheng/spark/assignees{/user}","branches_url":"https://api.github.com/repos/liancheng/spark/branches{/branch}","tags_url":"https://api.github.com/repos/liancheng/spark/tags","blobs_url":"https://api.github.com/repos/liancheng/spark/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/liancheng/spark/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/liancheng/spark/git/refs{/sha}","trees_url":"https://api.github.com/repos/liancheng/spark/git/trees{/sha}","statuses_url":"https://api.github.com/repos/liancheng/spark/statuses/{sha}","languages_url":"https://api.github.com/repos/liancheng/spark/languages","stargazers_url":"https://api.github.com/repos/liancheng/spark/stargazers","contributors_url":"https://api.github.com/repos/liancheng/spark/contributors","subscribers_url":"https://api.github.com/repos/liancheng/spark/subscribers","subscription_url":"https://api.github.com/repos/liancheng/spark/subscription","commits_url":"https://api.github.com/repos/liancheng/spark/commits{/sha}","git_commits_url":"https://api.github.com/repos/liancheng/spark/git/commits{/sha}","comments_url":"https://api.github.com/repos/liancheng/spark/comments{/number}","issue_comment_url":"https://api.github.com/repos/liancheng/spark/issues/comments{/number}","contents_url":"https://api.github.com/repos/liancheng/spark/contents/{+path}","compare_url":"https://api.github.com/repos/liancheng/spark/compare/{base}...{head}","merges_url":"https://api.github.com/repos/liancheng/spark/merges","archive_url":"https://api.github.com/repos/liancheng/spark/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/liancheng/spark/downloads","issues_url":"https://api.github.com/repos/liancheng/spark/issues{/number}","pulls_url":"https://api.github.com/repos/liancheng/spark/pulls{/number}","milestones_url":"https://api.github.com/repos/liancheng/spark/milestones{/number}","notifications_url":"https://api.github.com/repos/liancheng/spark/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/liancheng/spark/labels{/name}","releases_url":"https://api.github.com/repos/liancheng/spark/releases{/id}","created_at":"2014-02-27T01:39:03Z","updated_at":"2015-03-31T08:56:24Z","pushed_at":"2016-01-08T20:17:25Z","git_url":"git://github.com/liancheng/spark.git","ssh_url":"git@github.com:liancheng/spark.git","clone_url":"https://github.com/liancheng/spark.git","svn_url":"https://github.com/liancheng/spark","homepage":null,"size":130567,"stargazers_count":1,"watchers_count":1,"language":"Scala","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":1,"default_branch":"master"}},"base":{"label":"apache:master","ref":"master","sha":"6b6d02be0d4e2ce562dddfb391b3302f79de8276","user":{"login":"apache","id":47359,"avatar_url":"https://avatars.githubusercontent.com/u/47359?v=3","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,"name":"spark","full_name":"apache/spark","owner":{"login":"apache","id":47359,"avatar_url":"https://avatars.githubusercontent.com/u/47359?v=3","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},"private":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}","created_at":"2014-02-25T08:00:08Z","updated_at":"2016-01-08T20:00:54Z","pushed_at":"2016-01-08T20:39:09Z","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":397047,"stargazers_count":6921,"watchers_count":6921,"language":"Scala","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":6350,"mirror_url":"git://git.apache.org/spark.git","open_issues_count":341,"forks":6350,"open_issues":341,"watchers":6921,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/apache/spark/pulls/10541"},"html":{"href":"https://github.com/apache/spark/pull/10541"},"issue":{"href":"https://api.github.com/repos/apache/spark/issues/10541"},"comments":{"href":"https://api.github.com/repos/apache/spark/issues/10541/comments"},"review_comments":{"href":"https://api.github.com/repos/apache/spark/pulls/10541/comments"},"review_comment":{"href":"https://api.github.com/repos/apache/spark/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/apache/spark/pulls/10541/commits"},"statuses":{"href":"https://api.github.com/repos/apache/spark/statuses/97cd39e146ce1a4b49e3ca01b8a44906d7b19351"}}}}