Error rendering SQL engine

[mariusbommert]

Rmarkdown-Code to reproduce the error:

---
title: "Error rendering SQL engine"
output: html_document
date: "2023-01-01"
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

```{r}
library(DBI)
con = dbConnect(RSQLite::SQLite(), ":memory:")
knitr::opts_chunk$set(connection = "con")
```

Example database:
```{sql}
CREATE TABLE TABLE1 (
    ID INT NOT NULL,
    V1 VARCHAR(45),
    PRIMARY KEY (ID)
);
```

```{sql}
INSERT INTO TABLE1 (ID, V1)
VALUES 
  (1, 'C'),
  (2, 'D');
```

```{sql}
CREATE TABLE TABLE2 (
    USERID INT NOT NULL,
    V2 VARCHAR(45) NOT NULL,
    PRIMARY KEY (V2)
);
```

```{sql}
INSERT INTO TABLE2 (USERID, V2)
VALUES 
  (1, 'A'),
  (3, 'B');
```

The following chunk leads to the error:
```{sql}
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON ID = USERID; 
```

I think I have found out where the error comes from:


Right join with saving output as test (no error when not rendering output):
```{sql, output.var = "test"}
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON ID = USERID;
```

Evaluating code in line 664 in sql_engine.R leads to if(NA) which throws the error:
```{r}
test
first_column = test[[1]]
# R markdown: line 664 in engine.R (https://github.com/yihui/knitr/blob/83fb5084daa1161d3ee2f000b637e48bdcf64617/R/engine.R):
is.numeric(first_column) && length(first_column) > 1 && all(diff(first_column) == 1)
# evaluates to NA and if(NA) leads to error
```

```{r}
dbDisconnect(con)
```

xfun::session_info('knitr')
## R version 4.2.2 (2022-10-31 ucrt)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 19045)
## 
## Locale:
##   LC_COLLATE=German_Germany.utf8  LC_CTYPE=German_Germany.utf8   
##   LC_MONETARY=German_Germany.utf8 LC_NUMERIC=C                   
##   LC_TIME=German_Germany.utf8    
## 
## Package version:
##   evaluate_0.19   graphics_4.2.2  grDevices_4.2.2 highr_0.10     
##   knitr_1.41.7    methods_4.2.2   stats_4.2.2     tools_4.2.2    
##   utils_4.2.2     xfun_0.36       yaml_2.3.6

---

By filing an issue to this repo, I promise that

• I have fully read the issue guide at https://yihui.org/issue/.
• I have provided the necessary information about my issue.
  • If I'm asking a question, I have already asked it on Stack Overflow or RStudio Community, waited for at least 24 hours, and included a link to my question there.
  • If I'm filing a bug report, I have included a minimal, self-contained, and reproducible example, and have also included xfun::session_info('knitr'). I have upgraded all my packages to their latest versions (e.g., R, RStudio, and R packages), and also tried the development version: remotes::install_github('yihui/knitr').
  • If I have posted the same issue elsewhere, I have also mentioned it in this issue.
• I have learned the Github Markdown syntax, and formatted my issue correctly.

I understand that my issue may be closed if I don't fulfill my promises.

[cderv]

Thanks @mariusbommert !

I pushed a fixed to the issue. The NA in first column was indeed the issue. Thank you

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue by following the issue guide (https://yihui.org/issue/), and link to this old issue if necessary.