KB486953: How to Resolve the "JdbcStatementWrapper::ExecQuery Failed... AEValueSubQuery is Not Supported" Error When Running a Report Against Databricks Using JDBC Driver.

Symptom

Running a report using a JDBC connection with Databricks results in the following error:
 

JdbcStatementWrapper::ExecQuery failed.
Error type: Jdbc error. Jdbc operation attempted: MIX_JdbcStatement_execute. [HY000:500051: on SQLHANDLE] [Strategy][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 42000, SQL state: Syntax error or access violation, Query: AEValueSubQuery is not supported.

Cause

This error occurs because the JDBC driver (in this case, the Spark JDBC driver) is transforming or rewriting the SQL query before it is sent to Databricks for execution. The JDBC driver is likely altering the original query in a manner that introduces syntax or constructs that Databricks cannot handle.
 

Solution

Modify your JDBC connection string to include the parameter UseNativeQuery=1. This ensures that the SQL query is passed directly to Databricks without any transformation or optimization by the JDBC driver.
 
Example: JDBC;DRIVER={com.Strategy.spark.jdbc.Driver};URL={jdbc:spark://<hostname>:<port>/ UseNativeQuery=1; <Additional Parameters>}
 
This property specifies whether the connector transforms the queries emitted by applications:
0: The connector transforms the queries emitted by applications and converts them into an equivalent form in HiveQL.
1: The connector does not transform the queries emitted by applications, so the native query is used.