{"action":"created","issue":{"url":"https://api.github.com/repos/tidyverse/dplyr/issues/3080","repository_url":"https://api.github.com/repos/tidyverse/dplyr","labels_url":"https://api.github.com/repos/tidyverse/dplyr/issues/3080/labels{/name}","comments_url":"https://api.github.com/repos/tidyverse/dplyr/issues/3080/comments","events_url":"https://api.github.com/repos/tidyverse/dplyr/issues/3080/events","html_url":"https://github.com/tidyverse/dplyr/issues/3080","id":255249570,"number":3080,"title":"Incorrect SQL generated against Oracle databases when no schema is specified","user":{"login":"mungojam","id":3154635,"avatar_url":"https://avatars1.githubusercontent.com/u/3154635?v=4","gravatar_id":"","url":"https://api.github.com/users/mungojam","html_url":"https://github.com/mungojam","followers_url":"https://api.github.com/users/mungojam/followers","following_url":"https://api.github.com/users/mungojam/following{/other_user}","gists_url":"https://api.github.com/users/mungojam/gists{/gist_id}","starred_url":"https://api.github.com/users/mungojam/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mungojam/subscriptions","organizations_url":"https://api.github.com/users/mungojam/orgs","repos_url":"https://api.github.com/users/mungojam/repos","events_url":"https://api.github.com/users/mungojam/events{/privacy}","received_events_url":"https://api.github.com/users/mungojam/received_events","type":"User","site_admin":false},"labels":[{"id":17708198,"url":"https://api.github.com/repos/tidyverse/dplyr/labels/bug","name":"bug","color":"e02a2a","default":true},{"id":213548409,"url":"https://api.github.com/repos/tidyverse/dplyr/labels/database","name":"database","color":"eeeeee","default":false}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":7,"created_at":"2017-09-05T11:46:16Z","updated_at":"2017-10-27T22:11:23Z","closed_at":"2017-10-26T18:38:57Z","author_association":"NONE","body":"When using tbl() with our Oracle database, we have to provide the schema name using in_schema. But when calling dbGetQuery directly we seem to get away without it.\r\n\r\nLooks like this would be fixed by removing the brackets and speech marks from the table name if there is no schema:\r\n\r\n```r\r\n#Setup connection\r\ncon <- dbConnect(\r\n                odbc::odbc(),\r\n                driver = \"Oracle in INSTANTCLIENT_12_1\",\r\n                Dbq = \"TheDatabase\"\r\n            )\r\n\r\n#This works\r\ncon %>% dbGetQuery(\"select * from my_table\")\r\n\r\n#This doesn't\r\ncon %>% tbl(\"my_table\")\r\n\r\n#Error: <SQL> 'SELECT *\r\n#FROM (\"my_table\") \"zzz16\"\r\n#WHERE (0 = 1)'\r\n#nanodbc/nanodbc.cpp:1587: 42S02: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\r\n\r\n#Needs the schema to be specified to make it work\r\ncon %>% tbl(in_schema(\"SCHEMA_OWNER\", \"my_table\"))\r\n\r\n#Manually running the query without brackets or speech marks works:\r\ncon %>% dbGetQuery(\"SELECT * FROM my_table \\\"zzz16\\\" WHERE (0 = 1)\")\r\n\r\n#With speech marks doesn't (as per the error message)\r\ncon %>% dbGetQuery(\"SELECT * FROM (\\\"my_table\\\") \\\"zzz16\\\" WHERE (0 = 1)\")\r\n```\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/tidyverse/dplyr/issues/comments/340109715","html_url":"https://github.com/tidyverse/dplyr/issues/3080#issuecomment-340109715","issue_url":"https://api.github.com/repos/tidyverse/dplyr/issues/3080","id":340109715,"user":{"login":"hadley","id":4196,"avatar_url":"https://avatars3.githubusercontent.com/u/4196?v=4","gravatar_id":"","url":"https://api.github.com/users/hadley","html_url":"https://github.com/hadley","followers_url":"https://api.github.com/users/hadley/followers","following_url":"https://api.github.com/users/hadley/following{/other_user}","gists_url":"https://api.github.com/users/hadley/gists{/gist_id}","starred_url":"https://api.github.com/users/hadley/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hadley/subscriptions","organizations_url":"https://api.github.com/users/hadley/orgs","repos_url":"https://api.github.com/users/hadley/repos","events_url":"https://api.github.com/users/hadley/events{/privacy}","received_events_url":"https://api.github.com/users/hadley/received_events","type":"User","site_admin":false},"created_at":"2017-10-27T22:11:23Z","updated_at":"2017-10-27T22:11:23Z","author_association":"OWNER","body":"Yeah, unfortunately while that would solve your immediate problem, my sense is that it will create more problems in the longer run."}}
{"actor":{"display_login":"hadley"}}