Caching sql chunks using knitr/R Markdown throwing error

I'm having trouble knitting an Rmd when I set cache=TRUE for sql code chunks.

There is some discussion of a related error here and here, but those examples seem to be when output_var is set in the sql chunk and the bug was fixed a couple years ago.

The following Rmd will knit the first time, but the second time when it tries to read from the cache, I get this error:

Quitting from lines 12-13 (test-sql-cache-rmd.Rmd) 
Error in fun(environment()) : invalid first argument
Calls: <Anonymous> ... call_block -> <Anonymous> -> lazyLoad -> lazyLoadDBexec -> fun
Execution halted

When cache=FALSE, the error goes away. Any ideas or is this a knitr bug?

---
title: "sql chunk"
output: html_document
---

```{r setup, include=FALSE}
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
knitr::opts_chunk$set(echo = TRUE, connection = "con")
```

```{sql select_1, cache=TRUE}
SELECT 1
```
1 Like

No clues on this unfortunately, but I was able to reproduce the bug :+1:. It also went away when I specified an output.var.

I came across this issue because I was wondering myself if the caching was really working for SQL chunks. I see that a cache is built when I add cache = TRUE but my heavy queries still take forever. Not sure how to test it more rigorously though. I'd appreciate any guidance on making a proper reprex!

Glad to hear you can reproduce and it's not just on my system! Which reminds me that I posted this as an issue on knitr GitHub. We'll see if anyone over there has any ideas.

As for making a reprex, I think if possible to illustrate the issue, setting up an in memory database as I did above is the easiest way to make sure that others can run the code if they don't have access to a DB.