When using if_any with dbplyr, logic inconsistent with dplyr is noted when the result is used as an operand in a larger expression. This behavior can be addressed by surrounding the produced SQL with parentheses.
Consider:
dbplyr::tbl_lazy(starwars, dbplyr::simulate_odbc()) %>%
filter(
if_any(matches('color'), ~. == 'brown') & species == 'Human'
)
#> <SQL>
#> SELECT *
#> FROM `df`
#> WHERE (`hair_color` = 'brown' OR `skin_color` = 'brown' OR `eye_color` = 'brown' AND `species` = 'Human')Because if_any renders inline to a chain of X OR Y OR Z ..., and because AND has higher precedence compared to OR (in all dialects I could identify), the backend evaluation of WHERE is equivalent to
#> WHERE (`hair_color` = 'brown' OR `skin_color` = 'brown' OR (`eye_color` = 'brown' AND `species` = 'Human'))
when in fact we need
#> WHERE ((`hair_color` = 'brown' OR `skin_color` = 'brown' OR `eye_color` = 'brown') AND `species` = 'Human')
Surrounding the SQL representation of if_any in parentheses in all contexts would fix this issue at the expense of an occasionally unneeded pair of braces. I believe this would be much easier than intelligently adding them when needed.
dbplyr version 2.3.0
When using
if_anywith dbplyr, logic inconsistent with dplyr is noted when the result is used as an operand in a larger expression. This behavior can be addressed by surrounding the produced SQL with parentheses.Consider:
Because
if_anyrenders inline to a chain ofX OR Y OR Z ..., and becauseANDhas higher precedence compared toOR(in all dialects I could identify), the backend evaluation ofWHEREis equivalent towhen in fact we need
Surrounding the SQL representation of
if_anyin parentheses in all contexts would fix this issue at the expense of an occasionally unneeded pair of braces. I believe this would be much easier than intelligently adding them when needed.dbplyr version 2.3.0