dbGetQuery() error use apache drill odbc

[YuanZhencai]

dbGetQuery() returns [30038]Query execution error

Database

Database: DRILL, Drill Version: 01.16.0000

Reproducible Example

apache drill odbc

Example:

1. connect

odbc_url <- 'Driver=/opt/mapr/drill/lib/64/libdrillodbc_sb64.so;AuthenticationType=No Authentication;ConnectionType=ZooKeeper;ZKQuorum=zookeeper:2181;ZKClusterID=drillbits1;'
con <- DBI::dbConnect(odbc::odbc(), .connection_string = odbc_url)

2. query json is success

odbc::dbGetQuery(con, 'select * from cp.`employee.json`')

3. But query show files in xxxx is failed

odbc::dbGetQuery(con, 'show files in cp')

error message

Error in new_result(connection@ptr, statement): nanodbc/nanodbc.cpp:1344: HY000: [MapR][Drill] (1040) Drill failed to execute the query: show files in cp
[30038]Query execution error. Details:[
PARSE ERROR: Non-query expression encountered in illegal context

SQL Query SELECT * FROM (show files in cp) LIMIT 0
               ^

[Error Id: 7d372f3f-1f13-4487-9a9a-d509da08bde7 ]
]
Traceback:

1. odbc::dbGetQuery(con, "show files in cp")
2. odbc::dbGetQuery(con, "show files in cp")
3. .local(conn, statement, ...)
4. dbSendQuery(conn, statement, ...)
5. dbSendQuery(conn, statement, ...)
6. OdbcResult(connection = conn, statement = statement)
7. new_result(connection@ptr, statement)

4. Why query is SELECT * FROM (show files in cp) LIMIT 0，It should be show files in cp

[jimhester]

I think this is coming from the preview code that RStudio calls

https://github.com/r-dbi/odbc/blob/24607d4a5ffd66ecf17b368080cf4fbdb12c4a0a/R/Viewer.R#L225-L242

If you run this query outside of RStudio the viewer code should not run and your query should work.