### Install dbplyr R Package

Source: https://github.com/tidyverse/dbplyr/blob/main/README.md

This snippet provides instructions for installing the dbplyr R package. Users can install the entire tidyverse, just dbplyr, or the development version from GitHub using `pak`.

```R
# The easiest way to get dbplyr is to install the whole tidyverse:
install.packages("tidyverse")

# Alternatively, install just dbplyr:
install.packages("dbplyr")

# Or the development version from GitHub:
# install.packages("pak")
pak::pak("tidyverse/dbplyr")
```

--------------------------------

### Explain SQL Query Plan in dbplyr (Default Text Format)

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Demonstrates how to use the `explain()` function in `dbplyr` to view the SQL query and execution plan generated for a `dplyr` pipeline. This example shows the default, human-readable text output format of the query plan.

```R
db %>% mutate(y = x + 1) %>% explain()
```

```SQL
SELECT "test".*, "x" + 1.0 AS "y"
FROM "test"
```

```TEXT
<PLAN>
                                                 QUERY PLAN
1 Seq Scan on test  (cost=0.00..1.04 rows=3 width=36)
```

--------------------------------

### Andromeda Package: Examples Check Failure

Source: https://github.com/tidyverse/dbplyr/blob/main/revdep/problems.md

This snippet shows the error log when running examples for the Andromeda R package. The `groupApply` function example failed, indicating an issue with SQL translation or `dbplyr` integration, leading to an `rlang::abort`.

```R
Running examples in ‘Andromeda-Ex.R’ failed
The error most likely occurred in:

> ### Name: groupApply
> ### Title: Apply a function to groups of data in an Andromeda table
> ### Aliases: groupApply
> 
> ### ** Examples
> 
> andr <- andromeda(cars = cars)
...
 12. │           │   └─rlang::is_character(x)
 13. │           └─dbplyr::translate_sql_(op$order_by, con = con)
 14. │             └─base::lapply(...)
 15. │               └─dbplyr (local) FUN(X[[i]], ...)
 16. │                 ├─dbplyr::escape(eval_tidy(x, mask), con = con)
 17. │                 └─rlang::eval_tidy(x, mask)
 18. └─rlang::sym
 19.   └─cli::cli_abort(...)
 20.     └─rlang::abort(...)
Execution halted
```

--------------------------------

### Integrate Window Rank Function into sql_translator

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-helpers.md

Confirms that `win_rank()` can be successfully added to `sql_translator` and its presence is reflected in the `sql_variant`'s supported functions.

```R
sql_variant(sql_translator(test = win_rank("test")))
```

--------------------------------

### Generate Custom SQL for Table Analysis

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-teradata.md

Demonstrates how to use `sql_table_analyze` with `in_schema` to generate SQL for analyzing a table within a specific schema, useful for database maintenance tasks.

```R
sql_table_analyze(con, in_schema("schema", "tbl"))
```

```SQL
COLLECT STATISTICS `schema`.`tbl`
```

--------------------------------

### Verify sql_variant Print Method Output

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-helpers.md

Shows the console output of the `print` method for an `sql_variant` object, indicating the types of SQL functions (scalar, aggregate, window) it supports.

```R
sql_variant(sim_trans, sim_trans, sim_trans)
```

--------------------------------

### Basic SQL Function Call

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/sql.md

Demonstrates a basic call to the `sql()` function in dbplyr. When called without arguments, it initializes an empty SQL object, serving as a starting point for building SQL expressions.

```R
sql()
```

--------------------------------

### Calculate Weighted Mean in SQL using dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-teradata.md

Demonstrates how dbplyr translates the `weighted.mean()` function into a SQL query that calculates the sum of products divided by the sum of weights, effectively implementing a weighted average.

```R
mf %>% summarise(wt_mean = weighted.mean(x, y))
```

```SQL
SELECT SUM((`x` * `y`)) / SUM(`y`) OVER () AS `wt_mean`
FROM `df`
```

--------------------------------

### dbplyr select_query() SQL Output

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/query-select.md

Demonstrates the SQL query generated by the `select_query()` print method in `dbplyr`, illustrating how R code translates into a structured SQL SELECT statement with filtering, ordering, and limiting clauses.

```R
mf
```

```SQL
<SQL SELECT>
From:
  <table_path> `df`
Select:   `df`.*
Where:    `x` > 1
Order by: `x`
Limit:    10
```

--------------------------------

### R substr() error: Missing start argument

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-string.md

Demonstrates an error when calling `substr()` in R without providing the `start` argument. The function expects `start` to be a whole number, but it is absent in this call, leading to an error.

```R
substr("test")
```

--------------------------------

### Generate SQL for basic count with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-count.md

Demonstrates how `dbplyr`'s `count()` function translates a simple R `count` operation on a grouped column `g` into a SQL `SELECT` statement with `COUNT(*)` and `GROUP BY`.

```R
db %>% count(g)
```

```SQL
SELECT `g`, COUNT(*) AS `n`
FROM `df`
GROUP BY `g`
```

--------------------------------

### Generating SQL for inline table copy with data

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Demonstrates how `dbplyr` generates SQL for copying a `tibble` with data inline, using a `VALUES` clause to insert the data directly into the query.

```R
copy_inline(con, tibble(x = 1:2, y = letters[1:2])) %>% remote_query()
```

```SQL
<SQL> SELECT CAST(`x` AS INTEGER) AS `x`, CAST(`y` AS TEXT) AS `y`
FROM (  VALUES (1, 'a'), (2, 'b')) AS drvd(`x`, `y`)
```

--------------------------------

### Handle Unsupported SQL Functions in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-helpers.md

Demonstrates `dbplyr`'s error handling for SQL functions not supported by the current SQL variant, providing an informative error message.

```R
sql_not_supported("cor")()
```

--------------------------------

### Upsert Rows into Database Table with Returning Clause in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Illustrates an attempt to use `rows_upsert()` in `dbplyr` to insert or update rows in a database table, including `returning = everything()` to get back the modified rows. This example highlights an error condition where the table modification fails, along with the generated SQL for the upsert operation.

```R
rows_upsert(x, y, by = c("a", "b"), in_place = TRUE, returning = everything(),
method = "on_conflict")
```

```SQL
INSERT INTO "df_x" ("a", "b", "c", "d") SELECT "a", "b", "c", "d" FROM ( SELECT "a", "b", "c" + 1.0 AS "c", "d" FROM "df_y" ) AS "...y" WHERE true ON CONFLICT ("a", "b") DO UPDATE SET "c" = "excluded"."c", "d" = "excluded"."d" RETURNING "df_x"."a", "df_x"."b", "df_x"."c", "df_x"."d"
```

--------------------------------

### Apply SQL Window Functions LEAD and LAG with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-teradata.md

Shows how dbplyr translates R's `lead()` and `lag()` functions into SQL's `LEAD` and `LAG` window functions. It demonstrates the automatic inclusion of `PARTITION BY` and `ORDER BY` clauses when `group_by()` and `order_by` arguments are used.

```R
mf %>% group_by(y) %>% mutate(val2 = lead(x, order_by = x)) %>% sql_render()
```

```SQL
SELECT `df`.*, LEAD(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `x`) AS `val2`
FROM `df`
```

```R
mf %>% group_by(y) %>% mutate(val2 = lag(x, order_by = x)) %>% sql_render()
```

```SQL
SELECT `df`.*, LAG(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `x`) AS `val2`
FROM `df`
```

