You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
MSSQL is sensitive to subqueries that have an ORDER BY clause -- they are not allowed. A suggested workaround is to use TOP 100 PERCENT, but I believe we can also solve this by more accurate optimization.
This is an actual problem if window functions are involved, in this case it's not easy to move the arrange() as noted in #94 (comment).
library(tidyverse)
library(dbplyr)
df<- lazy_frame(x=1:4, g= rep(c(1, 2), each=2), con= simulate_mssql())
df %>%
group_by(g) %>%
arrange(x) %>%
mutate(r= cumsum(x))
#> <SQL>#> SELECT `x`, `g`, SUM(`x`) OVER (PARTITION BY `g` ORDER BY `x` ROWS UNBOUNDED PRECEDING) AS `r`#> FROM (SELECT *#> FROM `df`#> ORDER BY `x`) `dbplyr_001`
Created on 2019-04-09 by the reprex package (v0.2.1.9000)
The text was updated successfully, but these errors were encountered:
krlmlr
changed the title
Postpone op_arrange()
Move ORDER BY clause to outermost SELECT
Apr 9, 2019
krlmlr
changed the title
Move ORDER BY clause to outermost SELECT
Support ORDER BY in subqueries for MSSQL
Apr 9, 2019
MSSQL is sensitive to subqueries that have an
ORDER BY
clause -- they are not allowed. A suggested workaround is to useTOP 100 PERCENT
, but I believe we can also solve this by more accurate optimization.This is an actual problem if window functions are involved, in this case it's not easy to move the
arrange()
as noted in #94 (comment).Created on 2019-04-09 by the reprex package (v0.2.1.9000)
The text was updated successfully, but these errors were encountered: