SQL chunks do not respect option `max.print` when it is greater than its global value

[randy3k]

System details

RStudio Edition : Desktop
RStudio Version : 1.4
OS Version      : macOS 11.1
R Version       : R 4.0.3

Steps to reproduce the problem

Open a Rmarkdown file. Create a SQL connection to db. Run the following chunks.

```{r}
options(max.print = 100)

SELECT * FROM "table";

PS: The table `table` has more than 100 rows, say 200 rows.

### Describe the problem in detail

The SQL chunk outputs only 100 rows instead of 200 rows. After a closer inspection, I notice that
`.rs.runSqlForDataCapture` does respect the chunk option `max.print` and capture all the rows in a file. However, [`.rs.readDataCapture`](https://github.com/rstudio/rstudio/blob/ddcd7191ec89c4da00e77afae7e9f27e61e87c36/src/cpp/session/modules/NotebookData.R#L252) doesn't respect the chuck option `max.print.`

### Describe the behavior you expected

I expect that the paged table would show all rows instead 100 rows.

<!-- 
Please keep the below portion in your issue, and check `[x]` the applicable boxes.
-->

- [x] I have read the guide for [submitting good bug reports](https://github.com/rstudio/rstudio/wiki/Writing-Good-Bug-Reports).
- [x] I have installed the latest version of RStudio, and confirmed that the issue still persists.
- [x] If I am reporting a RStudio crash, I have included a [diagnostics report](https://support.rstudio.com/hc/en-us/articles/200321257-Running-a-Diagnostics-Report).
- [x] I have done my best to include a minimal, self-contained set of instructions for consistently reproducing the issue.

[ronblum]

@randy3k Thank you for raising this! Is the issue perhaps setting max.print = -1 for which -1 isn't a valid value? I tried this out with the code below, setting max.print as a chunk option both with a positive number and -1. The value was respected with the positive number. For what it's worth, according to the documentation of the options package, the default value for max.print is 99999, a stand in for "print everything."

This is the preview of the notebook code below, after all chunks are run: issue_8778.nb.html.zip Note in the output that options(max.print = -1) fails with this message:

Error in options(max.print = -1) : invalid value for 'max.print'

---

Test code:

---
title: "Issue 8778"
author: "Ron Blum"
date: "1/22/2021"
output:
  html_notebook: default
  pdf_document: default
---

```{r}
library(DBI)
conn = dbConnect(RSQLite::SQLite())
dbWriteTable(conn, "iris", iris)

options(max.print = 17)
getOption("max.print")

This only shows as many records as in the global max.print

SELECT * from iris

A positive value for the chunk's max.print option is respected

SELECT * from iris

A negative value for the chunk's max.print option is ignored

SELECT * from iris

Setting max.print to a negative value isn't allowed

options(max.print = -1)

getOption("max.print")

dbDisconnect(conn)

[randy3k]

Actually if you swap 17 and 5. You will see that the new value of 17 is not respected.

[randy3k]

Try the following variation.

---
title: "Issue 8778"
author: "Ron Blum"
date: "1/22/2021"
output:
  html_notebook: default
  pdf_document: default
---

```{r}
library(DBI)
conn = dbConnect(RSQLite::SQLite())
dbWriteTable(conn, "iris", iris)

options(max.print = 5)
getOption("max.print")

This only shows as many records as in the global max.print

SELECT * from iris

A max.print value which is greater than the global max.print is ignored.

SELECT * from iris

A negative value for the chunk's max.print option is ignored

SELECT * from iris

Setting max.print to a negative value isn't allowed

options(max.print = -1)

getOption("max.print")

dbDisconnect(conn)

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs, per https://github.com/rstudio/rstudio/wiki/Issue-Grooming. Thank you for your contributions.

[randy3k]

I believe that the bug is still not fixed.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs, per https://github.com/rstudio/rstudio/wiki/Issue-Grooming. Thank you for your contributions.

[randy3k]

I am pretty sure that it has not been fixed.