`mutate()` is incorrectly inlined after `distinct()`

[vadim-cherepanov]

I just updated to dplyr v1.1.0 + dbplyr 2.3.0, and my code broke. Upon investigation it seems that now a wrong SQL query is generated. Namely, in my case, I have distinct prior to the group_by block.

> remote_table %>% distinct(x, y) %>% count(y) %>% filter(y == "YYY") 
# Source:     SQL [1 x 2]
# Database:   postgres  [.../postgres]
# Ordered by: x, y
  y        n
  <chr>  <int64>
1 YYY       1
> remote_table %>% distinct(x, y) %>% group_by(y) %>% tally() %>% ungroup() %>% filter(y == "YYY") 
# Source:     SQL [1 x 2]
# Database:   postgres  [.../postgres]
# Ordered by: x, y
  y        n
  <chr>  <int64>
1 YYY       1
> remote_table %>% distinct(x, y) %>% group_by(y) %>% mutate(n = n()) %>% ungroup() %>% filter(y == "YYY") 
# Source:     SQL [1 x 3]
# Database:   postgres  [.../postgres]
# Ordered by: x, y
  x y        n
      <int64> <chr>  <int64>
1        XXX YYY       2

If I add compute between them, i.e. the result of distinct is actually computed and stored in a temporary table, n() returns values as expected.

> remote_table %>% distinct(x, y) %>% compute() %>% group_by(y) %>% mutate(n = n()) %>% ungroup() %>% filter(y == "YYY") 
# Source:     SQL [1 x 3]
# Database:   postgres  [.../postgres]
# Ordered by: x, y
  x y        n
      <int64> <chr>  <int64>
1        XXX YYY       1

This is the SQL query generated by dplyr v1.1.0 + dbplyr 2.3.0:

> remote_table %>% distinct(x, y) %>% group_by(y) %>% mutate(n = n()) %>% ungroup() %>% filter(y == "YYY") %>% show_query() 
<SQL>
SELECT DISTINCT
  "x",
  "y",
  COUNT(*) OVER (PARTITION BY "y") AS "n"
FROM "dbplyr_026"
WHERE ("y" = 'YYY')

Note how it extended distinct onto the subsequent query.

And this one is generated (as expected) by dplyr v1.0.10 + dbplyr 2.2.1:

> remote_table %>% distinct(x, y) %>% group_by(y) %>% mutate(n = n()) %>% ungroup() %>% filter(y == "YYY") %>% show_query() 
<SQL>
SELECT *
FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY "y") AS "n"
  FROM (
    SELECT DISTINCT "x", "y"
    FROM "dbplyr_026"
  ) "q01"
) "q02"
WHERE ("y" = 'YYY')

[hadley]

Moved to dbplyr

[mgirlich]

More minimal reprex

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

memdb_frame(x = 1:2) %>% 
  distinct(x) %>% 
  mutate(x = 0) %>% 
  collect()
#> # A tibble: 1 × 1
#>       x
#>   <dbl>
#> 1     0

^{Created on 2023-02-02 with reprex v2.0.2}

[mgirlich]

@vadim-cherepanov Thanks for filing the issue. Should be fixed in the dev version 😄

[vadim-cherepanov]

Thank you for taking care of this. I am just wondering what changes behind the scene caused this inlining of mutate, and are we sure the same changes did not break something else in other cases.

[mgirlich]

The queries generated by dbplyr used to consist of a lot of subqueries. For version 2.3.0 we tried to reduce the number of subqueries. This improves the readability, often quite a lot, but can also result in faster queries (e.g. in the case of multiple joins).
Unfortunately, this caused a couple of issues but with the help of the community we should be relatively quick to identify and fix them.