There are 3 issues I met when using R passthrough SQL which I want Rstudio/Posit's attention.
(1) Many (could I say Most?) complex nested sql codes could not work directly in R passthrough, must use "WITH" clause to create 1 or more temporary/intermediate tables to accomplish the purpose. This contradicts my understanding of the "passthrough" spirit, which is also a struggle for me to use R passthrough SQL, because it could not passthrough most of my nested SQL directly.
Before I used SAS passthrough SQL which seems could handle any complicated SQL statement, also SAS passthrough could handle many SQL statements in one passthrough. I was told from Python users that Python could passthrough any SQL statements without any modification.
(2) Some not very complex query (with two "left join") seems running well with no error from R passthrough, but the result table got total different column names.
I met this situation recently, the variable names are changed to C_2, C_3, C_4, ..., not the orginal variable names patid, Index_date, age, etc.
Using "WITH" clause to split the two "left join" could get the right result table, but why do we have to use "WITH" clause? The first way runs well from SQL Platform.
(3) R passthrough SQL could not run the statement with function "listagg", which works perfectly from SQL Platform. There are maybe other SQL functions that could not run from R passthrough.
Could we say these as the weakness of the R passthrough SQL?
My experience of using R passthrough is it is too less powerful (in handling nested query) and less convenient than SQL Platform, even much less powerful and less convenient as SAS passthrough. The main/biggest issue is it could not passthrough any SQL statement.
Could Rstudio/Posit check these situations and consider to improve the R passthrough to completely passthrough any nested SQL statement?