Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

pmin() / pmax() aren't translated to SQL MIN() / MAX() #1711

Closed
eamoncaddigan opened this issue Mar 14, 2016 · 3 comments
Closed

pmin() / pmax() aren't translated to SQL MIN() / MAX() #1711

eamoncaddigan opened this issue Mar 14, 2016 · 3 comments
Labels
Milestone

Comments

@eamoncaddigan
Copy link

@eamoncaddigan eamoncaddigan commented Mar 14, 2016

I consider this a bug, and it feels like it should be an easy fix. In the meantime, I'd also be grateful if anybody could suggest a workaround: is it possible to mix SQL statements with dplyr verbs? I would hate to do my entire analysis in SQL. D:

library(dplyr)
set.seed(217)

# Put some fake data into a temporary SQLite DB
testDF <- data_frame(col_1 = sample.int(99, 8, replace = TRUE),
                     col_2 = sample.int(99, 8, replace = TRUE))
db <- src_sqlite(tempfile(), create = TRUE)
copy_to(db, testDF, "test", temporary = FALSE)
#>From: test [8 x 2]
#>
#>  col_1 col_2
#>  (int) (int)
#>1    42    74
#>2    15     5
#>3    26    23
#>4    83    98
#>5     6    62
#>6    66    18
#>7    69    27
#>8    80    14

# This would work if pmin() was translated
test_dplyr <- tbl(db, "test") %>%
  mutate(col_min = pmin(col_1, col_2))
print(test_dplyr)
#>Error in sqliteSendQuery(con, statement, bind.data) : 
#>  error in statement: no such function: PMIN

# This should work if min() was translated without checks
test_dplyr <- tbl(db, "test") %>%
  mutate(col_min = min(col_1, col_2))
#>Error: Invalid number of args to SQL MIN. Expecting 1

# Proof that one of these should work
test_sql <- dbGetQuery(db$con, 
                       "SELECT col_1, col_2, MIN(col_1, col_2) AS col_min
                        FROM test")
print(test_sql)
#>  col_1 col_2 col_min
#>1    42    74      42
#>2    15     5       5
#>3    26    23      23
#>4    83    98      83
#>5     6    62       6
#>6    66    18      18
#>7    69    27      27
#>8    80    14      14
@eamoncaddigan
Copy link
Author

@eamoncaddigan eamoncaddigan commented Mar 14, 2016

Okay, I'm sorry. Checking out translate-sql.r, I now see that the preferred way to do this is:

# Works
test_dplyr <- tbl(db, "test") %>%
  mutate(col_min = sql("MIN(col_1, col_2)"))
print(test_dplyr)
#>From: test [8 x 3]
#>
#>  col_1 col_2 col_min
#>  (int) (int)   (int)
#>1    42    74      42
#>2    15     5       5
#>3    26    23      23
#>4    83    98      83
#>5     6    62       6
#>6    66    18      18
#>7    69    27      27
#>8    80    14      14

@hadley
Copy link
Member

@hadley hadley commented Mar 14, 2016

Let me reopen so I can do it automatically for you ;)

@hadley hadley reopened this Mar 14, 2016
@hadley hadley added this to the 0.5 milestone Mar 14, 2016
@hadley hadley closed this in 7219eaa Mar 14, 2016
@eamoncaddigan
Copy link
Author

@eamoncaddigan eamoncaddigan commented Mar 16, 2016

Wow, thanks! 😃

@lock lock bot locked as resolved and limited conversation to collaborators Jun 9, 2018
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants