{"action":"opened","issue":{"url":"https://api.github.com/repos/hadley/dplyr/issues/983","labels_url":"https://api.github.com/repos/hadley/dplyr/issues/983/labels{/name}","comments_url":"https://api.github.com/repos/hadley/dplyr/issues/983/comments","events_url":"https://api.github.com/repos/hadley/dplyr/issues/983/events","html_url":"https://github.com/hadley/dplyr/issues/983","id":58045727,"number":983,"title":"rename() should generate a subquery in SQL translation","user":{"login":"zozlak","id":6503177,"avatar_url":"https://avatars.githubusercontent.com/u/6503177?v=3","gravatar_id":"","url":"https://api.github.com/users/zozlak","html_url":"https://github.com/zozlak","followers_url":"https://api.github.com/users/zozlak/followers","following_url":"https://api.github.com/users/zozlak/following{/other_user}","gists_url":"https://api.github.com/users/zozlak/gists{/gist_id}","starred_url":"https://api.github.com/users/zozlak/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zozlak/subscriptions","organizations_url":"https://api.github.com/users/zozlak/orgs","repos_url":"https://api.github.com/users/zozlak/repos","events_url":"https://api.github.com/users/zozlak/events{/privacy}","received_events_url":"https://api.github.com/users/zozlak/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-02-18T09:40:09Z","updated_at":"2015-02-18T09:40:09Z","closed_at":null,"body":"At the moment the rename verb does not encapsulate a resulting SQL query in a subquery which leads to an unintuitive behaviour:\r\n\r\n```r\r\nlibrary(dplyr)\r\n\r\nsrcL = src_sqlite(\"test.sqlite3\", create = T)\r\ndataL = copy_to(srcL, mtcars)\r\ndataL %>% rename(aaa = cyl) %>% filter(cyl == 6, aaa == 6) # surprisingly works\r\ndataL %>% rename(aaa = cyl) %>% filter(cyl == 6)                 # works\r\ndataL %>% rename(aaa = cyl) %>% filter(aaa == 6)               # works\r\n\r\nsrcP = src_postgres({put a valid connection config here})\r\ndataP = copy_to(srcP, mtcars, temporary = TRUE)\r\ndataP %>% rename(aaa = cyl) %>% filter(cyl == 6, aaa == 6) # fails\r\ndataP %>% rename(aaa = cyl) %>% filter(cyl == 6)                 # works\r\ndataP %>% rename(aaa = cyl) %>% filter(aaa == 6)               # fails\r\n\r\nsrcM = src_mysql({put a valid connection config here})\r\ndataM = copy_to(srcM, mtcars, temporary = TRUE)\r\ndataM %>% rename(aaa = cyl) %>% filter(cyl == 6, aaa == 6) # fails\r\ndataM %>% rename(aaa = cyl) %>% filter(cyl == 6)                 # works\r\ndataM %>% rename(aaa = cyl) %>% filter(aaa == 6)               # fails\r\n```\r\n\r\nAs we can see MySQL and PostgreSQL compares WITH clause statements with column's names before renaming.\r\n\r\nTo resolve this issue, the rename verb should generate a subquery:\r\n```r\r\n(daneL %>% rename(aaa = cyl))$query\r\n# now:\r\n# SELECT \"mpg\" AS \"mpg\", \"cyl\" AS \"aaa\", \"disp\" AS \"disp\", \"hp\" AS \"hp\", \"drat\" AS \"drat\", \"wt\" AS \"wt\", \"qsec\" AS \"qsec\", \"vs\" AS \"vs\", \"am\" AS \"am\", \"gear\" AS \"gear\", \"carb\" AS \"carb\"\r\n# FROM \"mtcars\"\r\n#\r\n# should be:\r\n# SELECT \"mpg\", \"aaa\", \"disp\", \"hp\", \"drat\", \"wt\", \"qsec\", \"vs\", \"am\", \"gear\", \"carb\"\r\n# FROM (\r\n#   SELECT \"mpg\", \"cyl\" AS \"aaa\", \"disp\", \"hp\", \"drat\", \"wt\", \"qsec\", \"vs\", \"am\", \"gear\", \"carb\"\r\n#   FROM \"mtcars\"\r\n# ) AS \"_W1\"\r\n```"}}