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
|
test_that("defaults to postgres translations", {
local_con(simulate_redshift())
expect_equal(translate_sql(log10(x)), sql("LOG(`x`)"))
})
test_that("string translations", {
local_con(simulate_redshift())
expect_error(translate_sql(str_replace("xx", ".", "a")), "not available")
expect_equal(translate_sql(str_replace_all("xx", ".", "a")), sql("REGEXP_REPLACE('xx', '.', 'a')"))
expect_equal(translate_sql(substr(x, 2, 2)), sql("SUBSTRING(`x`, 2, 1)"))
expect_equal(translate_sql(str_sub(x, 2, -2)), sql("SUBSTRING(`x`, 2, LEN(`x`) - 2)"))
expect_equal(translate_sql(paste("x", "y")), sql("'x' || ' ' || 'y'"))
expect_equal(translate_sql(paste0("x", "y")), sql("'x' || 'y'"))
expect_equal(translate_sql(str_c("x", "y")), sql("'x' || 'y'"))
})
test_that("numeric translations", {
local_con(simulate_redshift())
expect_equal(translate_sql(as.numeric(x)), sql("CAST(`x` AS FLOAT)"))
expect_equal(translate_sql(as.double(x)), sql("CAST(`x` AS FLOAT)"))
expect_equal(translate_sql(round(1.234, 1)), sql("ROUND((1.234) :: float, 1)"))
})
test_that("aggregate functions", {
local_con(simulate_redshift())
expect_equal(translate_sql(str_flatten(x, y), window = FALSE), sql("LISTAGG(`x`, `y`)"))
expect_equal(translate_sql(str_flatten(x, y), window = TRUE), sql("LISTAGG(`x`, `y`) OVER ()"))
expect_equal(translate_sql(order_by(z, str_flatten(x, y))), sql("LISTAGG(`x`, `y`) WITHIN GROUP (ORDER BY `z`) OVER ()"))
})
test_that("lag and lead translation", {
local_con(simulate_redshift())
expect_equal(translate_sql(lead(x)), sql("LEAD(`x`, 1) OVER ()"))
expect_equal(translate_sql(lag(x)), sql("LAG(`x`, 1) OVER ()"))
expect_error(translate_sql(lead(x, default = y)), "unused argument")
expect_error(translate_sql(lag(x, default = y)), "unused argument")
})
test_that("copy_inline uses UNION ALL", {
con <- simulate_redshift()
y <- tibble::tibble(id = 1L, arr = "{1,2,3}")
types <- c(id = "bigint", arr = "integer[]")
expect_snapshot({
copy_inline(con, y %>% slice(0)) %>% remote_query()
copy_inline(con, y) %>% remote_query()
# with `types`
copy_inline(con, y %>% slice(0), types = types) %>% remote_query()
copy_inline(con, y, types = types) %>% remote_query()
})
})
|
