Repeated window function returns incorrect result

[tidyverse]

Repeated window function returns incorrect result

@hadley commented on Jun 7, 2018, 9:38 PM UTC:

Could you please try to make a simpler example? It's not immediately obvious to me what is wrong with the incorrect output.

@sirallen commented on Jun 7, 2018, 10:50 PM UTC:

@hadley The incorrect output does not return the earliest time_hours (for unique destinations) by carrier. For example, it returns 2013-01-03, 2013-02-01, 2013-04-14, 2013-10-17 for carrier 9E, while the "correct" query returns times on 2013-01-01, which are, in fact, the earliest. (I'm expecting the earliest times as I'm arranging by carrier and time_hour before the second filter in both cases.)

Hope this clears things up (kind of difficult to create a "simpler" example that requires repeated use of window functions)

[sirallen]

Can somebody please acknowledge that this is a problem?

[hadley]

I can not do anything with it until I have time to create a simpler reprex.

[edgararuiz-zz]

@sirallen - Can you try this code in your setup?

incorrect <- tbl(localdb, 'flights') %>%
  group_by(carrier, dest) %>%
  arrange(carrier, dest, time_hour) %>%
  filter(row_number() == 1L) %>%
  group_by(carrier) %>%
  arrange(time_hour) %>%
  filter(row_number() < 5L) %>%
  select(carrier, dest, time_hour) %>%
  collect()

I just removed carrier from the second group by. I've been trying to recreate your issue, but have not been able to. I theorize that the redundant carrier inside the order by window function is making the database re-order the records unnecessarily. Again, just a theory, would like to see what you get.

[hadley]

I've looked at the generated SQL and it seems fine to me, so the most likely explanation is some minor difference in semantics between R and SQL (or between what you expect and what actually happens). If you can generate a simpler example and explain exactly what the problem is, please feel free to open a new issue.