Issue with select * from indexname in tableau connected to elasticsearch using JDBC

[ivar (Ravi)]

I'm using elasticsearch 6.8 and JDBC client for 6.8
when connecting to elasticsearch from tableau, and executing
select * from indexname
it is failing with following error

An error occurred while communicating with the Other Databases (JDBC) data source 'ShowData'.
Bad Connection: Tableau could not connect to the data source.
java.sql.SQLException: Server encountered an error [Index: 0, Size: 0]. [java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
	at java.util.ArrayList.rangeCheck(ArrayList.java:653)
	at java.util.ArrayList.get(ArrayList.java:429)
	at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extractFromSource(FieldHitExtractor.java:185)
	at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extract(FieldHitExtractor.java:117)
	at org.elasticsearch.xpack.sql.execution.search.SearchHitRowSet.extractValue(SearchHitRowSet.java:111)
	at org.elasticsearch.xpack.sql.execution.search.SearchHitRowSet.extractValue(SearchHitRowSet.java:23)
	at org.elasticsearch.xpack.sql.execution.search.ResultRowSet.getColumn(ResultRowSet.java:37)
	at org.elasticsearch.xpack.sql.session.AbstractRowSet.column(AbstractRowSet.java:18)
	at org.elasticsearch.xpack.sql.session.RowView.forEachColumn(RowView.java:38)
	at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.lambda$createResponse$2(TransportSqlQueryAction.java:104)
	at org.elasticsearch.xpack.sql.session.RowSet.forEachRow(RowSet.java:32)
	at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.createResponse(TransportSqlQueryAction.java:102)
	at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.createResponse(TransportSqlQueryAction.java:97)
	at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.lambda$operation$0(TransportSqlQueryAction.java:80)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:61)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:61)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:61)
	at org.elasticsearch.xpack.sql.execution.search.Querier$ScrollActionListener.lambda$handleResponse$0(Querier.java:499)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:61)
	at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.lambda$clear$3(Querier.java:606)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:61)
	at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:85)
	at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:81)
	at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
	at org.elasticsearch.action.search.ClearScrollController.onFreedContext(ClearScrollController.java:130)
	at org.elasticsearch.action.search.ClearScrollController.lambda$cleanScrollIds$2(ClearScrollController.java:115)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:61)
	at org.elasticsearch.action.ActionListenerResponseHandler.handleResponse(ActionListenerResponseHandler.java:54)

error seems to be from elasticsearch class, any suggestions on how to get this fixed

select fieldname from indexname works well

only issue with *

Thanks

[ivar (Ravi)]

Issue seems to be with a field having value as , looks like a bug with jdbc driver , and reproducable from simple java jdbc client,how can I raise a bug to elasticsearch to get this fixed ?

[dadoonet (David Pilato)]

Here:

elastic/elasticsearch

May be @costin would like to confirm first if it's a bug?

[Andrei_Stefan]

@ivar what is the actual value from that field? In your post you mentioned [ ]. This means the value is two square brackets with a white space between them?

Also, next time, do please format the code in your post to make it more readable.

[ivar (Ravi)]

@Andrei_Stefan it is an empty array
sure I'll format my code
the field is of type text

Thanks

[Andrei_Stefan]

@ivar I tried with this data:

{"index":{"_id":1}}
{"test":"test1"}
{"index":{"_id":2}}
{"test":"test2"}
{"index":{"_id":3}}
{"test":""}
{"index":{"_id":4}}
{"test":" "}
{"index":{"_id":5}}
{"test":[]}

And on the most recent 7.x version of Elasticsearch and it doesn't reproduce. There were some fixes in this area of code, so it may be possible this one to be fixed already.

1. Is it 6.8.0 or 6.8.1 that you are using?
2. Any chance you could test this with latest 7.x?
3. Is this JDBC specific, or if you send a REST request to Elasticsearch (see here more about this), you get the same error?
4. What is the exact query you are using?
5. Please, provide a minimal set of data and index mapping to reproduce this.

Thank you.

[ivar (Ravi)]

@Andrei_Stefan
please find the responses

1)I'm using elasticsearch 6.8.0
2)I dont have 7.x I'll try this
3)REST is good
but observed a different issue : if a field has array with more than 1 value, SQL fails to return results
4)select * from mjdbctest
5)POST _bulk
{ "index" : { "_index" : "mjdbctest", "_type" : "_doc", "_id" : "4" } }
{ "keys" : ["key1"],"dupKeys":["dk4"]}
{ "index" : { "_index" : "mjdbctest", "_type" : "_doc", "_id" : "3" } }
{ "keys" : ,"dupKeys":["dk3"]}

[Andrei_Stefan]

Thank you. I am able to reproduce this.

[Andrei_Stefan]

More details about the issue you have here: https://github.com/elastic/elasticsearch/issues/43863.
Regarding "array with more than 1 value, SQL fails to return results", ES SQL doesn't return more than one value and this is by design. A bit more on the subject here and here in the documentation.

[ivar (Ravi)]

Thanks @Andrei_Stefan