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
|
# can translate subsetting
Code
test_translate_sql(a[[x]])
Condition
Error in `a[[x]]`:
! Can only index with strings and numbers
Code
test_translate_sql(a[[TRUE]])
Condition
Error in `a[[TRUE]]`:
! Can only index with strings and numbers
# $ doesn't evaluate second argument
Code
lazy_frame(x = 1, y = 1) %>% filter(x == y$id)
Output
<SQL>
SELECT `df`.*
FROM `df`
WHERE (`x` = `y`.`id`)
---
Code
lazy_frame(x = 1) %>% filter(x == y$id)
Output
<SQL>
SELECT `df`.*
FROM `df`
WHERE (`x` = 1.0)
# useful error if $ used with inlined value
Code
lazy_frame(x = 1) %>% filter(x == y$id)
Condition
Error in `x$id`:
! $ operator is invalid for atomic vectors
# can translate case insensitive like
Code
test_translate_sql(str_like(x, "abc", ignore_case = FALSE))
Condition
Error in `str_like()`:
! Backend only supports case insensitve `str_like()`.
# default raw escapes translated correctly
Code
mf %>% filter(x == a)
Output
<SQL>
SELECT `df`.*
FROM `df`
WHERE (`x` = X'616263')
---
Code
mf %>% filter(x %in% L)
Output
<SQL>
SELECT `df`.*
FROM `df`
WHERE (`x` IN (X'616263', X'0102'))
---
Code
qry
Output
<SQL>
SELECT `df`.*
FROM `df`
WHERE (`x` IN (X'616263', X'0102'))
# DDL operations generate expected SQL
Code
sql_table_analyze(con, in_schema("schema", "tbl"))
Output
<SQL> ANALYZE `schema`.`tbl`
---
Code
sql_query_explain(con, sql("SELECT * FROM foo"))
Output
<SQL> EXPLAIN SELECT * FROM foo
---
Code
sql_query_wrap(con, ident("table"))
Output
<table_path> `table`
---
Code
sql_query_wrap(con, in_schema("schema", "tbl"))
Output
<table_path> `schema`.`tbl`
---
Code
sql_query_wrap(con, sql("SELECT * FROM foo"))
Output
<SQL> (SELECT * FROM foo) AS `q01`
---
Code
sql_table_index(con, in_schema("schema", "tbl"), c("a", "b"))
Output
<SQL> CREATE INDEX `tbl_a_b` ON `schema`.`tbl` (`a`, `b`)
---
Code
sql_table_index(con, in_schema("schema", "tbl"), "c", unique = TRUE)
Output
<SQL> CREATE UNIQUE INDEX `tbl_c` ON `schema`.`tbl` (`c`)
---
Code
sql_query_save(con, sql("SELECT * FROM foo"), in_schema("temp", "tbl"))
Output
<SQL> CREATE TEMPORARY TABLE
`temp`.`tbl` AS
SELECT * FROM foo
|
