tally() returns incorrect result on database table with n variable

[JohnMount]

dbplyr issue: dplyr::tally() results on database different than on data.frame. This is a very low priority for me as I do not use tally().

suppressPackageStartupMessages(library("dplyr"))
packageVersion("dplyr")
#> [1] '0.7.2.9000'
library("sparklyr")
packageVersion("sparklyr")
#> [1] '0.6.2'
packageVersion("dbplyr")
#> [1] '1.1.0.9000'
packageVersion("DBI")
#> [1] '0.7'

db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

dLocal <- data.frame(n = 1:3)
print(dLocal)
#>   n
#> 1 1
#> 2 2
#> 3 3

dRemote <- dplyr::copy_to(db, dLocal)
print(dRemote)
#> # Source:   table<dLocal> [?? x 1]
#> # Database: sqlite 3.19.3 [:memory:]
#>       n
#>   <int>
#> 1     1
#> 2     2
#> 3     3


tally(dLocal)
#> Using `n` as weighting variable
#>   nn
#> 1  6

tally(dRemote)
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.19.3 [:memory:]
#>      nn
#>   <int>
#> 1     3

DBI::dbDisconnect(db)

[hadley]

Minimal reprex

library(dplyr, warn.conflicts = FALSE)

db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

dLocal <- data.frame(n = 1:3)
tally(dLocal)
#> Using `n` as weighting variable
#>   nn
#> 1  6

dRemote <- dplyr::copy_to(db, dLocal)
tally(dRemote)
#> # Source: lazy query [?? x 1]
#> # Database: sqlite 3.19.3 [:memory:]
#>      nn
#>   <int>
#> 1     3

Note the absence of "Using n as weighting variable`

[hadley]

(probably due to use of names() instead of tbl_vars() on line 64)

[krlmlr]

There's another inconsistency between tally() and count(), which is been present in v0.5.0 already (tested with #3412):

library(tidyverse)
tibble(a = 1:3, n = 4:6) %>% group_by(a) %>% tally()
#> Using `n` as weighting variable
#> # A tibble: 3 x 2
#>       a    nn
#>   <int> <int>
#> 1     1     4
#> 2     2     5
#> 3     3     6
tibble(a = 1:3, n = 4:6) %>% count(a)
#> # A tibble: 3 x 2
#>       a    nn
#>   <int> <int>
#> 1     1     1
#> 2     2     1
#> 3     3     1

Created on 2018-03-13 by the reprex package (v0.2.0).

I propose to stop guessing the weighting variable in tally() altogether, partly because it doesn't work as expected if there's an nn variable but not an n variable. First we change the message, then we warn if there's an n variable in the data but no wt argument is used, then we stop caring about it.

[hadley]

Eliminating guessing is not acceptable to me because it's important that tally(tally(x)) continue to work and return the correct answer (i.e. the total count)

[krlmlr]

We agreed to not support guessing in tally(tally(tally(x))), and tally(count(x)) currently is different from tally(tally(x)) with respect to guessing. How can we make this a bit more consistent?

[romainfrancois]

Fixed in #3525. Just need to move this test to dbplyr :

test_that("tally works on tbl_sql (#3075)", {
  db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

  dLocal <- data.frame(n = 1:3)
  expect_message(tally_local <- tally(dLocal), "Using `n` as weighting variable")

  dRemote <- dplyr::copy_to(db, dLocal)
  expect_message(tally_remote <- tally(dRemote), "Using `n` as weighting variable" )
  expect_equal(pull(tally_remote, nn), 6)
})

[lock]

This old issue has been automatically locked. If you believe you have found a related problem, please file a new issue (with reprex) and link to this issue. https://reprex.tidyverse.org/