dbRemoveTable() fails on SQLite with 'near "TRUNCATE": syntax error' and leaves the table in place
Nobody has claimed this yet.
Assessment
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Newbie friendliness
- 70/100
Research direction
Start from the dbRemoveTable() method in the DBI connection class (see R/Connection.R) and compare it with dropEmulatedTempTables() in R/Sql.R, which already runs SqlRender::translate() before executing the same TRUNCATE TABLE ...; DROP TABLE ...; statements. Apply the same translation there, and handle the # temp-table prefix by stripping it or mapping it to a temp table name. Run the reproduction against SQLite (and DuckDB) and confirm dbRemoveTable() returns TRUE and getTableNames() no longer lists the table.
Written by the indexing model from the issue text.
Description
Describe the bug
DBI::dbRemoveTable() on a DatabaseConnector SQLite connection fails and does not drop the table. The method sends a hard-coded TRUNCATE TABLE statement to the database without translating it, and SQLite has no TRUNCATE TABLE statement. Since the TRUNCATE runs before the DROP TABLE, the error aborts the call and the table remains.
The same happens when passing a temp table name (e.g. dbRemoveTable(connection, "#temp"), as used in extras/TestCode.R): the # prefix is sent to the database verbatim, which SQLite and DuckDB reject with a parser error.
In DatabaseConnector 6.x dbRemoveTable() translated its statements, so TRUNCATE TABLE became DELETE FROM on SQLite. Since the 7.0 DBI refactoring (commit 5969f2f "Removing all translation from DBI functions") the method sends the raw statements. dropEmulatedTempTables() in R/Sql.R runs the same TRUNCATE TABLE ...; DROP TABLE ...; pattern but translates it with SqlRender::translate() first, so it keeps working on SQLite.
To Reproduce
library(DatabaseConnector)
connection <- connect(dbms = "sqlite", server = tempfile(fileext = ".sqlite"))
insertTable(connection, databaseSchema = "main", tableName = "my_table", data = data.frame(x = 1:3))
DBI::dbRemoveTable(connection, "my_table", databaseSchema = "main")
#> Error: near "TRUNCATE": syntax error
getTableNames(connection, "main")
#> [1] "my_table"
disconnect(connection)
The same call on a DuckDB connection succeeds because DuckDB accepts TRUNCATE TABLE.
Expected behavior
dbRemoveTable() returns TRUE and the table no longer exists, on every platform DatabaseConnector supports (SQLite included).
Environment
- DatabaseConnector 8.0.0 (
develop, commit c7a5bed) and 7.x - SqlRender 1.19.7
- RSQLite 2.4.x, duckdb 1.x
- R 4.6.1 on macOS (aarch64)
- Dominant language
- R
- Stars
- 57
- Forks
- 96
- Avg merge
- 5d 2h
- Merged PRs (30d)
- 2
Getting set up
- No Dockerfile or Docker Compose file
- Has a pull request template
- No contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from OHDSI/DatabaseConnector
-
querySqlToAndromeda(appendToTable = TRUE) errors with "This ResultSet is closed" after appendingOpen
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
OHDSI/DatabaseConnector#346 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
OHDSI/DatabaseConnector#340 ·
-
Difficulty 3/5 1-2 days Newbie friendliness 50/100
OHDSI/DatabaseConnector#335 · 7 comments ·
-
Difficulty 3/5 1-2 days Newbie friendliness 62/100
OHDSI/DatabaseConnector#333 · 1 comment ·
-
Switch from Simba to Databricks JDBC driverPossibly taken @developer-rpai claimed this 17 days ago. Open
Difficulty 3/5 1-2 days Newbie friendliness 48/100
OHDSI/DatabaseConnector#331 · 1 comment ·
All issues in OHDSI/DatabaseConnector
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
waldronlab/bugphyzz#256 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 71/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 72/100