Filtering on mutate() columns in Greenplum

[patrick-nicholson]

Using filter on a column created by mutate (in this case rnk) fails with the error

> dp %>% mutate(rnk = min_rank(desc(salary))) %>% filter(rnk == 1)
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  column "rnk" does not exist
LINE 3: WHERE "rnk" = 1.0) AS master
              ^
)

However, selecting the column rnk works:

> dp %>% mutate(rnk = min_rank(desc(salary))) %>% select(rnk)
   rnk
1    1
2    2
3    3
...

Due to the setup of R and Greenplum I have to use, I can't provide a replicable example. I'm not sure if this is a psql issue or specific to Greenplum. I don't get errors using local dataframes or sqlite.

[hadley]

Could you please me the generated SQL? (Call the object x and then use x$sql

[patrick-nicholson]

SELECT "first_name", "last_name", "emp_id", "hire_date", "salary", "dept",
       "exempt", "interests", rank() OVER (ORDER BY "salary" DESC) AS "rnk"
FROM "emp"
WHERE "rnk" = 1.0

[hadley]

Oh hmm, I didn't think about using window functions when the data isn't grouped. It needs an intermediate subquery.