--------------------------------

### Handle unsupported .drop argument in dbplyr add_count

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-count.md

Illustrates an error case where the `.drop` argument in `add_count()` is not supported on database backends, providing the specific error message generated by `dbplyr`.

```R
lazy_frame(g = 1) %>% add_count(.drop = TRUE)
```

```Error Output
Error in `add_count()`:
! Argument `.drop` isn't supported on database backends.
```

--------------------------------

### Explain SQL Query Plan in dbplyr (JSON Format)

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Illustrates using the `explain()` function with `format = "json"` to retrieve the SQL query and execution plan in a machine-readable JSON format. This is particularly useful for programmatic parsing and analysis of query execution details.

```R
db %>% mutate(y = x + 1) %>% explain(format = "json")
```

```SQL
SELECT "test".*, "x" + 1.0 AS "y"
FROM "test"
```

```JSON
[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Async Capable": false,
      "Relation Name": "test",
      "Alias": "test",
      "Startup Cost": 0.00,
      "Total Cost": 1.04,
      "Plan Rows": 3,
      "Plan Width": 36
    }
  }
]
```

--------------------------------

### Generate SQL for sorted count with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-count.md

Illustrates how `dbplyr`'s `count()` function translates the `sort = TRUE` argument into a SQL `ORDER BY` clause, sorting the results by the count in descending order.

```R
db %>% count(g, sort = TRUE)
```

```SQL
SELECT `g`, COUNT(*) AS `n`
FROM `df`
GROUP BY `g`
ORDER BY `n` DESC
```

--------------------------------

### Prevent Duplicate Function Definitions in sql_translator

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-helpers.md

Illustrates the error thrown by `sql_translator` when attempting to define multiple functions with the same name, ensuring unique mappings.

```R
sql_translator(round = function(x) x, round = function(y) y)
```

--------------------------------

### Insert Rows into Database Table with Returning Clause in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Shows an attempt to use `rows_insert()` in `dbplyr` to insert new rows into a database table, specifying `returning = everything()` to retrieve all columns of the inserted rows. This example demonstrates an error scenario where the table cannot be modified, along with the generated SQL query for the insert operation.

```R
rows_insert(x, y, by = c("a", "b"), in_place = TRUE, conflict = "ignore",
returning = everything(), method = "on_conflict")
```

```SQL
INSERT INTO "df_x" ("a", "b", "c", "d") SELECT * FROM ( SELECT "a", "b", "c" + 1.0 AS "c", "d" FROM "df_y" ) AS "...y" ON CONFLICT ("a", "b") DO NOTHING RETURNING "df_x"."a", "df_x"."b", "df_x"."c", "df_x"."d"
```

--------------------------------

### Translate zero-row R data frames to SQL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-copy-to.md

Demonstrates how `copy_inline` handles empty R data frames, generating SQL that defines the schema but returns no rows, using `WHERE (0 = 1)` for different column configurations.

```R
copy_inline(con, tibble(dbl = numeric(), chr = character())) %>% remote_query()
```

```SQL
<SQL> SELECT CAST(NULL AS REAL) AS `dbl`, CAST(NULL AS TEXT) AS `chr`
WHERE (0 = 1)
```

```R
copy_inline(con, tibble(dbl = numeric())) %>% remote_query()
```

```SQL
<SQL> SELECT CAST(NULL AS REAL) AS `dbl`
WHERE (0 = 1)
```

--------------------------------

### Translate R's head() to SQL's TOP Clause

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-teradata.md

Illustrates how dbplyr translates the `head()` function in R to the `TOP` clause in SQL, effectively limiting the number of rows returned from a query.

```R
mf %>% head() %>% sql_render()
```

```SQL
SELECT TOP 6 `df`.*
FROM `df`
```

--------------------------------

### Translate runif with n() Limitation in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-helpers.md

Illustrates the translation of the `runif` function in `dbplyr`, highlighting the current limitation that only `n = n()` is supported for random number generation.

```R
test_translate_sql(runif(2))
```

--------------------------------

### Translating `difftime` with `tz` argument

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Shows that the `tz` argument is not supported when translating `difftime()` for database backends.
Error: Argument `tz` isn't supported on database backends.

```R
test_translate_sql(difftime(start_date, end_date, tz = "UTC", units = "days"))
```

--------------------------------

### dbplyr select() SQL Generation and Error Handling

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/query-select.md

Illustrates how `dbplyr`'s `select()` function generates concise SQL queries, avoiding unnecessary aliasing. It also demonstrates the error condition encountered when `select()` is invoked without specifying any columns, highlighting the importance of column selection.

```R
lf_render
```

```SQL
<SQL> SELECT `x`
FROM `df`
```

```R
lazy_frame(x = 1, y = 1) %>% select()
```

```R
Error in `sql_clause_select()`:
! Query contains no columns
```

--------------------------------

### Generating SQL for `sql_query_insert` with `conflict = "ignore"`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Shows how `dbplyr` generates an `INSERT INTO ... ON CONFLICT DO NOTHING RETURNING` SQL statement using `sql_query_insert()` with `conflict = "ignore"` for handling duplicate keys.

```R
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"))
```

```SQL
<SQL> INSERT INTO `df_x` (`a`, `b`, `c`, `d`)
SELECT *
FROM (
  SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
  FROM `df_y`
) AS `...y`
ON CONFLICT (`a`, `b`)
DO NOTHING
RETURNING `df_x`.`a`, `df_x`.`b` AS `b2`
```

--------------------------------

### Validate Arguments for dbplyr SQL Prefix Functions

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-helpers.md

Demonstrates `dbplyr`'s strict argument validation for SQL prefix functions, showing errors for incorrect argument counts and mismatched named arguments.

```R
sin_db(sin(1, 2))
```

```R
sin_db(sin(a = 1))
```

--------------------------------

### Generate Empty Table Schema with copy_inline and Explicit Types

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-hana.md

Shows how to use `copy_inline` with `slice(0)` and an explicit `types` argument to define the SQL data types for the columns. This ensures precise type mapping when creating an empty table schema.

```R
copy_inline(con, y %>% slice(0), types = types) %>% remote_query()
```

```SQL
SELECT CAST(NULL AS bigint) AS `id`, CAST(NULL AS integer[]) AS `arr`
FROM `DUMMY`
WHERE (0 = 1)
```

--------------------------------

### Generating SQL for empty inline table copy

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Shows how `dbplyr` generates SQL for copying an empty `tibble` inline, resulting in a `SELECT NULL` query with a `WHERE (0 = 1)` clause to ensure no rows are returned.

```R
copy_inline(con, tibble(x = integer(), y = character())) %>% remote_query()
```

```SQL
<SQL> SELECT CAST(NULL AS INTEGER) AS `x`, CAST(NULL AS TEXT) AS `y`
WHERE (0 = 1)
```

--------------------------------

### Print SQL Table Object in R with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/tbl-sql.md

Demonstrates printing a `dbplyr` SQL table object (`mf2`) to the console. The output shows the source database (sqlite), table dimensions, and initial rows, illustrating how `dbplyr` objects are represented when printed.

```R
mf2
```

--------------------------------

### Test for old dbplyr interface warning

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/db-sql.md

Checks if `dbplyr_analyze` throws an error and a warning when using an old `dbplyr` interface, indicating a need for package update. The warning advises installing a newer version or contacting the maintainer.

```R
expect_error(dbplyr_analyze(con), "db_method")
```

