tests/testthat/_snaps/backend-mssql.md

In dbplyr: A 'dplyr' Back End for Databases

custom aggregators translated correctly

Code
  test_translate_sql(quantile(x, 0.5, na.rm = TRUE), window = FALSE)
Condition
  Error in `quantile()`:
  ! Translation of `quantile()` in `summarise()` is not supported for SQL Server.
  i Use a combination of `distinct()` and `mutate()` for the same result:
    `mutate(<col> = quantile(x, 0.5, na.rm = TRUE)) %>% distinct(<col>)`
Code
  test_translate_sql(median(x, na.rm = TRUE), window = FALSE)
Condition
  Error in `median()`:
  ! Translation of `median()` in `summarise()` is not supported for SQL Server.
  i Use a combination of `distinct()` and `mutate()` for the same result:
    `mutate(<col> = median(x, na.rm = TRUE)) %>% distinct(<col>)`

custom lubridate functions translated correctly

Code
  test_translate_sql(month(x, label = TRUE, abbr = TRUE))
Condition
  Error in `month()`:
  ! `abbr = TRUE` isn't supported in SQL Server translation.
  i It must be FALSE instead.

convert between bit and boolean as needed

Code
  mf %>% filter(is.na(x))
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE ((`x` IS NULL))

Code
  mf %>% filter(!is.na(x))
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE (NOT((`x` IS NULL)))

Code
  mf %>% filter(x == 1L || x == 2L)
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE (`x` = 1 OR `x` = 2)

Code
  mf %>% mutate(z = ifelse(x == 1L, 1L, 2L))
Output
  <SQL>
  SELECT `df`.*, IIF(`x` = 1, 1, 2) AS `z`
  FROM `df`

Code
  mf %>% mutate(z = case_when(x == 1L ~ 1L))
Output
  <SQL>
  SELECT `df`.*, CASE WHEN (`x` = 1) THEN 1 END AS `z`
  FROM `df`

Code
  mf %>% mutate(z = !is.na(x))
Output
  <SQL>
  SELECT `df`.*, ~CAST(IIF((`x` IS NULL), 1, 0) AS BIT) AS `z`
  FROM `df`

Code
  mf %>% mutate(x = x == 1L)
Output
  <SQL>
  SELECT CAST(IIF(`x` = 1, 1, 0) AS BIT) AS `x`
  FROM `df`

Code
  mf %>% mutate(x = x == 1L || x == 2L)
Output
  <SQL>
  SELECT CAST(IIF(`x` = 1 OR `x` = 2, 1, 0) AS BIT) AS `x`
  FROM `df`

Code
  mf %>% mutate(x = x == 1L || x == 2L || x == 3L)
Output
  <SQL>
  SELECT CAST(IIF(`x` = 1 OR `x` = 2 OR `x` = 3, 1, 0) AS BIT) AS `x`
  FROM `df`

Code
  mf %>% mutate(x = !(x == 1L || x == 2L || x == 3L))
Output
  <SQL>
  SELECT ~CAST(IIF((`x` = 1 OR `x` = 2 OR `x` = 3), 1, 0) AS BIT) AS `x`
  FROM `df`

handles ORDER BY in subqueries

Code
  sql_query_select(simulate_mssql(), ident("x"), ident("y"), order_by = "z",
  subquery = TRUE)
Condition
  Warning:
  ORDER BY is ignored in subqueries without LIMIT
  i Do you need to move arrange() later in the pipeline or use window_order() instead?
Output
  <SQL> SELECT `x`
  FROM `y`

custom limit translation

Code
  sql_query_select(simulate_mssql(), ident("x"), ident("y"), order_by = ident("z"),
  limit = 10)
Output
  <SQL> SELECT TOP 10 `x`
  FROM `y`
  ORDER BY `z`

custom escapes translated correctly

Code
  mf %>% filter(x == a)
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE (`x` = 0x616263)

Code
  mf %>% filter(x %in% L)
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE (`x` IN (0x616263, 0x0102))

