You can translate the question and the replies:

Array Contains with Databricks driver.

Hi, i've been working with Denodo and Databricks (Using a JDBC connection) and i've encountered an issue with the databricks driver. It seems like when i perform a query like this: ``` Select teammembers from some_table where (teammembers).item = 'some_value' ``` This query is then translated to databricks wrongly and i get the following error: ``` [DATATYPE_MISMATCH.BINARY_OP_DIFF_TYPES] Cannot resolve "(teamMembers = some_value)" due to data type mismatch: the left and right operands of the binary operator have incompatible types ("ARRAY<STRING>" and "STRING").; ``` The datatype of teammembers is: ARRAY OF REGISTER OF (item:text (sourcetypeid = '12')); Am i doing something wrong here? Or is the driver not working as expected? Because if i use this directly in Denodo (without the JDBC connection to Databricks but pointing to a REST API it works out of the box). Thanks.
user
08-11-2023 15:57:21 -0500
code

3 Answers

Hi, I understand that when you execute a query (that belongs to Databricks source) in Denodo by creating a JDBC data source, you end up with the following error: > [DATATYPE_MISMATCH.BINARY_OP_DIFF_TYPES] Cannot resolve "(teamMembers = some_value)" due to data type mismatch: the left and right operands of the binary operator have incompatible types ("ARRAY<STRING>" and "STRING").; It works fine in Denodo without a JDBC Connection by pointing to a REST API. So, the issue may be with how the Databricks JDBC driver is interpreting the query or handling the data types. The above error message is usually encountered in the [Databricks](https://learn.microsoft.com/en-us/azure/databricks/error-messages/datatype-mismatch-error-class#binary_op_diff_types) when the types of the operands involved in the operation are incompatible. You could also check with yourDatabricks support team regarding this thread. In order to further isolate that the issue could be with the Databricks driver, you could try connecting the Databricks source from any other external JDBC client tool (eg. DBeaver) using the same JDBC driver you used in Denodo, and check how it works. Also you could try executing the delegated SQL sentence (obtained from the Execution Trace of the query) directly in your Databricks database to check how the query executes. After performing these tests, if you still confirm that the error is occuring only on the Denodo JDBC connection and if you have valid support access, then you could raise a new support case where we could assist you more. Hope this helps!
Denodo Team
09-11-2023 07:39:46 -0500
code
Hi, I've tried to execute the generated query in Databricks directly but it didn't work, it gave the same error. It seems like the query conversion is wrong, and i think the Databricks driver is the cause. Denodo Query: ``` Select teammembers from some_table where (teammembers).item = 'some_value' ``` Generated Databricks query: ``` Select teammembers from v_some_table where teammembers = 'some_value' ``` I was able to run the query in Databricks only by converting the query to something like: ``` Select teammembers from v_some_table where array_contains(teammembers, 'some_value') ``` I also tried changing the Databricks driver to a Spark driver i got a very similar error.
user
09-11-2023 08:23:01 -0500
Hi, I would like to let you know that the information provided will not be sufficient to deeply analyze the issue. We need to review the execution trace and the VQL statements. Therefore, if you have a valid support account, I recommend opening a support case for this issue on the [Denodo Support site](https://support.denodo.com/). Hope this helps!
Denodo Team
10-11-2023 06:31:10 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here