Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

TOP 100 PERCENT is issued for MSSQL in absence of ORDER BY, breaking support for Azure DW #337

Closed
alexkyllo opened this issue Jul 24, 2019 · 2 comments
Labels
bug an unexpected problem or unintended behavior verb trans 🤖 Translation of dplyr verbs to SQL
Milestone

Comments

@alexkyllo
Copy link
Contributor

@alexkyllo alexkyllo commented Jul 24, 2019

PR #277 causes the mssql backend to issue SELECT TOP 100 PERCENT in sub queries even if there is no ORDER BY clause. This breaks dbplyr for Azure Data Warehouse, which is a version of MSSQL that does not support SELECT [...] PERCENT syntax. I'm working on a PR to change this so that TOP 100 PERCENT at least only gets issued if there is actually an ORDER BY, so that ADW users can use dbplyr again.

mf <- lazy_frame(x = 1:3, con = simulate_mssql())
mf %>% mutate(x = -x) %>% mutate(x = -x) %>% sql_render()
#> <SQL> SELECT -`x` AS `x`
#> FROM (SELECT TOP 100 PERCENT -`x` AS `x`
#> FROM `df`) `dbplyr_001`
@filipwastberg
Copy link

@filipwastberg filipwastberg commented Oct 17, 2019

#277 also breaks code for Parallell Data Warehouse for MSSQL which doesn't support TOP ... PERCENT

@filipwastberg
Copy link

@filipwastberg filipwastberg commented Oct 22, 2019

We hade to roll back to dbplyr 1.4.0 to be able to use dplyr with our database.

If anyone wonder how: devtools::install_version("dbplyr", version = "1.4.0", repos = "http://cran.us.r-project.org")

@hadley hadley added bug an unexpected problem or unintended behavior verb trans 🤖 Translation of dplyr verbs to SQL labels Dec 13, 2019
@hadley hadley added this to the 2.0.0 milestone Dec 13, 2019
alexkyllo added a commit to alexkyllo/dbplyr that referenced this issue May 22, 2020
@krlmlr krlmlr closed this as completed in 6e2d200 May 23, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug an unexpected problem or unintended behavior verb trans 🤖 Translation of dplyr verbs to SQL
Projects
None yet
Development

No branches or pull requests

3 participants