--------------------------------

### Generate SQL for weighted count with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-count.md

Shows how `dbplyr`'s `count()` function handles the `wt` argument, translating a weighted count in R into a SQL `SUM()` aggregation on column `x` within each group `g`.

```R
db %>% count(g, wt = x)
```

```SQL
SELECT `g`, SUM(`x`) AS `n`
FROM `df`
GROUP BY `g`
```

--------------------------------

### Generating SQL for `sql_query_upsert` with `method = "on_conflict"`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Demonstrates how `dbplyr` generates an `INSERT INTO ... ON CONFLICT DO UPDATE SET ... RETURNING` SQL statement using `sql_query_upsert()` with `method = "on_conflict"` for performing an upsert operation.

```R
sql_query_upsert(con = con, table = ident("df_x"), from = sql_render(df_y, con,
  lvl = 1), by = c("c", "d"), update_cols = c("a", "b"), returning_cols = c("a",
  b2 = "b"), method = "on_conflict")
```

```SQL
<SQL> INSERT INTO `df_x` (`c`, `d`, `a`, `b`)
SELECT `c`, `d`, `a`, `b`
FROM (
  SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
  FROM `df_y`
) AS `...y`
WHERE true
ON CONFLICT  (`c`, `d`)
DO UPDATE
SET `a` = `excluded`.`a`, `b` = `excluded`.`b`
RETURNING `df_x`.`a`, `df_x`.`b` AS `b2`
```

--------------------------------

### Chaining `union`, `left_join` and inspecting SQL with CTEs in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-set-ops.md

This example extends the union operations by adding a `left_join` and then uses `show_query` with `sql_options(cte = TRUE)` to display the generated SQL query. It demonstrates how dbplyr translates complex R operations into SQL with Common Table Expressions (CTEs) for improved readability and performance.

```R
lf1 %>% union_all(lf2) %>% union(lf3) %>% left_join(lf1, by = "x") %>% show_query(sql_options = sql_options(cte = TRUE))
```

```SQL
WITH `q01` AS (
  SELECT `lf1`.*, NULL AS `z`
  FROM `lf1`
),
`q02` AS (
  SELECT NULL AS `x`, `lf2`.*
  FROM `lf2`
),
`q03` AS (
  SELECT `q01`.*, NULL AS `z`
  FROM `q02` AS `q01`
),
`q04` AS (
  SELECT NULL AS `x`, NULL AS `y`, `lf3`.*
  FROM `lf3`
),
`q05` AS (
  SELECT *
  FROM `q01`

  UNION ALL

  SELECT *
  FROM `q03`

  UNION

  SELECT *
  FROM `q04`
)
SELECT `LHS`.`x` AS `x`, `LHS`.`y` AS `y.x`, `z`, `lf1`.`y` AS `y.y`
FROM `q05` AS `LHS`
LEFT JOIN `lf1`
  ON (`LHS`.`x` = `lf1`.`x`)
```

--------------------------------

### Generate Empty Table Schema with copy_inline and slice(0)

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-hana.md

Illustrates how `dbplyr`'s `copy_inline` function, when used with `slice(0)`, generates a SQL query to create an empty table schema with appropriate NULL casts for column types, useful for defining table structures without data.

```R
copy_inline(con, y %>% slice(0)) %>% remote_query()
```

```SQL
SELECT CAST(NULL AS INTEGER) AS `id`, CAST(NULL AS VARCHAR) AS `arr`
FROM `DUMMY`
WHERE (0 = 1)
```

--------------------------------

### Generate SQL for adding count after grouping with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-count.md

Shows how `dbplyr`'s `add_count()` behaves when chained after `group_by()`, generating a SQL window function to add a count column `n` partitioned by the grouped column `g`.

```R
db %>% group_by(g) %>% add_count()
```

```SQL
SELECT `df`.*, COUNT(*) OVER (PARTITION BY `g`) AS `n`
FROM `df`
```

--------------------------------

### Translate R paste0 to SQL Concatenation

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-hana.md

Demonstrates how `dbplyr` translates the R `paste0` function, which concatenates strings without a separator, into SQL's `||` operator.

```R
test_translate_sql(paste0("a", "b"))
```

```SQL
'a' || 'b'
```

--------------------------------

### Generate SQL for Data Definition Language (DDL) Operations

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-.md

Provides examples of how `dbplyr` functions translate into various SQL DDL commands. This includes analyzing tables, explaining queries, wrapping table identifiers, creating indexes (unique and non-unique), and saving query results into temporary tables.

```R
sql_table_analyze(con, in_schema("schema", "tbl"))
```

```SQL
ANALYZE `schema`.`tbl`
```

```R
sql_query_explain(con, sql("SELECT * FROM foo"))
```

```SQL
EXPLAIN SELECT * FROM foo
```

```R
sql_query_wrap(con, ident("table"))
```

```SQL
<table_path> `table`
```

```R
sql_query_wrap(con, in_schema("schema", "tbl"))
```

```SQL
<table_path> `schema`.`tbl`
```

```R
sql_query_wrap(con, sql("SELECT * FROM foo"))
```

```SQL
(SELECT * FROM foo) AS `q01`
```

```R
sql_table_index(con, in_schema("schema", "tbl"), c("a", "b"))
```

```SQL
CREATE INDEX `tbl_a_b` ON `schema`.`tbl` (`a`, `b`)
```

```R
sql_table_index(con, in_schema("schema", "tbl"), "c", unique = TRUE)
```

```SQL
CREATE UNIQUE INDEX `tbl_c` ON `schema`.`tbl` (`c`)
```

```R
sql_query_save(con, sql("SELECT * FROM foo"), in_schema("temp", "tbl"))
```

```SQL
CREATE TEMPORARY TABLE 
`temp`.`tbl` AS
SELECT * FROM foo
```

--------------------------------

### Format Identifier using `ident()`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/ident.md

This snippet illustrates the basic usage of the `ident()` function to create an identifier object. In this example, `ident()` is called without arguments, resulting in an empty identifier object as output.

```R
ident()
```

--------------------------------

### dbplyr: `head()` function translation to SQL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-access.md

Demonstrates how dbplyr successfully translates the R `head()` function, applied to a data frame object, into an equivalent SQL `SELECT TOP` query. This illustrates dbplyr's capability to generate correct and efficient SQL for common R data manipulation operations.

```R
mf %>% head()
```

```SQL
SELECT TOP 6 `df`.*
FROM `df`
```

--------------------------------

### Copy Inline Data to SQL Table Using UNION ALL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-hana.md

Demonstrates `dbplyr`'s `copy_inline` function for transferring R data frames directly into a SQL database. It constructs a SQL query that uses `UNION ALL` to combine an empty schema definition with the actual data rows, effectively creating a temporary table or view.

```R
copy_inline(con, y) %>% remote_query()
```

```SQL
SELECT CAST(`id` AS INTEGER) AS `id`, CAST(`arr` AS VARCHAR) AS `arr`
FROM (
  SELECT NULL AS `id`, NULL AS `arr`
  FROM `DUMMY`
  WHERE (0 = 1)

  UNION ALL

  SELECT 1, '{1,2,3}' FROM DUMMY
) AS `values_table`
```

--------------------------------

### Applying arrange() after union() in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-arrange.md

This example shows how to correctly apply `arrange()` after a `union()` operation. The `ORDER BY` clause is applied to the combined result of the `UNION` query, ensuring the entire dataset is sorted as expected.

