Median translated incorrectly for SQLite

[GrayAlex49]

Using dplyr to work with data from a database, the median function does not seem to be translating correctly. Mean works as expected but median does not. I'm using a mysql database but can recreate the problem in sqlite.

mf <- memdb_frame(group = c("a", "a", "b", "b", "c"), 
                  year = c(2010, 2010, 2012, 2013, 2010), 
                  var1 = 5:1,
                  var2 = 100:104)

mf %>% 
  group_by(group, year) %>% 
  summarise_at(vars(one_of("var1", "var2")), median)


mf %>% 
  group_by(group, year) %>% 
  summarise_at(vars(one_of("var1", "var2")), mean)

This error is rather unhelpful, on my actual data I was getting something like

'AS temp, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY var1) AS var1, at line 1 [1064]

[shntnu]

The problem is that PERCENTILE_CONT is not supported in SQLite

data <- data.frame(x = c(1, 2))

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

data <- dplyr::copy_to(db, data)

dbplyr::sql_render(dplyr::summarize(data, x = mean(x)))
#> Warning: Missing values are always removed in SQL.
#> Use `mean(x, na.rm = TRUE)` to silence this warning
#> This warning is displayed only once per session.
#> <SQL> SELECT AVG(`x`) AS `x`
#> FROM `data`

dplyr::summarize(data, x = mean(x))
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.29.0 [:memory:]
#>       x
#>   <dbl>
#> 1   1.5

dbplyr::sql_render(dplyr::summarize(data, x = median(x)))
#> <SQL> SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) AS `x`
#> FROM `data`

dplyr::summarize(data, x = median(x))
#> Error: near "(": syntax error

^{Created on 2019-10-18 by the reprex package (v0.3.0)}

[shntnu]

This commit might have introduced the bug 19909fa.
This issue looks relevant #169

[GrayAlex49]

My original code and error was with MariaDB, I just didn't know how to do a reprex for that. But it looks like you are right which is why my error messages were different.

[krlmlr]

median() only works in window functions, you may need to group_by() %>% mutate() %>% distinct()

[shntnu]

median() only works in window functions, you may need to group_by() %>% mutate() %>% distinct()

@krlmlr I'm not sure that is the issue here. IIUC the problem is that PERCENTILE_CONT is not supported in SQLite. I tried the group_by / mutate approach below:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

data <- data.frame(x = c(1, 2), y = c(0, 0))

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

data <- copy_to(db, data)

dbplyr::sql_render(data %>% group_by(y) %>% mutate(x = median(x)))
#> <SQL> SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) OVER (PARTITION BY `y`) AS `x`, `y`
#> FROM `data`

data %>% group_by(y) %>% mutate(x = median(x)) 
#> Error: near "(": syntax error

DBI::dbDisconnect(db)

^{Created on 2019-11-01 by the reprex package (v0.3.0)}

[krlmlr]

Indeed:

library(tidyverse)
library(dbplyr)

data <- memdb_frame(x = c(1, 2), y = c(0, 0))
data %>% group_by(y) %>% mutate(x = MEDIAN(x))
#> # Source:   lazy query [?? x 2]
#> # Database: sqlite 3.29.0 [:memory:]
#> # Groups:   y
#>       x     y
#>   <dbl> <dbl>
#> 1   1.5     0

^{Created on 2019-11-01 by the reprex package (v0.3.0)}

[shntnu]

Oh wait, you used MEDIAN instead of median. If I make that change, summarize works as well (because it gets sql-translated to MEDIAN instead of PERCENTILE_CONT)

data <- data.frame(x = c(1, 2))

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

data <- dplyr::copy_to(db, data)

dbplyr::sql_render(dplyr::summarize(data, x = MEDIAN(x)))
#> <SQL> SELECT MEDIAN(`x`) AS `x`
#> FROM `data`

dplyr::summarize(data, x = MEDIAN(x))
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.29.0 [:memory:]
#>       x
#>   <dbl>
#> 1   1.5

dbplyr::sql_render(dplyr::summarize(data, x = median(x)))
#> <SQL> SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) AS `x`
#> FROM `data`

dplyr::summarize(data, x = median(x))
#> Error: near "(": syntax error

^{Created on 2019-11-01 by the reprex package (v0.3.0)}

[hadley]

@GrayAlex49 MariaDB does support PERCENTILE_CONT according to the documentation: https://mariadb.com/kb/en/library/percentile_cont/

MEDIAN is not built into SQLite, but it is included in the extension functions bundled by RSQLite: https://github.com/r-dbi/RSQLite/blob/master/src/vendor/sqlite3/extension-functions.c

[shntnu]

Because this comment #357 (comment) is hidden, I'm adding a new comment to make sure that context is visible to whoever addresses this issue

[hadley]

The original bug was about MySQL, but no reprex was provided. There seems to be a separate bug with SQLite where it's using PERCENTILE_CONT, but should instead be using MEDIAN.