search

OHDSI / SqlRender

This is an R package and Java library for rendering parameterized SQL, and translating it to different SQL dialects.
https://ohdsi.github.io/SqlRender
Other
82 stars 77 forks source link

old syntax used in "drop index" #267

Open wyriwyg opened 3 years ago

wyriwyg commented 3 years ago

Achilles is using an old syntax for "drop index", which does not work well in certain cases. Here is the error message produced by this drop index statement:

DBMS:
sql server

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot drop the index 'results_deid.idx_ar_aid', because it does not exist or you do not have permission.

SQL:
drop index results_deid.idx_ar_aid

R version:
R version 3.6.0 (2019-04-26)

Platform:
x86_64-redhat-linux-gnu

This syntax is not working well and it has as also described by the official DROP INDEX documentation:

Important

The syntax defined in will be removed in a future version of MicrosoftSQL Server. Avoid using this syntax in new development work, and plan to modify applications that currently use the feature. Use the syntax specified under instead. XML indexes cannot be dropped using backward compatible syntax.

Hope this can be fixed soon.

AnthonyMolinaro commented 2 years ago

This issue was originally posted in Achilles: https://github.com/OHDSI/Achilles/issues/609

DROP INDEX IF EXISTS is newer syntax supported by SQL Server, but not other platforms, and is not currently supported by SqlRender.

schuemie commented 2 years ago

We can certainly add this new syntax to OHDSI SQL. Could you help by figuring out the equivalent SQL in the other supported dialects (that do support deleting indices if they exist)? For example, DROP INDEX IF EXISTS does exist (using exactly the same syntax) in Postgres.

AnthonyMolinaro commented 2 years ago

@schuemie Thanks, Martijn. Yes, will do.