I use MariaDB 10.3.11 and since 10.2 it's supported windowing functions. MySQL has also supported them since 8.0.2 I believe. The additions to MySQL have been more recent, but it's still been a feature for about 18 months.
I came across this issue: https://github.com/r-dbi/RMariaDB/issues/89, which is why I'm posting here now. I couldn't find a previous discussion about this on this website.
MySQL/MariaDB's support for window functions is inaccurately mentioned in the documentation.
Sorry, I don't have a reprex, but this isn't a bug, it's a feature being intentionally turned off, and I'm not sure the simulate_mysql() would simulate a current MySQL or MariaDB if it's assumed those don't have features they have. Sending a windowing query through the MariaDB DBI works:
suppressPackageStartupMessages({
# (it's not a reprex but I don't want to paste these package messages,
# rather just the output I get)
library(tidyverse)
library(DBI)
library(dbplyr)
})
con <- dbConnect(RMariaDB::MariaDB(), dbname = "mydb", group = "mygroup")
sql <- glue::glue("
SELECT `id_count`, RANK()
OVER (ORDER BY `id_count` DESC) `rank`
FROM (
SELECT `id`, count(`id`) `id_count` FROM `my_table`
WHERE `version_date` > '2017-12-31' GROUP BY `id`
) `t`
ORDER BY `rank` LIMIT 10;
")
dbGetQuery(con, sql)
## id_count rank
##1 13 1
##2 13 1
##3 13 1
##4 13 1
##5 13 1
##6 13 1
##7 13 1
##8 13 1
##9 13 1
##10 13 1
but I can't do it through dbplyr:
tbl(con, 'my_table') %>%
filter(version_date > '2017-12-31') %>%
group_by(id) %>%
summarize(id_count=n()) %>%
arrange(desc(id_count)) %>%
mutate(rank=min_rank(desc(id_count))) %>%
select(id, rank)
##Error: Window function `rank()` is not supported by this database
I'm really a lot better with dplyr than I am with SQL. I use collect() to perform these kind of operations when I need to, as opposed to figuring out the SQL query and using dbGetQuery().
Are there plans to accommodate MariaDB and MySQL with windowing-query-generating code in dbplyr? Is the specific window query syntax much different than Postgres?
