Support for array data type

[pasali]

Elasticsearch doesn't have dedicated array datatype. First value in the array determines the field type. So if i have field that contains array of long values, the field type will be long. I am getting below exception when i try to display an array type field.

java.sql.SQLDataException: Can not return value of type java.util.ArrayList as a Long
	at com.amazon.opendistroforelasticsearch.jdbc.types.TypeHelper.objectConversionException(TypeHelper.java:44)
	at com.amazon.opendistroforelasticsearch.jdbc.types.LongType.fromValue(LongType.java:44)
	at com.amazon.opendistroforelasticsearch.jdbc.types.LongType.fromValue(LongType.java:24)
	at com.amazon.opendistroforelasticsearch.jdbc.types.BaseTypeConverter.convert(BaseTypeConverter.java:58)
	at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObjectX(ResultSetImpl.java:530)
	at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObjectX(ResultSetImpl.java:524)
	at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObjectX(ResultSetImpl.java:520)
	at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:503)
	at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.tryGetObject(RemoteResultSetImpl.java:1320)
	at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getObject(RemoteResultSetImpl.java:1272)
	at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getCurrentRow(RemoteResultSetImpl.java:1249)
	at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getObjects(RemoteResultSetImpl.java:1229)
	at sun.reflect.GeneratedMethodAccessor18.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:346)
	at sun.rmi.transport.Transport$1.run(Transport.java:200)
	at sun.rmi.transport.Transport$1.run(Transport.java:197)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)

[dai-chen]

@pasali Thanks for reporting the issue! Could you also post your index mapping and part of the data for us to reproduce the issue?

[pasali]

@dai-chen Here is my mapping and data sample:

PUT /my_index/_mapping
{ 
   "properties":{ 
      "productId":{ 
         "type":"keyword"
      },
      "categoryIds":{ 
         "type":"long"
      }
   }
}

PUT my_index/_doc/1
{
  "productId": 123456789,
  "categoryIds":  [ 567891234134123, 98765432176567 ]
}

[dai-chen]

Thanks! Will look into it.

[dai-chen]

@pasali Thanks for the info! I've reproduced the issue. Currently our plugin returns the type of element instead of array and no support from the JDBC driver. So this issue requires changes from both driver and plugin:

1. Plugin: add array type in response. Opening separate issue Support array type for JDBC driver #326 for tracking.
2. Driver: parse it to java.sql.Array in https://github.com/opendistro-for-elasticsearch/sql-jdbc/blob/625614647c5110daa4e524143d55ecabb8e50794/src/main/java/com/amazon/opendistroforelasticsearch/jdbc/ResultSetImpl.java#L954

Reference forjava.sql.Array: https://docs.oracle.com/javase/tutorial/jdbc/basics/array.html

[dai-chen]

Issue for plugin: #326