Use backticks or brackets for quoting identifiers

[krlmlr]

Not double quotes.

con <- DBI::dbConnect(RSQLite::SQLite())
DBI::dbGetQuery(con, 'SELECT "a" FROM (SELECT 1 AS "b")')
#>   "a"
#> 1   a
DBI::dbGetQuery(con, 'SELECT `a` FROM (SELECT 1 AS `b`)')
#> Error in rsqlite_send_query(conn@ptr, statement): no such column: a
DBI::dbGetQuery(con, 'SELECT [a] FROM (SELECT 1 AS [b])')
#> Error in rsqlite_send_query(conn@ptr, statement): no such column: a

Related: tidyverse/dplyr#1426

CC @craigcitro.

[hadley]

I think this is due to

For resilience when confronted with historical SQL statements, SQLite will sometimes bend the quoting rules above:

• If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.
• If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

https://sqlite.org/lang_keywords.html

I'd have slight preference for using [] since that would be the same as bigquery.

[krlmlr]

Backticks are slightly better because they can be escaped by repetition; I haven't found a way to escape the closing bracket ].

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.