```R
lf <- lazy_frame(a = 1:3, b = 3:1)
rf <- lazy_frame(a = 1:3, c = 4:6)
lf %>% union(rf) %>% arrange(a)
```

```SQL
SELECT `q01`.*
FROM (
  SELECT `df`.*, NULL AS `c`
  FROM `df`

  UNION

  SELECT `a`, NULL AS `b`, `c`
  FROM `df`
) AS `q01`
ORDER BY `a`
```

--------------------------------

### Generate empty table schema with copy_inline and slice(0) in R

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-redshift.md

Demonstrates how `copy_inline` combined with `slice(0)` generates a SQL query to retrieve only the schema of a table without any data, using `WHERE (0 = 1)` to ensure no rows are returned. This is useful for inspecting table structure.

```R
copy_inline(con, y %>% slice(0)) %>% remote_query()
```

```SQL
SELECT CAST(NULL AS INTEGER) AS `id`, CAST(NULL AS TEXT) AS `arr`
WHERE (0 = 1)
```

--------------------------------

### Generate SQL and Explain Query Plan

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-mysql.md

Demonstrates how `explain()` generates the SQL query for a `dbplyr` pipeline and then provides the database's execution plan for that query, useful for performance analysis.

```R
db %>% mutate(y = x + 1) %>% explain()
```

```SQL
SELECT `test`.*, `x` + 1.0 AS `y`
FROM `test`

<PLAN>
  id select_type table type possible_keys  key key_len  ref rows Extra
1  1      SIMPLE  test  ALL          <NA> <NA>    <NA> <NA>    3      
```

--------------------------------

### Deprecated `check_from` Argument in `tbl_sql()`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/tbl-sql.md

Shows the usage of the `check_from` argument with `tbl_sql()`, which is deprecated as of dbplyr 2.5.0. This snippet highlights how warnings are issued for deprecated features, guiding users to update their code and avoid using the argument.

```R
out <- tbl(con, "x", check_from = FALSE)
```

--------------------------------

### Subquery Generation for head() after distinct()

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-teradata.md

Explains how applying `head()` after `distinct()` in dbplyr results in a SQL subquery. This ensures that distinct rows are first identified, and then the `TOP` clause is applied to the result set.

```R
lf %>% distinct() %>% head()
```

```SQL
SELECT TOP 6 `q01`.*
FROM (
  SELECT DISTINCT `df`.*
  FROM `df`
) AS `q01`
```

--------------------------------

### Using a Formula for values_fn in dbplyr pivot_wider

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Shows how to provide a formula (e.g., `~ sum(.x, na.rm = TRUE)`) as `values_fn` to `dbplyr_pivot_wider_spec`, allowing for more flexible aggregation logic that translates to SQL.

```R
dbplyr_pivot_wider_spec(df, spec1, values_fn = ~ sum(.x, na.rm = TRUE))
```

```SQL
SELECT `a`, SUM(CASE WHEN (`key` = 'x') THEN `val` END) AS `x`
FROM `df`
GROUP BY `a`
```

--------------------------------

### Validate inputs for `copy_inline` function

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-copy-to.md

Presents various error cases for `copy_inline`, showing how the function validates inputs such as requiring at least one column, a data frame type, and matching names and types for the `types` argument, along with the specific error messages.

```R
(expect_error(copy_inline(con, tibble())))
Output
  <error/rlang_error>
  Error in `copy_inline()`:
  ! `df` needs at least one column.
```

```R
(expect_error(copy_inline(con, lazy_frame(a = 1))))
Output
  <error/rlang_error>
  Error in `copy_inline()`:
  ! `df` needs to be a data.frame.
```

```R
(expect_error(copy_inline(con, tibble(a = 1), types = c(b = "bigint"))))
Output
  <error/rlang_error>
  Error in `copy_inline()`:
  ! Names of `df` and `types` must be the same.
```

```R
(expect_error(copy_inline(con, tibble(a = 1), types = c(b = 1))))
Output
  <error/rlang_error>
  Error in `copy_inline()`:
  ! `types` must be a character vector, not the number 1.
```

--------------------------------

### Pivot All Columns to Wide Format with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Demonstrates how to pivot all columns in a lazy data frame to a wide format using `dbplyr_pivot_wider_spec`, generating a SQL query that uses `MAX(CASE WHEN ...)` for aggregation.

```R
lazy_frame(key = c("x", "y", "z"), val = 1:3) %>% dbplyr_pivot_wider_spec(spec)
```

```SQL
SELECT
  MAX(CASE WHEN (`key` = 'x') THEN `val` END) AS `x`,
  MAX(CASE WHEN (`key` = 'y') THEN `val` END) AS `y`,
  MAX(CASE WHEN (`key` = 'z') THEN `val` END) AS `z`
FROM `df`
```

--------------------------------

### R substr() error: Invalid start argument type

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-string.md

Shows an error when `substr()` is called with a non-numeric value ('x') for the `start` argument. The function expects `start` to be a whole number, but a string is provided, causing a type mismatch error.

```R
substr("test", "x", 1)
```

--------------------------------

### Translate R substring to SQL SUBSTRING

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-hana.md

Demonstrates `dbplyr`'s translation of the R `substring` function, which is similar to `substr`, into the SQL `SUBSTRING` function. The length parameter calculation difference applies here as well.

```R
test_translate_sql(substring(x, 2, 4))
```

```SQL
SUBSTRING(`x`, 2, 3)
```

--------------------------------

### Translate single-column R data frame to SQL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-copy-to.md

Illustrates `copy_inline`'s ability to translate a single-column R data frame into a SQL `SELECT` statement, demonstrating type casting for numeric values.

```R
copy_inline(con, tibble(dbl = 1.5)) %>% remote_query()
```

```SQL
<SQL> SELECT CAST(`dbl` AS REAL) AS `dbl`
FROM (
  SELECT NULL AS `dbl`
  WHERE (0 = 1)

  UNION ALL

  VALUES (1.5)
) AS `values_table`
```

--------------------------------

### Explain a dbplyr query and show SQL plan

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-sqlite.md

Demonstrates how to use the `explain()` function in dbplyr to view both the generated SQL query and its execution plan, providing insights into database optimization and index usage.

```R
db %>% filter(x > 2) %>% explain()
```

```SQL
SELECT `test`.*
FROM `test`
WHERE (`x` > 2.0)
```

```PLAN
id parent notused                                        detail
1  2      0      35 SEARCH test USING COVERING INDEX test_x (x>?)
```

--------------------------------

### Translate R str_sub with Negative Index to SQL SUBSTRING

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-hana.md

Shows how `dbplyr` translates the `stringr::str_sub` function, particularly when a negative end index is used, into SQL's `SUBSTRING` function combined with `LENGTH` for dynamic length calculation.

```R
test_translate_sql(str_sub(x, 2, -2))
```

```SQL
SUBSTRING(`x`, 2, LENGTH(`x`) - 2)
```

--------------------------------

### R substr() error: Missing stop argument

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-string.md

Illustrates an error when `substr()` is called with only the `string` and `start` arguments, where `start` is 0, and the `stop` argument is implicitly missing. The function requires `stop` to be a whole number.

```R
substr("test", 0)
```

--------------------------------

### Generate SQL `EXPLAIN PLAN` with `sql_query_explain`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-oracle.md

Demonstrates using `sql_query_explain` to generate SQL statements for explaining a query plan, useful for performance analysis.

