So a few things here. This may differ depending on the DBMS you are using. (I.e. in Postgres, it might be possible to use string manipulation or arrays to work around this). You can also obviously build a query string yourself and use it in an R chunk (while exposing yourself to SQL injection
).
But to your question, it seems to me that the generated SQL is being interpreted as a character, as shown here:
And differs little with parentheses:

That would explain why you get no rows back. Because name = "('one', 'two')" is not true for any record.
Further, the error you are getting with multiple_1 (in my code) seems to suggest that the SQL chunk wants a single object.
Now, this could be done using multiple parameters (if you know you have a finite and unchanging number of parameters):
But the question is about using the array itself, and the only way I know to do that (besides with a generated query in an R chunk or array / string splitting trickery in the database) is with dplyr:
And the query that dplyr generates for you:
<SQL>
SELECT *
FROM `df`
WHERE (`name` IN ('one', 'two'))
It may be just a result of my ignorance, but this seems to me like a feature request! For the database back-ends themselves (for parameterized queries), as well as for R Notebooks. (From the discussion here, it seems this feature is lacking in the RPostgres package, at least. My comment concerned arrays, but had a similar aim. I never actually made the feature request, though).
One parting thought is that you could always populate a temp table with the "lookup" values that you want... but that is not a huge help. Just a lot of different ways to work-around 