1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
# custom window functions translated correctly
Code
(expect_error(test_translate_sql(quantile(x, 0.3, na.rm = TRUE), window = TRUE))
)
Output
<error/rlang_error>
Error in `quantile()`:
! Translation of `quantile()` in `mutate()` is not supported for PostgreSQL.
i Use a combination of `summarise()` and `left_join()` instead:
`df %>% left_join(summarise(<col> = quantile(x, 0.3, na.rm = TRUE)))`.
Code
(expect_error(test_translate_sql(median(x, na.rm = TRUE), window = TRUE)))
Output
<error/rlang_error>
Error in `median()`:
! Translation of `median()` in `mutate()` is not supported for PostgreSQL.
i Use a combination of `summarise()` and `left_join()` instead:
`df %>% left_join(summarise(<col> = median(x, na.rm = TRUE)))`.
# custom SQL translation
Code
left_join(lf, lf, by = "x", na_matches = "na")
Output
<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`)
---
Code
copy_inline(con, tibble(x = integer(), y = character())) %>% remote_query()
Output
<SQL> SELECT CAST(NULL AS INTEGER) AS `x`, CAST(NULL AS TEXT) AS `y`
WHERE (0 = 1)
---
Code
copy_inline(con, tibble(x = 1:2, y = letters[1:2])) %>% remote_query()
Output
<SQL> SELECT CAST(`x` AS INTEGER) AS `x`, CAST(`y` AS TEXT) AS `y`
FROM ( VALUES (1, 'a'), (2, 'b')) AS drvd(`x`, `y`)
# `sql_query_insert()` works
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 = "error",
returning_cols = c("a", b2 = "b")))
Condition
Error in `sql_query_insert()`:
! `conflict = "error"` isn't supported on database backends.
i It must be "ignore" instead.
---
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`)
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`
# `sql_query_upsert()` with method = 'on_conflict' is correct
Code
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")
Output
<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`
# can explain
Code
db %>% mutate(y = x + 1) %>% explain()
Output
<SQL>
SELECT "test".*, "x" + 1.0 AS "y"
FROM "test"
<PLAN>
QUERY PLAN
1 Seq Scan on test (cost=0.00..1.04 rows=3 width=36)
---
Code
db %>% mutate(y = x + 1) %>% explain(format = "json")
Output
<SQL>
SELECT "test".*, "x" + 1.0 AS "y"
FROM "test"
<PLAN>
QUERY PLAN
1 [\n {\n "Plan": {\n "Node Type": "Seq Scan",\n "Parallel Aware": false,\n "Async Capable": false,\n "Relation Name": "test",\n "Alias": "test",\n "Startup Cost": 0.00,\n "Total Cost": 1.04,\n "Plan Rows": 3,\n "Plan Width": 36\n }\n }\n]
# can insert with returning
Code
rows_insert(x, y, by = c("a", "b"), in_place = TRUE, conflict = "ignore",
returning = everything(), method = "on_conflict")
Condition
Error in `rows_insert()`:
! Can't modify database table "df_x".
i Using 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"
Caused by error:
! dummy DBI error
# can upsert with returning
Code
rows_upsert(x, y, by = c("a", "b"), in_place = TRUE, returning = everything(),
method = "on_conflict")
Condition
Error in `rows_upsert()`:
! Can't modify database table "df_x".
i Using 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"
Caused by error:
! dummy DBI error
|