```R
sql_query_explain(con, sql("SELECT * FROM foo"))
```

```SQL
EXPLAIN PLAN FOR SELECT * FROM foo
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())
```

--------------------------------

### Translate R paste to SQL Concatenation with Space

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-hana.md

Shows how `dbplyr` translates the R `paste` function, which concatenates strings with a default space separator, into SQL's `||` operator including the space.

```R
test_translate_sql(paste("a", "b"))
```

```SQL
'a' || ' ' || 'b'
```

--------------------------------

### Insert inline data with copy_inline using UNION ALL in R

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-redshift.md

Illustrates how `copy_inline` translates R data frames into SQL `UNION ALL` statements for efficient inline data insertion into a database. It shows the generated SQL for inserting a single row with specific data types.

```R
copy_inline(con, y) %>% remote_query()
```

```SQL
SELECT CAST(`id` AS INTEGER) AS `id`, CAST(`arr` AS TEXT) AS `arr`
FROM (
  SELECT NULL AS `id`, NULL AS `arr`
  WHERE (0 = 1)

  UNION ALL

  SELECT 1, '{1,2,3}'
) AS `values_table`
```

--------------------------------

### Basic `arrange()` Usage in `dbplyr`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-arrange.md

Illustrates the fundamental use of `arrange()` on a `lazy_frame` to order data, showing the resulting SQL `ORDER BY` clause.

```R
lf <- lazy_frame(a = 1:3, b = 3:1)
lf %>% arrange(a)
```

```SQL
SELECT `df`.*
FROM `df`
ORDER BY `a`
```

--------------------------------

### Generate Row Numbers in SQL with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-teradata.md

Illustrates how dbplyr translates `row_number()` in R to SQL's `ROW_NUMBER()` window function. It shows its behavior with and without `group_by()` and `arrange()`, and how it defaults to `ORDER BY (SELECT NULL)` when no explicit order is specified.

```R
mf %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS `rown`
FROM `df`
```

```R
mf %>% group_by(y) %>% mutate(rown = row_number())
```

```SQL
SELECT
  `df`.*,
  ROW_NUMBER() OVER (PARTITION BY `y` ORDER BY (SELECT NULL)) AS `rown`
FROM `df`
```

```R
mf %>% arrange(y) %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY `y`) AS `rown`
FROM `df`
ORDER BY `y`
```

--------------------------------

### Error: values_fn Cannot Be NULL in dbplyr pivot_wider

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Demonstrates that `values_fn` cannot be `NULL` when using `dbplyr_pivot_wider_spec`, as it requires a function for each column in `values_from`.

```R
dbplyr_pivot_wider_spec(df, spec1, values_fn = NULL)
```

```Error
Error in `dbplyr_pivot_wider_spec()`:
! `values_fn` must specify a function for each col in `values_from`
```

--------------------------------

### Translating `left_join` with `na_matches = "na"`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Demonstrates how `dbplyr` translates an R `left_join` operation with `na_matches = "na"` into SQL, specifically showing the `IS NOT DISTINCT FROM` clause for NA matching.

```R
left_join(lf, lf, by = "x", na_matches = "na")
```

```SQL
SELECT `df_LHS`.`x` AS `x`
FROM `df` AS `df_LHS`
LEFT JOIN `df` AS `df_RHS`
  ON (`df_LHS`.`x` IS NOT DISTINCT FROM `df_RHS`.`x`)
```

--------------------------------

### Handle `pull()` error with multiple arguments and non-existent column in R

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pull.md

Demonstrates the error message when `pull()` is called with multiple arguments, including one that refers to a non-existent column. The error specifically points out the missing column.

```R
memdb_frame(x = 1) %>% pull(x, "name_non_existent")
Condition
  Error in `pull()`:
  ! Can't extract columns that don't exist.
  x Column `name_non_existent` doesn't exist.
```

--------------------------------

### Translating `lubridate::quarter` with `fiscal_start` in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Illustrates that `fiscal_start = 2` is not supported when translating `quarter()` for PostgreSQL. It must be `1` instead.
Error: `fiscal_start = 2` isn't supported in PostgreSQL translation. It must be 1 instead.

```R
test_translate_sql(quarter(x, fiscal_start = 2))
```

--------------------------------

### Generate SQL for adding sorted count with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-count.md

Demonstrates `dbplyr`'s `add_count()` function, which adds a new column `n` representing the count for each group `g` using a SQL window function (`COUNT(*) OVER (PARTITION BY `g`)`) and then sorts the entire result set.

```R
db %>% add_count(g, sort = TRUE)
```

```SQL
SELECT `df`.*, COUNT(*) OVER (PARTITION BY `g`) AS `n`
FROM `df`
ORDER BY `n` DESC
```

--------------------------------

### Retrieving Returned Rows with get_returned_rows()

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/rows.md

Demonstrates the usage of `get_returned_rows()`. This example shows that calling the function when no rows have been returned by a preceding operation (e.g., in a simulated environment) will result in an error indicating no returned rows are available.

```R
(get_returned_rows(df))
```

--------------------------------

### Generate empty table schema with copy_inline and explicit types in R

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-redshift.md

Shows how `copy_inline` with `slice(0)` and an explicit `types` argument generates a SQL query for an empty table schema, respecting the specified data types (e.g., `bigint`, `integer[]`). This allows precise control over the resulting SQL schema.

```R
copy_inline(con, y %>% slice(0), types = types) %>% remote_query()
```

```SQL
SELECT CAST(NULL AS bigint) AS `id`, CAST(NULL AS integer[]) AS `arr`
WHERE (0 = 1)
```

--------------------------------

### `.value` Placeholder at Start of `names_to` in dbplyr Pivot

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-longer.md

Demonstrates the flexibility of the `.value` placeholder within the `names_to` argument, showing how it can be placed at the beginning to control the naming of pivoted columns.

```R
value_first
```

```SQL
SELECT `i`, 't1' AS `time`, `y_t1` AS `y`, `z_t1` AS `z`
FROM `df`

UNION ALL

SELECT `i`, 't2' AS `time`, `y_t2` AS `y`, `z_t2` AS `z`
FROM `df`
```

--------------------------------

### Error: values_from Required if 'value' Column Missing

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Shows an error when `values_from` is not supplied and the default 'value' column is absent from the data, resulting in a column selection error.

```R
(expect_error(tidyr::pivot_wider(df, names_from = key)))
```

```Error
<error/vctrs_error_subscript_oob>
Error in `dbplyr_build_wider_spec()`:
! Can't select columns that don't exist.
x Column `value` doesn't exist.
```

--------------------------------

### Specifying Schema with dbplyr compute

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-compute.md

Illustrates an attempt to use `dbplyr::compute()` to create a table within a specific schema (`main.db1`) when `temporary = FALSE`. The example shows a simulated `DBI` error during the table creation process, indicating issues with saving the query to the specified schema.

```R
df %>% compute(name = in_schema("main", "db1"), temporary = FALSE)
```

--------------------------------

### SQL Quoting for Summarized Grouped Tables in `dbplyr`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-summarise.md

This example illustrates how `dbplyr` renders SQL queries for summarized and grouped tables, specifically demonstrating the quoting mechanism used for identifiers like table and column names to ensure proper SQL syntax.

```R
out %>% sql_render
```

```SQL
SELECT `x`, COUNT(*) AS `n`
FROM `verb-summarise`
GROUP BY `x`
```