Code
  qry
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE (`x` IN (0x616263, 0x0102))

logical escaping to 0/1 for both filter() and mutate()

Code
  mf %>% filter(x == TRUE)
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE (`x` = 1)

Code
  mf %>% mutate(x = TRUE)
Output
  <SQL>
  SELECT 1 AS `x`
  FROM `df`

generates custom sql

Code
  sql_table_analyze(con, in_schema("schema", "tbl"))
Output
  <SQL> UPDATE STATISTICS `schema`.`tbl`

Code
  sql_query_save(con, sql("SELECT * FROM foo"), in_schema("schema", "tbl"))
Output
  <SQL> SELECT * INTO `schema`.`tbl` FROM (
    SELECT * FROM foo
  ) AS temp

Code
  sql_query_save(con, sql("SELECT * FROM foo"), in_schema("schema", "tbl"),
  temporary = FALSE)
Output
  <SQL> SELECT * INTO `schema`.`tbl` FROM (
    SELECT * FROM foo
  ) AS temp

Code
  lf %>% slice_sample(n = 1)
Output
  <SQL>
  SELECT `x`
  FROM (
    SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY RAND()) AS `col01`
    FROM `df`
  ) AS `q01`
  WHERE (`col01` <= 1)

Code
  copy_inline(con, tibble(x = 1:2, y = letters[1:2])) %>% remote_query()
Output
  <SQL> SELECT
    TRY_CAST(TRY_CAST(`x` AS NUMERIC) AS INT) AS `x`,
    TRY_CAST(`y` AS VARCHAR(MAX)) AS `y`
  FROM (  VALUES (1, 'a'), (2, 'b')) AS drvd(`x`, `y`)

Code
  copy_inline(con, trees) %>% remote_query()
Output
  <SQL> SELECT
    TRY_CAST(`Girth` AS FLOAT) AS `Girth`,
    TRY_CAST(`Height` AS FLOAT) AS `Height`,
    TRY_CAST(`Volume` AS FLOAT) AS `Volume`
  FROM (
    VALUES
      (8.3, 70.0, 10.3),
      (8.6, 65.0, 10.3),
      (8.8, 63.0, 10.2),
      (10.5, 72.0, 16.4),
      (10.7, 81.0, 18.8),
      (10.8, 83.0, 19.7),
      (11.0, 66.0, 15.6),
      (11.0, 75.0, 18.2),
      (11.1, 80.0, 22.6),
      (11.2, 75.0, 19.9),
      (11.3, 79.0, 24.2),
      (11.4, 76.0, 21.0),
      (11.4, 76.0, 21.4),
      (11.7, 69.0, 21.3),
      (12.0, 75.0, 19.1),
      (12.9, 74.0, 22.2),
      (12.9, 85.0, 33.8),
      (13.3, 86.0, 27.4),
      (13.7, 71.0, 25.7),
      (13.8, 64.0, 24.9),
      (14.0, 78.0, 34.5),
      (14.2, 80.0, 31.7),
      (14.5, 74.0, 36.3),
      (16.0, 72.0, 38.3),
      (16.3, 77.0, 42.6),
      (17.3, 81.0, 55.4),
      (17.5, 82.0, 55.7),
      (17.9, 80.0, 58.3),
      (18.0, 80.0, 51.5),
      (18.0, 80.0, 51.0),
      (20.6, 87.0, 77.0)
  ) AS drvd(`Girth`, `Height`, `Volume`)

sql_query_insert() is correct

Code
  sql_query_insert(con = con, table = ident("df_x"), from = sql_render(df_y, con,
    lvl = 1), insert_cols = colnames(df_y), by = c("a", "b"), conflict = "ignore",
  returning_cols = c("a", b2 = "b"))
