---
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