--------------------------------

### Generate SQL to Explain Query Plan in MariaDB

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-mysql.md

Shows how `sql_query_explain` translates an R expression into an `EXPLAIN` SQL statement, allowing users to view the execution plan for a given query in MariaDB.

```R
sql_query_explain(con_maria, sql("SELECT * FROM table"))
```

```SQL
EXPLAIN SELECT * FROM table
```

--------------------------------

### Error: names_from Required if 'name' Column Missing

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Demonstrates an error when `names_from` is not supplied and the default 'name' column is not present in the data, leading to a column selection error.

```R
(expect_error(tidyr::pivot_wider(df, values_from = val)))
```

```Error
<error/vctrs_error_subscript_oob>
Error in `dbplyr_build_wider_spec()`:
! Can't select columns that don't exist.
x Column `name` doesn't exist.
```

--------------------------------

### Address deprecated `do()` syntax in R (dplyr 0.2+)

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-do.md

This example shows an error when using the old `do(.f = ...)` syntax, which was deprecated in dplyr 0.2. Users should consult the updated documentation for the correct `do()` usage to avoid this `named_args()` error.

```R
mf %>% do(.f = nrow)
Error in `named_args()`:
! `do()` syntax changed in dplyr 0.2. Please see documentation for details
```

--------------------------------

### Translating `paste0` with `collapse` in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Demonstrates that `paste0` with the `collapse` argument is not supported for database translation in `dbplyr`. The error message suggests using `str_flatten()` instead.
Error: `collapse` not supported in DB translation of `paste()`. Please use `str_flatten()` instead.

```R
test_translate_sql(paste0(x, collapse = ""), window = FALSE)
```

--------------------------------

### Translate R data frame to SQL using `copy_inline`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-copy-to.md

Shows how `copy_inline` translates a standard R data frame with various data types (logical, integer, double, character, date, datetime) into an equivalent SQL `SELECT` statement, including type casting and a `UNION ALL` structure for inline values.

```R
copy_inline(con, df) %>% remote_query()
```

```SQL
<SQL> SELECT
  CAST(`lgl` AS BOOLEAN) AS `lgl`,
  CAST(`int` AS INTEGER) AS `int`,
  CAST(`dbl` AS REAL) AS `dbl`,
  CAST(`chr` AS TEXT) AS `chr`,
  CAST(`date` AS TEXT) AS `date`,
  CAST(`dtt` AS TEXT) AS `dtt`
FROM (
  SELECT
    NULL AS `lgl`,
    NULL AS `int`,
    NULL AS `dbl`,
    NULL AS `chr`,
    NULL AS `date`,
    NULL AS `dtt`
  WHERE (0 = 1)

  UNION ALL

  VALUES (1, 1, 1.5, 'a', '2020-01-01', '2020-01-01T01:23:45Z')
) AS `values_table`
```

--------------------------------

### Error: Cannot Pivot Local Lazy Tibbles with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Explains that `dbplyr_build_wider_spec()` does not work with local lazy tibbles and suggests using `memdb_frame()` with `show_query()` to inspect SQL code.

```R
tidyr::pivot_wider(lazy_frame(name = "x", value = 1))
```

```Error
Error in `dbplyr_build_wider_spec()`:
! `dbplyr_build_wider_spec()` doesn't work with local lazy tibbles.
i Use `memdb_frame()` together with `show_query()` to see the SQL code.
```

--------------------------------

### Using rows_delete() with in_place = TRUE

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/rows.md

Demonstrates the behavior of `rows_delete()` when `in_place = TRUE` is specified. This example highlights a limitation where `in_place = TRUE` does not function with simulated connections, resulting in an error.

```R
(rows_delete(lazy_frame(x = 1:3, y = 11:13, .name = "df_x"), lazy_frame(x = 2:3,
.name = "df_y"), by = "x", unmatched = "ignore", in_place = TRUE))
```

--------------------------------

### Translate `row_number` with `group_by` and `arrange` in SQL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-snowflake.md

These snippets illustrate how `dbplyr` translates the `row_number()` R function into SQL, demonstrating its behavior with and without `group_by()` and `arrange()`. When no explicit ordering is provided, `row_number()` defaults to ordering by `NULL` in the SQL `OVER` clause, ensuring consistent behavior for unordered partitions.

```R
mf %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS `rown`
FROM `df`
```

```R
mf %>% group_by(y) %>% mutate(rown = row_number())
```

```SQL
SELECT
  `df`.*,
  ROW_NUMBER() OVER (PARTITION BY `y` ORDER BY (SELECT NULL)) AS `rown`
FROM `df`
```

```R
mf %>% arrange(y) %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY `y`) AS `rown`
FROM `df`
ORDER BY `y`
```

--------------------------------

### Using `sql_query_insert` with unsupported `conflict = "error"`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Highlights that `conflict = "error"` is not supported for `sql_query_insert()` on database backends. The error message suggests using `"ignore"` instead.
Error: `conflict = "error"` isn't supported on database backends. It must be "ignore" instead.

```R
(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 = "error",
returning_cols = c("a", b2 = "b")))
```

--------------------------------

### Validation of unused_fn Parameter in tidyr::pivot_wider

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Shows that `tidyr::pivot_wider` validates the `unused_fn` argument, expecting it to be `NULL`, a function, or a named list of functions, and throws an error otherwise.

```R
(expect_error(tidyr::pivot_wider(df, id_cols = id, unused_fn = 1)))
```

```Error
<error/rlang_error>
Error in `tidyr::pivot_wider()`:
! `unused_fn` must be `NULL`, a function, or a named list of functions.
```

--------------------------------

### Handle Implicit Missings in dbplyr pivot_wider

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Illustrates how `dbplyr_pivot_wider_spec` converts implicit missing values into explicit ones during the pivoting process, resulting in a SQL query with `GROUP BY` clause.

```R
lazy_frame(a = 1:2, key = c("x", "y"), val = 1:2) %>% dbplyr_pivot_wider_spec(
  spec)
```

```SQL
SELECT
  `a`,
  MAX(CASE WHEN (`key` = 'x') THEN `val` END) AS `x`,
  MAX(CASE WHEN (`key` = 'y') THEN `val` END) AS `y`
FROM `df`
GROUP BY `a`
```

--------------------------------

### Translating `clock::date_count_between` with `n = 5`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Highlights that the argument `n = 5` is not supported for `date_count_between()` on database backends. It must be `1` instead.
Error: `n = 5` isn't supported on database backends. It must be 1 instead.

```R
test_translate_sql(date_count_between(date_column_1, date_column_2, "day", n = 5))
```

--------------------------------

### Handle `collapse` argument in `paste0` translation

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-snowflake.md

This snippet demonstrates an error when attempting to translate `paste0` with the `collapse` argument using `dbplyr`. It indicates that `collapse` is not supported for direct database translation of `paste()` and suggests using `str_flatten()` instead for string concatenation with collapse.

```R
test_translate_sql(paste0(x, collapse = ""))
```

--------------------------------

### Translating `quantile` as a window function in PostgreSQL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Explains that `quantile()` used as a window function within `mutate()` is not supported for PostgreSQL. It suggests an alternative approach using a combination of `summarise()` and `left_join()`.
Error: Translation of `quantile()` in `mutate()` is not supported for PostgreSQL. Use a combination of `summarise()` and `left_join()` instead: `df %>% left_join(summarise(<col> = quantile(x, 0.3, na.rm = TRUE)))`.

