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
|
test_that("custom scalar translated correctly", {
local_con(simulate_teradata())
expect_equal(translate_sql(x != y), sql("`x` <> `y`"))
expect_equal(translate_sql(as.numeric(x)), sql("CAST(`x` AS DECIMAL(12, 9))"))
expect_equal(translate_sql(as.numeric(x, 8)), sql("CAST(`x` AS DECIMAL(12, 8))"))
expect_equal(translate_sql(as.double(x)), sql("CAST(`x` AS NUMERIC)"))
expect_equal(translate_sql(as.character(x)), sql("CAST(`x` AS VARCHAR(MAX))"))
expect_equal(translate_sql(log(x)), sql("LN(`x`)"))
expect_equal(translate_sql(cot(x)), sql("1 / TAN(`x`)"))
expect_equal(translate_sql(nchar(x)), sql("CHARACTER_LENGTH(`x`)"))
expect_equal(translate_sql(ceil(x)), sql("CEILING(`x`)"))
expect_equal(translate_sql(ceiling(x)), sql("CEILING(`x`)"))
expect_equal(translate_sql(atan2(x, y)), sql("ATAN2(`y`, `x`)"))
expect_equal(translate_sql(substr(x, 1, 2)), sql("SUBSTR(`x`, 1.0, 2.0)"))
expect_equal(translate_sql(startsWith(x,"A")), sql("CAST(CASE WHEN INSTR(`x`, 'A') = 1 THEN 1 ELSE 0 END AS INTEGER)"))
expect_equal(translate_sql(paste0(x,y)), sql("`x` || `y`"))
expect_equal(translate_sql(paste(x,y)), sql("`x` || ' ' || `y`"))
expect_equal(translate_sql(as.Date("2020-01-01")), sql("DATE '2020-01-01'"))
expect_equal(translate_sql(week(as.Date("2020-01-01"))), sql("WEEKNUMBER_OF_YEAR(DATE '2020-01-01', 'iso')"))
expect_equal(translate_sql(quarter(as.Date("2020-01-01"))), sql("to_char(DATE '2020-01-01','q')"))
})
test_that("custom bitwise operations translated correctly", {
local_con(simulate_teradata())
expect_equal(translate_sql(bitwNot(x)), sql("BITNOT(`x`)"))
expect_equal(translate_sql(bitwAnd(x, 128L)), sql("BITAND(`x`, 128)"))
expect_equal(translate_sql(bitwOr(x, 128L)), sql("BITOR(`x`, 128)"))
expect_equal(translate_sql(bitwXor(x, 128L)), sql("BITXOR(`x`, 128)"))
expect_equal(translate_sql(bitwShiftL(x, 2L)), sql("SHIFTLEFT(`x`, 2)"))
expect_equal(translate_sql(bitwShiftR(x, 2L)), sql("SHIFTRIGHT(`x`, 2)"))
})
test_that("custom aggregators translated correctly", {
local_con(simulate_teradata())
expect_equal(translate_sql(var(x, na.rm = TRUE), window = FALSE), sql("VAR_SAMP(`x`)"))
expect_equal(translate_sql(cumsum(x, order_by = c(A,B))), sql("SUM(`x`) OVER (ORDER BY `A`, `B` ROWS UNBOUNDED PRECEDING)"))
})
test_that("custom window functions translated correctly", {
local_con(simulate_teradata())
expect_equal(translate_sql(var(x, na.rm = TRUE)), sql("VAR_SAMP(`x`) OVER ()"))
expect_equal(translate_sql(cumsum(x, order_by = c(A,B))), sql("SUM(`x`) OVER (ORDER BY `A`, `B` ROWS UNBOUNDED PRECEDING)"))
})
test_that("generates custom sql", {
con <- simulate_teradata()
expect_snapshot(sql_table_analyze(con, in_schema("schema", "tbl")))
})
# verb translation --------------------------------------------------------
test_that("head translated to TOP", {
mf <- lazy_frame(x = 1, con = simulate_teradata())
expect_snapshot(mf %>% head() %>% sql_render())
})
test_that("lead, lag work", {
mf <- lazy_frame(x = c(1:5), y = c(rep("A", 5)), con = simulate_teradata())
expect_snapshot(mf %>% group_by(y) %>% mutate(val2 = lead(x, order_by = x)) %>% sql_render())
expect_snapshot(mf %>% group_by(y) %>% mutate(val2 = lag(x, order_by = x)) %>% sql_render())
})
test_that("weighted.mean", {
mf <- lazy_frame(x = c(1:5), y = c(6:10), con = simulate_teradata())
expect_snapshot(mf %>% summarise(wt_mean = weighted.mean(x, y)))
})
test_that("row_number with and without group_by", {
mf <- lazy_frame(x = c(1:5), y = c(rep("A", 5)), con = simulate_teradata())
expect_snapshot(mf %>% mutate(rown = row_number()))
expect_snapshot(mf %>% group_by(y) %>% mutate(rown = row_number()))
})
test_that("queries do not use *", {
lf <- lazy_frame(x = 1L, con = simulate_teradata())
expect_equal(
lf %>% mutate(y = x) %>% remote_query(),
sql("SELECT `x`, `x` AS `y`\nFROM `df`")
)
})
|
