tests/testthat/test-query-select.R

In dbplyr: A 'dplyr' Back End for Databases

test_that("select_query() print method output is as expected", {
  mf <- lazy_frame(x = 1, con = simulate_dbi()) %>%
    filter(x > 1L) %>%
    arrange(x) %>%
    head(10) %>%
    sql_build()
  expect_snapshot(mf)
})

test_that("queries generated by select() don't alias unnecessarily", {
  lf_build <- lazy_frame(x = 1, y = 1) %>%
    select(x) %>%
    sql_build()

  lf_render <- sql_render(lf_build, con = simulate_dbi())
  expect_snapshot(lf_render)
})

test_that("queries generated by select() don't alias unnecessarily", {
  expect_snapshot(error = TRUE, lazy_frame(x = 1, y = 1) %>% select())
})

# Optimisations -----------------------------------------------------------

test_that("optimisation is turned on by default", {
  lf <- lazy_frame(x = 1, y = 2) %>% arrange(x) %>% head(5)
  qry <- lf %>% sql_build()

  expect_equal(qry$from, base_query(ident("df")))
})

test_that("group by then limit is collapsed", {
  lf <- memdb_frame(x = 1:10, y = 2) %>%
    group_by(x) %>%
    summarise(y = sum(y, na.rm = TRUE)) %>%
    head(1)

  qry <- lf %>% sql_build()
  expect_equal(qry$limit, 1L)
  expect_equal(qry$group_by, sql('`x`'))

  # And check that it returns the correct value
  expect_equal(collect(lf), tibble(x = 1L, y = 2))
})

test_that("filter and rename are correctly composed", {
  lf <- memdb_frame(x = 1, y = 2) %>%
    filter(x == 1) %>%
    select(x = y)

  qry <- lf %>% sql_build()
  expect_equal(qry$select, sql(x = "`y`"))
  expect_equal(qry$where, sql('`x` = 1.0'))

  # It surprises me that this SQL works!
  expect_equal(collect(lf), tibble(x = 2))
})

test_that("trivial subqueries are collapsed", {
  lf <- memdb_frame(a = 1:3) %>%
    mutate(b = a + 1) %>%
    distinct() %>%
    arrange()

  qry <- lf %>% sql_build()
  expect_s3_class(qry$from, "base_query")
  expect_true(qry$distinct)

  # And check that it returns the correct value
  expect_equal(collect(lf), tibble(a = 1:3, b = a + 1.0))
})