search

tidyverse / dbplyr

Database (DBI) backend for dplyr
https://dbplyr.tidyverse.org
Other
469 stars 169 forks source link

str_like case sensitivity #1490

Open edward-burn opened 3 months ago

edward-burn commented 3 months ago

Relates to issue #1488

This pr would change behaviour to the following

library(dbplyr)

lf_postgres <- lazy_frame(a = TRUE, b = 1, c = 2, d = "zzzyzzz", 
                 con = simulate_postgres())
lf_redshift <- lazy_frame(a = TRUE, b = 1, c = 2, d = "zzzyzzz", 
                          con = simulate_redshift())
lf_mssql <- lazy_frame(a = TRUE, b = 1, c = 2, d = "zzzyzzz", 
                 con = simulate_mssql()) 
lf_snowflake <- lazy_frame(a = TRUE, b = 1, c = 2, d = "zzzyzzz", 
                       con = simulate_snowflake()) 
lf_spark <- lazy_frame(a = TRUE, b = 1, c = 2, d = "zzzyzzz", 
                           con = simulate_spark_sql()) 

# error for backends without ilike
lf_postgres |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = TRUE))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`d` ILIKE 'Y')
lf_redshift |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = TRUE))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`d` ILIKE 'Y')
lf_mssql |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = TRUE))
#> Error in `stringr::str_like()`:
#> ! Backend does not support case insensitve {.fn str_like}.
#> ℹ Set ignore_case = FALSE for case sensitive match.
#> ℹ Note, using `tolower()` to cast string and pattern to lower case would also achieve a case insenitive match.
#> Backtrace:
#>      ▆
#>   1. ├─base::tryCatch(...)
#>   2. │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>   3. │   ├─base (local) tryCatchOne(...)
#>   4. │   │ └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   5. │   └─base (local) tryCatchList(expr, names[-nh], parentenv, handlers[-nh])
#>   6. │     └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>   7. │       └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   8. ├─base::withCallingHandlers(...)
#>   9. ├─base::saveRDS(...)
#>  10. ├─base::do.call(...)
#>  11. ├─base (local) `<fn>`(...)
#>  12. ├─global `<fn>`(input = base::quote("stony-moth_reprex.R"))
#>  13. │ └─rmarkdown::render(input, quiet = TRUE, envir = globalenv(), encoding = "UTF-8")
#>  14. │   └─knitr::knit(knit_input, knit_output, envir = envir, quiet = quiet)
#>  15. │     └─knitr:::process_file(text, output)
#>  16. │       ├─knitr:::handle_error(...)
#>  17. │       │ └─base::withCallingHandlers(...)
#>  18. │       ├─base::withCallingHandlers(...)
#>  19. │       ├─knitr:::process_group(group)
#>  20. │       └─knitr:::process_group.block(group)
#>  21. │         └─knitr:::call_block(x)
#>  22. │           └─knitr:::block_exec(params)
#>  23. │             └─knitr:::eng_r(options)
#>  24. │               ├─knitr:::in_input_dir(...)
#>  25. │               │ └─knitr:::in_dir(input_dir(), expr)
#>  26. │               └─knitr (local) evaluate(...)
#>  27. │                 └─evaluate::evaluate(...)
#>  28. │                   └─evaluate:::evaluate_call(...)
#>  29. │                     ├─evaluate (local) handle(...)
#>  30. │                     │ └─base::try(f, silent = TRUE)
#>  31. │                     │   └─base::tryCatch(...)
#>  32. │                     │     └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  33. │                     │       └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  34. │                     │         └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>  35. │                     ├─base::withCallingHandlers(...)
#>  36. │                     ├─base::withVisible(value_fun(ev$value, ev$visible))
#>  37. │                     └─knitr (local) value_fun(ev$value, ev$visible)
#>  38. │                       └─knitr (local) fun(x, options = options)
#>  39. │                         ├─base::withVisible(knit_print(x, ...))
#>  40. │                         ├─knitr::knit_print(x, ...)
#>  41. │                         └─knitr:::knit_print.default(x, ...)
#>  42. │                           └─evaluate (local) normal_print(x)
#>  43. │                             ├─base::print(x)
#>  44. │                             └─dbplyr:::print.tbl_lazy(x)
#>  45. │                               ├─dplyr::show_query(x) at dbplyr/R/tbl-lazy.R:49:3
#>  46. │                               └─dbplyr:::show_query.tbl_lazy(x)
#>  47. │                                 └─dbplyr::remote_query(x, cte = cte, sql_options = sql_options) at dbplyr/R/explain.R:5:3
#>  48. │                                   └─dbplyr::db_sql_render(remote_con(x), x, cte = cte, sql_options = sql_options) at dbplyr/R/remote.R:99:3
#>  49. │                                     ├─dbplyr::db_sql_render(con, sql, ..., sql_options = sql_options) at dbplyr/R/db.R:76:5
#>  50. │                                     ├─dbplyr:::`db_sql_render.Microsoft SQL Server`(...) at dbplyr/R/db.R:80:3
#>  51. │                                     ├─base::NextMethod() at dbplyr/R/backend-mssql.R:635:3
#>  52. │                                     └─dbplyr:::db_sql_render.DBIConnection(con, sql, ..., sql_options = sql_options)
#>  53. │                                       ├─dbplyr::sql_render(sql, con = con, ..., sql_options = sql_options) at dbplyr/R/db.R:84:3
#>  54. │                                       └─dbplyr:::sql_render.tbl_lazy(sql, con = con, ..., sql_options = sql_options) at dbplyr/R/sql-build.R:90:3
#>  55. │                                         ├─dbplyr::sql_render(...) at dbplyr/R/sql-build.R:101:3
#>  56. │                                         └─dbplyr:::sql_render.lazy_query(...) at dbplyr/R/sql-build.R:90:3
#>  57. │                                           ├─dbplyr::sql_build(query, con = con, sql_options = sql_options) at dbplyr/R/sql-build.R:118:3
#>  58. │                                           └─dbplyr:::sql_build.lazy_select_query(query, con = con, sql_options = sql_options) at dbplyr/R/sql-build.R:43:3
#>  59. │                                             └─dbplyr::translate_sql_(op$where, con = con, context = list(clause = "WHERE")) at dbplyr/R/lazy-select-query.R:177:3
#>  60. │                                               └─base::lapply(...) at dbplyr/R/translate-sql.R:142:3
#>  61. │                                                 └─dbplyr (local) FUN(X[[i]], ...)
#>  62. │                                                   ├─dbplyr::escape(eval_tidy(x, mask), con = con) at dbplyr/R/translate-sql.R:149:7
#>  63. │                                                   └─rlang::eval_tidy(x, mask) at dbplyr/R/translate-sql.R:149:7
#>  64. └─stringr::str_like(d, "Y", ignore_case = TRUE)
#>  65.   └─rlang::abort(bullets) at dbplyr/R/backend-.R:290:7
lf_snowflake |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = TRUE))
#> Error in `stringr::str_like()`:
#> ! Backend does not support case insensitve {.fn str_like}.
#> ℹ Set ignore_case = FALSE for case sensitive match.
#> ℹ Note, using `tolower()` to cast string and pattern to lower case would also achieve a case insenitive match.
#> Backtrace:
#>      ▆
#>   1. ├─base::tryCatch(...)
#>   2. │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>   3. │   ├─base (local) tryCatchOne(...)
#>   4. │   │ └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   5. │   └─base (local) tryCatchList(expr, names[-nh], parentenv, handlers[-nh])
#>   6. │     └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>   7. │       └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   8. ├─base::withCallingHandlers(...)
#>   9. ├─base::saveRDS(...)
#>  10. ├─base::do.call(...)
#>  11. ├─base (local) `<fn>`(...)
#>  12. ├─global `<fn>`(input = base::quote("stony-moth_reprex.R"))
#>  13. │ └─rmarkdown::render(input, quiet = TRUE, envir = globalenv(), encoding = "UTF-8")
#>  14. │   └─knitr::knit(knit_input, knit_output, envir = envir, quiet = quiet)
#>  15. │     └─knitr:::process_file(text, output)
#>  16. │       ├─knitr:::handle_error(...)
#>  17. │       │ └─base::withCallingHandlers(...)
#>  18. │       ├─base::withCallingHandlers(...)
#>  19. │       ├─knitr:::process_group(group)
#>  20. │       └─knitr:::process_group.block(group)
#>  21. │         └─knitr:::call_block(x)
#>  22. │           └─knitr:::block_exec(params)
#>  23. │             └─knitr:::eng_r(options)
#>  24. │               ├─knitr:::in_input_dir(...)
#>  25. │               │ └─knitr:::in_dir(input_dir(), expr)
#>  26. │               └─knitr (local) evaluate(...)
#>  27. │                 └─evaluate::evaluate(...)
#>  28. │                   └─evaluate:::evaluate_call(...)
#>  29. │                     ├─evaluate (local) handle(...)
#>  30. │                     │ └─base::try(f, silent = TRUE)
#>  31. │                     │   └─base::tryCatch(...)
#>  32. │                     │     └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  33. │                     │       └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  34. │                     │         └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>  35. │                     ├─base::withCallingHandlers(...)
#>  36. │                     ├─base::withVisible(value_fun(ev$value, ev$visible))
#>  37. │                     └─knitr (local) value_fun(ev$value, ev$visible)
#>  38. │                       └─knitr (local) fun(x, options = options)
#>  39. │                         ├─base::withVisible(knit_print(x, ...))
#>  40. │                         ├─knitr::knit_print(x, ...)
#>  41. │                         └─knitr:::knit_print.default(x, ...)
#>  42. │                           └─evaluate (local) normal_print(x)
#>  43. │                             ├─base::print(x)
#>  44. │                             └─dbplyr:::print.tbl_lazy(x)
#>  45. │                               ├─dplyr::show_query(x) at dbplyr/R/tbl-lazy.R:49:3
#>  46. │                               └─dbplyr:::show_query.tbl_lazy(x)
#>  47. │                                 └─dbplyr::remote_query(x, cte = cte, sql_options = sql_options) at dbplyr/R/explain.R:5:3
#>  48. │                                   └─dbplyr::db_sql_render(remote_con(x), x, cte = cte, sql_options = sql_options) at dbplyr/R/remote.R:99:3
#>  49. │                                     ├─dbplyr::db_sql_render(con, sql, ..., sql_options = sql_options) at dbplyr/R/db.R:76:5
#>  50. │                                     └─dbplyr:::db_sql_render.DBIConnection(con, sql, ..., sql_options = sql_options) at dbplyr/R/db.R:80:3
#>  51. │                                       ├─dbplyr::sql_render(sql, con = con, ..., sql_options = sql_options) at dbplyr/R/db.R:84:3
#>  52. │                                       └─dbplyr:::sql_render.tbl_lazy(sql, con = con, ..., sql_options = sql_options) at dbplyr/R/sql-build.R:90:3
#>  53. │                                         ├─dbplyr::sql_render(...) at dbplyr/R/sql-build.R:101:3
#>  54. │                                         └─dbplyr:::sql_render.lazy_query(...) at dbplyr/R/sql-build.R:90:3
#>  55. │                                           ├─dbplyr::sql_build(query, con = con, sql_options = sql_options) at dbplyr/R/sql-build.R:118:3
#>  56. │                                           └─dbplyr:::sql_build.lazy_select_query(query, con = con, sql_options = sql_options) at dbplyr/R/sql-build.R:43:3
#>  57. │                                             └─dbplyr::translate_sql_(op$where, con = con, context = list(clause = "WHERE")) at dbplyr/R/lazy-select-query.R:177:3
#>  58. │                                               └─base::lapply(...) at dbplyr/R/translate-sql.R:142:3
#>  59. │                                                 └─dbplyr (local) FUN(X[[i]], ...)
#>  60. │                                                   ├─dbplyr::escape(eval_tidy(x, mask), con = con) at dbplyr/R/translate-sql.R:149:7
#>  61. │                                                   └─rlang::eval_tidy(x, mask) at dbplyr/R/translate-sql.R:149:7
#>  62. └─stringr::str_like(d, "Y", ignore_case = TRUE)
#>  63.   └─rlang::abort(bullets) at dbplyr/R/backend-.R:290:7
lf_spark |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = TRUE))
#> Error in `stringr::str_like()`:
#> ! Backend does not support case insensitve {.fn str_like}.
#> ℹ Set ignore_case = FALSE for case sensitive match.
#> ℹ Note, using `tolower()` to cast string and pattern to lower case would also achieve a case insenitive match.
#> Backtrace:
#>      ▆
#>   1. ├─base::tryCatch(...)
#>   2. │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>   3. │   ├─base (local) tryCatchOne(...)
#>   4. │   │ └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   5. │   └─base (local) tryCatchList(expr, names[-nh], parentenv, handlers[-nh])
#>   6. │     └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>   7. │       └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   8. ├─base::withCallingHandlers(...)
#>   9. ├─base::saveRDS(...)
#>  10. ├─base::do.call(...)
#>  11. ├─base (local) `<fn>`(...)
#>  12. ├─global `<fn>`(input = base::quote("stony-moth_reprex.R"))
#>  13. │ └─rmarkdown::render(input, quiet = TRUE, envir = globalenv(), encoding = "UTF-8")
#>  14. │   └─knitr::knit(knit_input, knit_output, envir = envir, quiet = quiet)
#>  15. │     └─knitr:::process_file(text, output)
#>  16. │       ├─knitr:::handle_error(...)
#>  17. │       │ └─base::withCallingHandlers(...)
#>  18. │       ├─base::withCallingHandlers(...)
#>  19. │       ├─knitr:::process_group(group)
#>  20. │       └─knitr:::process_group.block(group)
#>  21. │         └─knitr:::call_block(x)
#>  22. │           └─knitr:::block_exec(params)
#>  23. │             └─knitr:::eng_r(options)
#>  24. │               ├─knitr:::in_input_dir(...)
#>  25. │               │ └─knitr:::in_dir(input_dir(), expr)
#>  26. │               └─knitr (local) evaluate(...)
#>  27. │                 └─evaluate::evaluate(...)
#>  28. │                   └─evaluate:::evaluate_call(...)
#>  29. │                     ├─evaluate (local) handle(...)
#>  30. │                     │ └─base::try(f, silent = TRUE)
#>  31. │                     │   └─base::tryCatch(...)
#>  32. │                     │     └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  33. │                     │       └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  34. │                     │         └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>  35. │                     ├─base::withCallingHandlers(...)
#>  36. │                     ├─base::withVisible(value_fun(ev$value, ev$visible))
#>  37. │                     └─knitr (local) value_fun(ev$value, ev$visible)
#>  38. │                       └─knitr (local) fun(x, options = options)
#>  39. │                         ├─base::withVisible(knit_print(x, ...))
#>  40. │                         ├─knitr::knit_print(x, ...)
#>  41. │                         └─knitr:::knit_print.default(x, ...)
#>  42. │                           └─evaluate (local) normal_print(x)
#>  43. │                             ├─base::print(x)
#>  44. │                             └─dbplyr:::print.tbl_lazy(x)
#>  45. │                               ├─dplyr::show_query(x) at dbplyr/R/tbl-lazy.R:49:3
#>  46. │                               └─dbplyr:::show_query.tbl_lazy(x)
#>  47. │                                 └─dbplyr::remote_query(x, cte = cte, sql_options = sql_options) at dbplyr/R/explain.R:5:3
#>  48. │                                   └─dbplyr::db_sql_render(remote_con(x), x, cte = cte, sql_options = sql_options) at dbplyr/R/remote.R:99:3
#>  49. │                                     ├─dbplyr::db_sql_render(con, sql, ..., sql_options = sql_options) at dbplyr/R/db.R:76:5
#>  50. │                                     └─dbplyr:::db_sql_render.DBIConnection(con, sql, ..., sql_options = sql_options) at dbplyr/R/db.R:80:3
#>  51. │                                       ├─dbplyr::sql_render(sql, con = con, ..., sql_options = sql_options) at dbplyr/R/db.R:84:3
#>  52. │                                       └─dbplyr:::sql_render.tbl_lazy(sql, con = con, ..., sql_options = sql_options) at dbplyr/R/sql-build.R:90:3
#>  53. │                                         ├─dbplyr::sql_render(...) at dbplyr/R/sql-build.R:101:3
#>  54. │                                         └─dbplyr:::sql_render.lazy_query(...) at dbplyr/R/sql-build.R:90:3
#>  55. │                                           ├─dbplyr::sql_build(query, con = con, sql_options = sql_options) at dbplyr/R/sql-build.R:118:3
#>  56. │                                           └─dbplyr:::sql_build.lazy_select_query(query, con = con, sql_options = sql_options) at dbplyr/R/sql-build.R:43:3
#>  57. │                                             └─dbplyr::translate_sql_(op$where, con = con, context = list(clause = "WHERE")) at dbplyr/R/lazy-select-query.R:177:3
#>  58. │                                               └─base::lapply(...) at dbplyr/R/translate-sql.R:142:3
#>  59. │                                                 └─dbplyr (local) FUN(X[[i]], ...)
#>  60. │                                                   ├─dbplyr::escape(eval_tidy(x, mask), con = con) at dbplyr/R/translate-sql.R:149:7
#>  61. │                                                   └─rlang::eval_tidy(x, mask) at dbplyr/R/translate-sql.R:149:7
#>  62. └─stringr::str_like(d, "Y", ignore_case = TRUE)
#>  63.   └─rlang::abort(bullets) at dbplyr/R/backend-.R:290:7

# like used for all
lf_postgres |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = FALSE))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`d` LIKE 'Y')
lf_redshift |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = FALSE))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`d` LIKE 'Y')
lf_mssql |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = FALSE))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`d` LIKE 'Y')
lf_snowflake |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = FALSE))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`d` LIKE 'Y')
lf_spark |> 
  dplyr::filter(stringr::str_like(d, "Y", ignore_case = FALSE))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`d` LIKE 'Y')

Created on 2024-04-04 with reprex v2.0.2

edward-burn commented 2 months ago

@hadley @mgirlich just fyi I noticed this pr had a merge conflict so I have resolved that