r - Does SQL chunk in Rmarkdown/knitr on Redshift support INNER JOIN and Common Table Expressions? -

really enjoyed using rmarkdown/knitr execution of sql chunks recently, seems there limitation more complex queries. i've been using redshift data base rjdbc powered connection.

specifically using common table expressions inner join:

```{sql, connection=redshift, output.var="field_count"}     cte (       select distinct field               table       date >= '2017-01-01'   )select count(distinct field) cte inner join table_2 t2 on t2.join_here = cte.join_here; ``` 

i've used cte , inner join in individual queries, combining them leads error:

error in .verify.jdbc.result(r, "unable retrieve jdbc results set ", : unable retrieve jdbc result set         cte (           select distinct field                       table           date >= '2017-01-01'       )select count(distinct field)     cte     inner join table_2 t2 on t2.join_here = cte.join_here; ([amazon] (500335) 1 query expected:         cte (           select distinct field                       table           date >= '2017-01-01'       )select count(distinct field)     cte     inner join table_2 t2 on t2.join_here = cte.join_here;) calls: <anonymous> ... dbsendquery -> dbsendquery -> .local -> .verify.jdbc.reslt execution halted 

it appears being translated wrongly/duplicated on querying out of rstudio session.

is known limitation? if documented anywhere?

is known bug? if in existing issue?

edit: visibly happening within knit process, individually running chunks interactively returns expected results.

i'd suggest trying version on redshift jdbc driver.

i got same error, however, while using pentaho mileage may vary. didn't find documentation or other info related this. problem got solved switching away newest driver version. using redshiftjdbc4-1.2.1.1001.jar (jdbc 4.0 compatible) , switched redshiftjdbc4-1.1.10.1010.jar.