NULL %foo% lhs is not translated to foo lhs anymore ?

[moodymudskipper]

I had the following code and it worked ok (not sure with which version):

data |>
  mutate(year = extract(NULL %year from% mydate))

Now it is broken, I could fix by changing to:

data |>
  mutate(year = extract(`year from`(mydate)))

The translation is extract(year from("mydate").

I'm not sure if the previous behavior was documented. I don't see the breaking change idocumented in the NEWS. Was it purposefully disabled ? I thought it was neat.

Note: I just found out that I could just use year() (on Oracle), not sure if it's new or not, but tangential to the issue.

[mgirlich]

So you mean that previously extract(NULL %year from% mydate) was translated to extract(year from `mydate`)? Interesting, I don't think this was intended nor was it intended to change, therefore there is no breaking change documented.

I see that it would be nice to somehow get the SQL code extract(year from `mydate`) but I'm not quite convinced of NULL %year from% mydate.

[hadley]

This works, but is a bit clunky:

library(dbplyr)
translate_sql(extract(sql("year") %from% 'mydate'), con = simulate_postgres())
#> <SQL> extract(year from 'mydate')

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

Maybe we could provide built-in %from% and %as% infix helpers for these common cases?

[krlmlr]

I remember using this feature extensively in a project, and I have it on my workshop slides somewhere.

[krlmlr]

Can we just pass through %whatever% unchanged?

[hadley]

Since this is a regression we can just fix it for 2.4.1. I don't love the syntax, but it works, and there's no obviously better alternative.

[hadley]

Most likely root cause: https://github.com/tidyverse/dbplyr/pull/1228/files#diff-bffb8f93561affc007b200e783a30fb6b418d48ffa60175ade2a2fc3818bc532

Reprex:

library(dbplyr)
translate_sql(extract(NULL %year from% mydate), con = simulate_postgres())
#> <SQL> extract(NULL year from `mydate`)

^{Created on 2024-02-20 with reprex v2.0.2.9000}