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)

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

Example database:

CREATE TABLE TABLE1 (
    ID INT NOT NULL,
    V1 VARCHAR(45),
    PRIMARY KEY (ID)
);

INSERT INTO TABLE1 (ID, V1)
VALUES 
  (1, 'C'),
  (2, 'D');

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

INSERT INTO TABLE2 (USERID, V2)
VALUES 
  (1, 'A'),
  (3, 'B');

The following chunk leads to the error:

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

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:

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

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

<!--
Please keep the below portion in your issue. Your issue will be closed if any of the boxes is not checked (i.e., replace `[ ]` by `[x]`). In certain (rare) cases, you may be exempted if you give a brief explanation (e.g., you are only making a suggestion for improvement). Thanks!
-->

---

By filing an issue to this repo, I promise that

- [x] I have fully read the issue guide at https://yihui.org/issue/.
- [x] 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.
- [x] 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[bot]]

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.