```R
test_translate_sql(quantile(x, 0.3, na.rm = TRUE), window = TRUE)
```

--------------------------------

### Generate SQL to Analyze MariaDB Table

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-mysql.md

Demonstrates how `sql_table_analyze` generates an `ANALYZE TABLE` statement for a specified schema and table in MariaDB, optimizing query performance.

```R
sql_table_analyze(con_maria, in_schema("schema", "tbl"))
```

```SQL
ANALYZE TABLE `schema`.`tbl`
```

--------------------------------

### Generate SQL for `window_frame()` in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-window.md

These examples demonstrate how `window_frame()` in dbplyr translates to SQL `ROWS` clauses for window functions, showing both fixed preceding rows and unbounded following frames.

```R
lf %>% window_frame(-3, 0) %>% window_order(x) %>% mutate(z = sum(y, na.rm = TRUE)) %>% show_query()
```

```SQL
SELECT `df`.*, SUM(`y`) OVER (ORDER BY `x` ROWS 3 PRECEDING) AS `z`
FROM `df`
```

```R
lf %>% window_frame(-3) %>% window_order(x) %>% mutate(z = sum(y, na.rm = TRUE)) %>% show_query()
```

```SQL
SELECT
  `df`.*,
  SUM(`y`) OVER (ORDER BY `x` ROWS BETWEEN 3 PRECEDING AND UNBOUNDED FOLLOWING) AS `z`
FROM `df`
```

--------------------------------

### Understanding `names()` Method for `tbl_lazy` (R)

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/tbl-lazy.md

This example highlights the internal nature of the `names()` method for `tbl_lazy` objects. When called, it issues a message advising that it's for internal use only and suggests using `colnames()` instead, while still returning the internal names `lazy_query` and `src`.

```R
names(lazy_frame(x = 1))
```

--------------------------------

### Handle `row_number()` with `c()` in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-window.md

This example shows an informative error message generated when `c()` is incorrectly used as an argument within the `row_number()` function, suggesting the correct usage with `tibble()`.

```R
test_translate_sql(row_number(c(x)))
```

```Console Output
Error in `row_number()`:
! Can't use `c()` in `ROW_NUMBER()`
i Did you mean to use `tibble(x)` instead?
```

--------------------------------

### Handle Missing `I()` for Table Names in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/tbl-sql.md

Illustrates the error encountered when attempting to access a table with a compound name (e.g., `foo.bar`) without using `I()` to explicitly mark it as a literal identifier. The error message clearly suggests the correct usage: `from = I("foo.bar")` to resolve the 'no such table' error.

```R
tbl(src_memdb(), "foo.bar")
```

--------------------------------

### Translating `difftime` with `units = "auto"`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Demonstrates that `units = "auto"` is not supported when translating `difftime()` for database backends. It must be `"days"` instead.
Error: `units = "auto"` isn't supported on database backends. It must be "days" instead.

```R
test_translate_sql(difftime(start_date, end_date, units = "auto"))
```

--------------------------------

### Unsupported `slice` functions on `dbplyr` database backends

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-slice.md

Demonstrates that `slice()`, `slice_head()`, and `slice_tail()` are not supported when working with database backends in `dbplyr`, providing the specific error messages and suggesting alternatives like `slice_min()` and `slice_max()`.

```R
lf %>% slice()
Condition
Error in `slice()`:
! `slice()` is not supported on database backends.
```

```R
lf %>% slice_head()
Condition
Error in `slice_head()`:
! `slice_head()` is not supported on database backends.
i Please use `slice_min()` instead.
```

```R
lf %>% slice_tail()
Condition
Error in `slice_tail()`:
! `slice_tail()` is not supported on database backends.
i Please use `slice_max()` instead.
```

--------------------------------

### Untranslatable functions are preserved by across() in SQL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/tidyeval-across.md

This example shows that `across()` passes functions that cannot be translated to SQL directly into the generated SQL query, assuming they are valid SQL functions.

```R
lf %>% summarise(across(a:b, SQL_LOG))
```

```SQL
SELECT SQL_LOG(`a`) AS `a`, SQL_LOG(`b`) AS `b`
FROM `df`
```

--------------------------------

### dbplyr: Performing SQL INSERT with Conflict Handling

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-mssql.md

Illustrates `sql_query_insert()` for inserting data from a source query (`df_y`) into a target table (`df_x`). This example includes conflict resolution (`conflict = "ignore"`) and specifies returning columns, generating an `INSERT ... OUTPUT ... SELECT ... WHERE NOT EXISTS` statement for upsert-like behavior.

```R
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"))
```

```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`)
)
```

--------------------------------

### Translating `median` as a window function in PostgreSQL

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-postgres.md

Explains that `median()` used as a window function within `mutate()` is not supported for PostgreSQL. It suggests an alternative approach using a combination of `summarise()` and `left_join()`.
Error: Translation of `median()` in `mutate()` is not supported for PostgreSQL. Use a combination of `summarise()` and `left_join()` instead: `df %>% left_join(summarise(<col> = median(x, na.rm = TRUE)))`.

```R
test_translate_sql(median(x, na.rm = TRUE), window = TRUE)
```

--------------------------------

### Generate and Print SQL Query Structure with dbplyr's lazy_select_query

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/lazy-select-query.md

This snippet uses `dbplyr::lazy_select_query` to define a lazy SQL query. It specifies selections (`x_mean`, `y2`), filtering conditions (`y > 1`, `x == y - 2`), and grouping (`x`). The output shows the structured SQL representation that `dbplyr` would generate, detailing the `FROM`, `SELECT`, `WHERE`, and `GROUP BY` clauses.

```R
lazy_select_query(x = lf$lazy_query, select = quos(x_mean = mean(x), y2 = y),
      where = quos(y > 1, x == y - 2), group_by = quos("x"))
```

```SQL
<SQL SELECT>
From:
  <table_path> `df`
Select:   x_mean = mean(x), y2 = y
Where:    y > 1, x == y - 2
Group by: "x"
```

--------------------------------

### Create Temporary Database Table with Custom Prefix using dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-mssql.md

Illustrates how to create a temporary table in a database connection using `dbplyr`'s `db_table_temporary()` function. It shows how to specify a table path and that a temporary table name is generated with a prefix (e.g., `#`).

```R
out <- db_table_temporary(con, table_path("foo.bar"), temporary = TRUE)
```

--------------------------------

### Using a Single Function for values_fn in dbplyr pivot_wider

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Demonstrates how to apply a single aggregation function (e.g., `sum`) to `values_fn` in `dbplyr_pivot_wider_spec`, translating it into a `SUM(CASE WHEN ...)` SQL expression.

```R
suppressWarnings(dbplyr_pivot_wider_spec(df, spec1, values_fn = sum))
```

```SQL
SELECT `a`, SUM(CASE WHEN (`key` = 'x') THEN `val` END) AS `x`
FROM `df`
GROUP BY `a`
```

--------------------------------

### Translate `head()` to SQL `FETCH FIRST`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-oracle.md

Illustrates how `dbplyr` translates the R `head()` function, typically used for previewing data, into a SQL query using `FETCH FIRST N ROWS ONLY`.

```R
mf %>% head()
```

```SQL
SELECT `df`.* FROM `df` FETCH FIRST 6 ROWS ONLY
```

--------------------------------

### Chaining `arrange()` with `head()` in `dbplyr`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-arrange.md

