Consistent chunk options for SQL chunks

[hardin47]

Description

SQL in Positron

I understand that in Positron, there is no way to interactively run SQL chunks (although I would love that feature to be added!). When rendering a Quarto doc, the SQL renders via an established connection using an option indicated with --|.

```{sql}
--| connection: con_flights

SELECT * FROM flights
LIMIT 0, 10;
```

SQL in RStudio

In RStudio (which uses the knitr engine) it is possible to run SQL interactively AND by rendering. However, I have found that the interactive chunk requires a #| option tag and the render chunk requires a --| option tag. For teaching purposes, the disconnect of two different types of option tags is a cognitive load that I cannot require of my students. Do you know if there is a way to use the same option tag across both render and interactive in RStudio?

I've attached a video (I don't know if it will go through) so that you can more easily see what I'm saying.

Also, the different chunk options for render vs interactive is brand new! Last fall, I was using #| for both in all of my teaching materials.

video1911188719.mp4

[cscheid]

I think this is an issue in RStudio; Quarto doesn't really control what RStudio does when someone clicks on a button in an RStudio editor.

[mcanouil]

Rstudio still reads #| when working interactively, likely for backward compatibility.
Both syntaxes R version and SQL version works in knitr/rmardown.

Only Quarto CLI enforces the syntax using the correct/proper comment symbol which it warns about, while knitr/rmardown just renders both without any messaging, again likely backward compatibility.

Error: The chunk options should start with '--| ' instead of '#| '.

So, to me, If I had to guess your issue, it is that you migrated from rmarkdown to Quarto without migrating the option syntax.

[hardin47]

I'm not sure what this mean "migrated from rmarkdown to Quarto without migrating the option syntax" or how to fix it. Any suggestions?

To recap:

• all sql chunks should use --| instead of #|.
• the reason why my old chunks use #| is potentially irrelevant (?)
• the reason why my Quarto doc has a different option for render and interactive is potentially due to what version of RStudio I am using (?)
• SQL does not run interactively in Quarto + Positron, but that may happen someday. It is seemingly not a high priority feature.

Is that all correct?

Thanks!

[mcanouil]

You said the R syntax worked and now it does not.
The only way for this to happen is to have change from rmd files to qmd files thus migrated from RMarkdown to Quarto.
The other possibility is that a fairly old version of Quarto CLI did not use the SQL comment syntax (seems very unlikely).

Anyhow, beside interactive SQL code cell not supported in Positron/VSCode, all works everywhere with --|.

# is legacy from knitr which then was also implemented in RStudio up until Quarto (2022).
Then more consistent comment + pipe appeared with Quarto, which led to RStudio needed to support it.
I can't give you the exact timeline for all these changes, but the support of the per-language syntax is in RStudio for more than 3 years now.

• Interactive mode with quarto documents in RStudio does not like --| code cell option, requires #|.  #5864

[mcanouil]

So I've found the following which suggests this was enforced in Quarto CLI 1.7.21 at least as a test it does work properly.
But this looks mostly as a maintenance change with no user facing consequences.

• a8525d4

[juliasilge]

SQL does not run interactively in Quarto + Positron, but that may happen someday. It is seemingly not a high priority feature.

I'll add that we are currently actively working on SQL support in Positron. We don't expect it to be ready for students this fall, but it's one of our top line priorities for next milestone (as well as following):

posit-dev/positron#14458

[hardin47]

I did not / am not copying Rmd files to qmd files. All of the files I'm running are natively Quarto. (Note: I was previously using Quarto 1.6 which might have been part of the problem. However, I am now running Quarto 1.9.38, and the problem persists.)

I know that you say that --| is the correct syntax, but it does not work for me.

Interactively, when I use --| I get the following:

When I use #| I get the following:

[mcanouil]

our messages crossed.

[mcanouil]

I have issues installing RMariaDB, so I can't right now.

Could you try writing -- | connection: ... with the trailing space after the two dashes? I expect you'll get a different error, i.e.,

Error in .rs.runSqlForDataCapture("-- | connection: db\nSELECT * FROM trials",  : 
  The 'connection' option (DBI connection) is required for sql chunks.

https://mariadb.com/docs/server/reference/sql-statements/comment-syntax

From a double dash (--) to the end of a line. The space after the two dashes is required (as in MySQL).

if this is the case, then the issue is basically everywhere: knitr, RStudio, and Quarto CLI.

[hardin47]

yes, when I include the space -- | I get the following error:

Error in .rs.runSqlForDataCapture("-- | connection: con_flights\n\nSELECT * FROM flights\nLIMIT 0, 10;",  : 
  The 'connection' option (DBI connection) is required for sql chunks.
Failed to execute SQL chunk

[mcanouil]

Basically, if you write any comments without the space after the two dashes you are going to get the first error you've seen.
This is because RStudio actually sends the option (which is a comment) to the SQL server, but --| is invalid comment while -- | is valid for MariaDB.

To me the simplest fix would be for RStudio to not send the comment options at all, rather than having everything supports the space in between version. (Cc @juliasilge if you are not aware of this for the Positron implementation, that's something to consider)

---

DeepWiki:

• https://deepwiki.com/search/are-sql-code-cellchunk-options_7d8cf9af-7afe-4fd2-8022-288bc87601b3?mode=fast

(Contains some wrong conclusions, but the code is real)

[cderv]

Thanks for the investigation. Great findings! We learned a lot here.

I do think we have something to reflect on quarto side too.

Only Quarto CLI enforces the syntax using the correct/proper comment symbol which it warns about, while knitr/rmardown just renders both without any messaging, again likely backward compatibility.

Quarto enforce strict mode. So in quarto document knitr will error, and not in rmarkdown which is not enforcing strict mode. Example of this at: rstudio/rstudio#18093 (comment)

This means if we see sql engine, we do ask for --| but I understand this is not valid syntax in MariaDB and MySQL. However, #| is supported for those somehow.

The hash pipe syntax logic is supposed to be '' + ''. We did that so that it would not be a problem to keep those comments with YAML options in cells and it seems this is does not work in that case.
Sure, this is better to strip them out, but should not be mandatory in principle.

Though we don't have a way to know because there is no `{mariadb} cell. It is only sql. But I wonder if we should be less strict in this case. Not that straightforward considering interaction between knitr and quarto on this.

From a quick search (1, 2) I understand that MySQL/MariaDB are the outliers, adding the space requirement specifically to avoid ambiguity with some expressions they support.

#| is our fallback for unknown language currently.

[mcanouil]

@hardin47 I've opened an issue on RStudio side:

• SQL chunk option lines (--|) sent to DB server, breaking MySQL/MariaDB interactive execution rstudio/rstudio#18093