Bug in transmute translation

[mgirlich]

There is an error in the SQL generation for transmute

library(dplyr)
library(dbplyr)

df <- data.frame(x = 1, y = 2)

# doesn't work
tbl_lazy(df, con = simulate_postgres()) %>% 
  transmute(x, x2 = x + y, y)
#> <SQL>
#> SELECT `x`, `x` + `y` AS `x2`, `y`
#> FROM (SELECT `x`
#> FROM `df`) `dbplyr_001`

# works
tbl_lazy(df, con = simulate_postgres()) %>% 
  transmute(x, y, x2 = x + y)
#> <SQL>
#> SELECT `x`, `y`, `x` + `y` AS `x2`
#> FROM (SELECT `x`, `y`
#> FROM `df`) `dbplyr_002`

^{Created on 2019-06-04 by the reprex package (v0.3.0)}

^{Created on 2019-06-04 by the reprex package (v0.3.0)}

[hadley]

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(x = 1, y = 2)
transmute(lf, x, x2 = x + y, y)
#> <SQL>
#> SELECT `x`, `x` + `y` AS `x2`, `y`
#> FROM (SELECT `x`
#> FROM `df`) `dbplyr_001`

^{Created on 2019-06-04 by the reprex package (v0.2.1.9000)}

[hadley]

Probably overly aggressive query optimisation

[hadley]

Interestingly, the equivalent mutate() works too:

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(x = 1, y = 2)
transmute(lf, x, x2 = x + y, y)
#> <SQL>
#> SELECT `x`, `x` + `y` AS `x2`, `y`
#> FROM (SELECT `x`
#> FROM `df`) `q01`
mutate(lf, x, x2 = x + y, y)
#> <SQL>
#> SELECT `x`, `y`, `x` + `y` AS `x2`
#> FROM (SELECT `x`, `y`
#> FROM `df`) `q01`

^{Created on 2020-09-24 by the reprex package (v0.3.0.9001)}