row_number(desc(x)) produces invalid SQL

[donour]

---

The following example produces a non-compliant SQL query on dbplyr 2.3.0, but works correctly with 2.2.

#######################################################################
# this is site specific code needed to reproduce the error on a live DB
con <- dbi_connect()
######################################################################

tbl(con, sql("test_table")) %>%
     select(OBJ_TYPE, CREATED_AT) %>%
           group_by(OBJ_TYPE) %>%
                 filter(1L == row_number(desc(CREATED_AT))) %>%
                       show_query()
                       
 ...
 
 <SQL>
SELECT "OBJ_TYPE", "CREATED_AT"
FROM (
  SELECT
    "OBJ_TYPE",
    "CREATED_AT",
    CASE
WHEN (NOT(("CREATED_AT" DESC IS NULL))) THEN ROW_NUMBER() OVER (PARTITION BY "OBJ_TYPE", (CASE WHEN (("CREATED_AT" DESC IS NULL)) THEN 1 ELSE 0 END) ORDER BY "CREATED_AT" DESC)
END AS "q02"
  FROM (test_table) "q01"
) "q02"
WHERE (1 = "q02")
> 
                      

[hadley]

Moving to the dbplyr repo.

[donour]

thanks @hadley !

[mgirlich]

Thanks for creating the issue! Should be fixed in the dev version 😄

[donour]

woot!