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!

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

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.