Error using dbplyr 1.4 with row_number()

cderv · May 1, 2019, 8:48pm

Ok ! writing my answer and looking again I found the error. Just a typo that introduced the regression. I'll do a PR

EDIT: I pushed a fix in this PR. You could try it

github.com/tidyverse/dbplyr

Hive translations for window functions were no more working

tidyverse:master ← cderv:hive-window

opened 08:54PM - 01 May 19 UTC

cderv

+3 -1

This comes from a question on [community.rstudio.com](https://community.rstudio.…com/t/error-using-dbplyr-1-4-with-row-number/29779/5) In dbplyr 1.3.0 ```r library(dplyr) library(dbplyr) tab <- tbl_lazy(iris, src = simulate_hive()) query <- tab %>% group_by(Species) %>% filter(row_number() == 1L) query %>% show_query() <SQL> SELECT `Sepal.Length`, `Sepal.Width`, `Petal.Length`, `Petal.Width`, `Species` FROM (SELECT `Sepal.Length`, `Sepal.Width`, `Petal.Length`, `Petal.Width`, `Species`, row_number() OVER (PARTITION BY `Species`) AS `zzz1` FROM `df`) `ohjggmqvbj` WHERE (`zzz1` = 1) ``` With 1.4.0 ```r tab <- tbl_lazy(iris, src = simulate_hive()) query <- tab %>% group_by(Species) %>% filter(row_number() == 1L) query %>% show_query() <SQL> SELECT * FROM `df` WHERE (row_number() = 1) ``` A `git bisect` points to this commit: f4fcfdc9f73d56bbf2d93eb705b57709ccae52e5. This comes from a small typo - odbc window function were no more used. This PR changes that and now it is working as before ```r tab <- tbl_lazy(iris, src = simulate_hive()) query <- tab %>% group_by(Species) %>% filter(row_number() == 1L) query %>% show_query() <SQL> SELECT `Sepal.Length`, `Sepal.Width`, `Petal.Length`, `Petal.Width`, `Species` FROM (SELECT `Sepal.Length`, `Sepal.Width`, `Petal.Length`, `Petal.Width`, `Species`, ROW_NUMBER() OVER (PARTITION BY `Species`) AS `zzz1` FROM `df`) `dbplyr_001` WHERE (`zzz1` = 1) ``` I can add tests later if you want. There are not so many tests for hive curently, and nothing about window function