Output
  <SQL> INSERT INTO `df_x` (`a`, `b`, `c`, `d`)
  OUTPUT `INSERTED`.`a`, `INSERTED`.`b` AS `b2`
  SELECT *
  FROM (
    SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
    FROM `df_y`
  ) AS `...y`
  WHERE NOT EXISTS (
    SELECT 1 FROM `df_x`
    WHERE (`df_x`.`a` = `...y`.`a`) AND (`df_x`.`b` = `...y`.`b`)
  )

sql_query_append() is correct

Code
  sql_query_append(con = con, table = ident("df_x"), from = sql_render(df_y, con,
    lvl = 1), insert_cols = colnames(df_y), returning_cols = c("a", b2 = "b"))
Output
  <SQL> INSERT INTO `df_x` (`a`, `b`, `c`, `d`)
  OUTPUT `INSERTED`.`a`, `INSERTED`.`b` AS `b2`
  SELECT *
  FROM (
    SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
    FROM `df_y`
  ) AS `...y`

sql_query_update_from() is correct

Code
  sql_query_update_from(con = con, table = ident("df_x"), from = sql_render(df_y,
    con, lvl = 1), by = c("a", "b"), update_values = sql(c = "COALESCE(`df_x`.`c`, `...y`.`c`)",
    d = "`...y`.`d`"), returning_cols = c("a", b2 = "b"))
Output
  <SQL> UPDATE `df_x`
  SET `c` = COALESCE(`df_x`.`c`, `...y`.`c`), `d` = `...y`.`d`
  OUTPUT `INSERTED`.`a`, `INSERTED`.`b` AS `b2`
  FROM `df_x`
  INNER JOIN (
    SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
    FROM `df_y`
  ) AS `...y`
    ON `...y`.`a` = `df_x`.`a` AND `...y`.`b` = `df_x`.`b`

sql_query_delete() is correct

Code
  sql_query_delete(con = simulate_mssql(), table = ident("df_x"), from = sql_render(
    df_y, simulate_mssql(), lvl = 2), by = c("a", "b"), returning_cols = c("a",
    b2 = "b"))
Output
  <SQL> DELETE FROM `df_x`
  OUTPUT `DELETED`.`a`, `DELETED`.`b` AS `b2`
  WHERE EXISTS (
    SELECT 1 FROM (
      SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
      FROM `df_y`
  ) AS `...y`
    WHERE (`...y`.`a` = `df_x`.`a`) AND (`...y`.`b` = `df_x`.`b`)
  )

sql_query_upsert() is correct

Code
  sql_query_upsert(con = con, table = ident("df_x"), from = sql_render(df_y, con,
    lvl = 1), by = c("a", "b"), update_cols = c("c", "d"), returning_cols = c("a",
    b2 = "b"))
Output
  <SQL> MERGE INTO `df_x`
  USING (
    SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
    FROM `df_y`
  ) AS `...y`
    ON `...y`.`a` = `df_x`.`a` AND `...y`.`b` = `df_x`.`b`
  WHEN MATCHED THEN
    UPDATE SET `c` = `...y`.`c`, `d` = `...y`.`d`
  WHEN NOT MATCHED THEN
    INSERT (`a`, `b`, `c`, `d`)
    VALUES (`...y`.`a`, `...y`.`b`, `...y`.`c`, `...y`.`d`)
  OUTPUT `INSERTED`.`a`, `INSERTED`.`b` AS `b2`
  ;

row_number() with and without group_by() and arrange(): unordered defaults to Ordering by NULL (per empty_order)

Code
  mf %>% mutate(rown = row_number())
Output
  <SQL>
  SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS `rown`
  FROM `df`

Code
  mf %>% group_by(y) %>% mutate(rown = row_number())
Output
  <SQL>
  SELECT
    `df`.*,
    ROW_NUMBER() OVER (PARTITION BY `y` ORDER BY (SELECT NULL)) AS `rown`
  FROM `df`

Code
  mf %>% arrange(y) %>% mutate(rown = row_number())
Output
  <SQL>
  SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY `y`) AS `rown`
  FROM `df`
  ORDER BY `y`

dbplyr documentation built on Oct. 26, 2023, 9:06 a.m.