Illustrates a sequence of `arrange()`, `head()`, and another `arrange()`. The first `arrange()` orders the data before `head()` limits it, and the second `arrange()` reorders the limited subquery result.

```R
lf %>% arrange(a) %>% head(1) %>% arrange(b)
```

```SQL
SELECT `q01`.*
FROM (
  SELECT `df`.*
  FROM `df`
  ORDER BY `a`
  LIMIT 1
) AS `q01`
ORDER BY `b`
```

--------------------------------

### Expected errors with tidyr::expand in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-expand.md

Illustrates common error scenarios when using `tidyr::expand()`, specifically when no variables are supplied or when a variable is explicitly set to `NULL` without other valid inputs, leading to a `Must supply variables in ...` error.

```R
tidyr::expand(memdb_frame(x = 1))
```

```Error
Error in `tidyr::expand()`:
! Must supply variables in `...`
```

```R
tidyr::expand(memdb_frame(x = 1), x = NULL)
```

```Error
Error in `tidyr::expand()`:
! Must supply variables in `...`
```

--------------------------------

### dbplyr: Handling Named Inputs in filter() for Equality Checks

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-filter.md

Illustrates the error dbplyr throws when `=` is used instead of `==` for equality checks within `filter()`, guiding the user to the correct syntax.

```R
filter(lf, x = 1)
```

```Error
Error in `filter()`:
! Problem with `filter()` input `..1`.
x Input `..1` is named.
i This usually means that you've used `=` instead of `==`.
i Did you mean `x == 1`?
```

```R
filter(lf, y > 1, x = 1)
```

```Error
Error in `filter()`:
! Problem with `filter()` input `..2`.
x Input `..2` is named.
i This usually means that you've used `=` instead of `==`.
i Did you mean `x == 1`?
```

--------------------------------

### `arrange()` Before `head()` in `dbplyr`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-arrange.md

Demonstrates the SQL generated when `arrange()` is applied before `head()`. The data is first ordered, and then the `LIMIT` clause is applied to the ordered result.

```R
lf %>% arrange(a) %>% head(1)
```

```SQL
SELECT `df`.*
FROM `df`
ORDER BY `a`
LIMIT 1
```

--------------------------------

### Error: values_fill Argument Validation in dbplyr pivot_wider

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Shows that `values_fill` in `dbplyr_pivot_wider_spec` must be `NULL`, a scalar, or a named list, and an error is thrown if an invalid type like an integer vector is provided.

```R
dbplyr_pivot_wider_spec(lf, spec, values_fill = 1:2)
```

```Error
Error in `dbplyr_pivot_wider_spec()`:
! `values_fill` must be `NULL`, a scalar, or a named list, not an integer vector.
```

--------------------------------

### Verify dbplyr print method output for chained left joins

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/query-join.md

This snippet verifies that the internal `dbplyr` print method output for a sequence of `left_join` operations remains consistent. It demonstrates how `sql_build()` visualizes the join structure.

```R
left_join(lf1, lf2, by = "x") %>% left_join(lf3, by = "x") %>% sql_build()
```

```dbplyr internal
<SQL JOINS>
X:
  <table_path> `lf1`
Type: left
By:
  x-x
Y:
  <table_path> `lf2`
Type: left
By:
  x-x
Y:
  <table_path> `lf3`
```

--------------------------------

### Handle `copy_to` with invalid `df` input

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-copy-to.md

Demonstrates an error when `copy_to` is used with an invalid `df` argument, which must be a local dataframe or a remote `tbl_sql` object, highlighting the specific error message.

```R
copy_to(con, list(x = 1), name = "df")
Condition
  Error in `copy_to()`:
  ! `df` must be a local dataframe or a remote tbl_sql
```

--------------------------------

### Render SQL for Ordered Grouped Table with `sql_render()`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-arrange.md

Demonstrates how `dbplyr`'s `sql_render()` function correctly quotes table and column names when rendering SQL for an ordered grouped table, ensuring proper SQL syntax for `ORDER BY` clauses.

```R
sql_render(out)
```

```SQL
SELECT `test-verb-arrange`.*
FROM `test-verb-arrange`
ORDER BY `y`
```

--------------------------------

### Translate `row_number()` with `group_by()` and `arrange()` in dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-window.md

These examples illustrate how `row_number()` translates to SQL's `ROW_NUMBER() OVER()` clause under different `dbplyr` pipeline configurations, including no grouping/ordering, `group_by()` for `PARTITION BY`, and `arrange()` for `ORDER BY`.

```R
mf %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER () AS `rown`
FROM `df`
```

```R
mf %>% group_by(y) %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER (PARTITION BY `y`) AS `rown`
FROM `df`
```

```R
mf %>% group_by(y) %>% arrange(y) %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER (PARTITION BY `y` ORDER BY `y`) AS `rown`
FROM `df`
ORDER BY `y`
```

```R
mf %>% arrange(y) %>% mutate(rown = row_number())
```

```SQL
SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY `y`) AS `rown`
FROM `df`
ORDER BY `y`
```

--------------------------------

### Connect to SQLite and Copy Data with dbplyr

Source: https://github.com/tidyverse/dbplyr/blob/main/README.md

This R snippet demonstrates how to establish an in-memory SQLite database connection using `DBI` and `RSQLite`, and then copy a local R dataset (`mtcars`) into it using `copy_to` for use with dbplyr.

```R
library(dplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, mtcars)
```

--------------------------------

### dbplyr: Displaying a Stored Query

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/backend-mssql.md

Illustrates the SQL output for a previously defined `dbplyr` query object. This confirms the translation of an `IN` clause with custom escaped values, similar to the previous filter example.

```R
qry
```

```SQL
SELECT `df`.*
FROM `df`
WHERE (`x` IN (0x616263, 0x0102))
```

--------------------------------

### Translate R median to SQL PERCENTILE_CONT (with grouping)

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/translate-sql-quantile.md

Demonstrates the translation of the R `median` function with `na.rm=TRUE` and a grouping variable `g` to SQL's `PERCENTILE_CONT` for the 0.5 percentile with a `PARTITION BY` clause within each group.

```R
test_translate_sql(median(x, na.rm = TRUE), vars_group = "g")
```

```SQL
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY \`x\`) OVER (PARTITION BY \`g\`)
```

--------------------------------

### Error on Overwriting Existing Column with tidyr::pivot_wider

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-pivot-wider.md

Shows an error condition when `tidyr::pivot_wider` attempts to create new columns that would overwrite existing ones, specifically highlighting duplicated names.

```R
tidyr::pivot_wider(df, names_from = key, values_from = val)
```

```Error
Error in `dbplyr_pivot_wider_spec()`:
! Names must be unique.
x These names are duplicated:
  * "a" at locations 1 and 2.
```

--------------------------------

### `head()` Before `arrange()` in `dbplyr`

Source: https://github.com/tidyverse/dbplyr/blob/main/tests/testthat/_snaps/verb-arrange.md

Shows the SQL generated when `head()` is applied before `arrange()`. `head()` creates a subquery, and `arrange()` then orders the results of that subquery.

```R
lf <- lazy_frame(a = 1:3, b = 3:1)
lf %>% head(1) %>% arrange(a)
```

```SQL
SELECT `q01`.*
FROM (
  SELECT `df`.*
  FROM `df`
  LIMIT 1
) AS `q01`
ORDER BY `a`
```