SQL: array columns for drivers

[bpintea]

The Kibana sample data includes array fields which can not be selected (yet), like kibana_sample_data_ecommerce.manufacturer.
We would need a solution for these fields when the client is a driver, likely in line with #39471/#37801.

Example of a failure with ODBC:
{"query": "SELECT kibana_sample_data_ecommerce.category, kibana_sample_data_ecommerce.category.keyword, kibana_sample_data_ecommerce.currency, kibana_sample_data_ecommerce.customer_birth_date, kibana_sample_data_ecommerce.customer_first_name, kibana_sample_data_ecommerce.customer_first_name.keyword, kibana_sample_data_ecommerce.customer_full_name, kibana_sample_data_ecommerce.customer_full_name.keyword, kibana_sample_data_ecommerce.customer_gender, kibana_sample_data_ecommerce.customer_id, kibana_sample_data_ecommerce.customer_last_name, kibana_sample_data_ecommerce.customer_last_name.keyword, kibana_sample_data_ecommerce.customer_phone, kibana_sample_data_ecommerce.day_of_week, kibana_sample_data_ecommerce.day_of_week_i, kibana_sample_data_ecommerce.email, kibana_sample_data_ecommerce.geoip.city_name, kibana_sample_data_ecommerce.geoip.continent_name, kibana_sample_data_ecommerce.geoip.country_iso_code, kibana_sample_data_ecommerce.geoip.region_name, kibana_sample_data_ecommerce.manufacturer, kibana_sample_data_ecommerce.manufacturer.keyword, kibana_sample_data_ecommerce.order_date, kibana_sample_data_ecommerce.order_id, kibana_sample_data_ecommerce.products._id, kibana_sample_data_ecommerce.products._id.keyword, kibana_sample_data_ecommerce.products.base_price, kibana_sample_data_ecommerce.products.base_unit_price, kibana_sample_data_ecommerce.products.category, kibana_sample_data_ecommerce.products.category.keyword, kibana_sample_data_ecommerce.products.created_on, kibana_sample_data_ecommerce.products.discount_amount, kibana_sample_data_ecommerce.products.discount_percentage, kibana_sample_data_ecommerce.products.manufacturer, kibana_sample_data_ecommerce.products.manufacturer.keyword, kibana_sample_data_ecommerce.products.min_price, kibana_sample_data_ecommerce.products.price, kibana_sample_data_ecommerce.products.product_id, kibana_sample_data_ecommerce.products.product_name, kibana_sample_data_ecommerce.products.product_name.keyword, kibana_sample_data_ecommerce.products.quantity, kibana_sample_data_ecommerce.products.sku, kibana_sample_data_ecommerce.products.tax_amount, kibana_sample_data_ecommerce.products.taxful_price, kibana_sample_data_ecommerce.products.taxless_price, kibana_sample_data_ecommerce.products.unit_discount_amount, kibana_sample_data_ecommerce.sku, kibana_sample_data_ecommerce.taxful_total_price, kibana_sample_data_ecommerce.taxless_total_price, kibana_sample_data_ecommerce.total_quantity, kibana_sample_data_ecommerce.total_unique_products, kibana_sample_data_ecommerce.type, kibana_sample_data_ecommerce.user\r\nFROM distribution_run:kibana_sample_data_ecommerce kibana_sample_data_ecommerce", "mode": "ODBC", "client_id": "odbc64"}

generating

{"error":{"root_cause":[{"type":"sql_illegal_argument_exception","reason":"Arrays (returned by [manufacturer]) are not supported"}],"type":"sql_illegal_argument_exception","reason":"Arrays (returned by [manufacturer]) are not supported"},"status":500}.

[elasticmachine]

Pinging @elastic/es-search

[astefan]

Related and relevant issue #33204

[bpintea]

With a fresh build:

{"query": "SELECT kibana_sample_data_ecommerce.manufacturer\r\nFROM distribution_run:kibana_sample_data_ecommerce kibana_sample_data_ecommerce", "mode": "ODBC", "client_id": "odbc64"}

returns

{"error":{"root_cause":[{"type":"sql_illegal_argument_exception","reason":"Arrays (returned by [manufacturer]) are not supported"}],"type":"sql_illegal_argument_exception","reason":"Arrays (returned by [manufacturer]) are not supported"},"status":500}

and corresponding stacktrace:

[2019-03-20T14:44:40,938][WARN ][r.suppressed             ] [node-0] path: /_sql, params: {}
org.elasticsearch.xpack.sql.SqlIllegalArgumentException: Arrays (returned by [manufacturer]) are not supported
        at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.unwrapMultiValue(FieldHitExtractor.java:129) ~[?:?]
        at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extractFromSource(FieldHitExtractor.java:177) ~[?:?]
        at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extract(FieldHitExtractor.java:111) ~[?:?]
        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:79) ~[?:?]
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.xpack.sql.execution.search.Querier$ScrollActionListener.handleResponse(Querier.java:508) [x-pack-sql-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:584) [x-pack-sql-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:558) [x-pack-sql-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:316) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:51) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.FetchSearchPhase$3.run(FetchSearchPhase.java:213) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:166) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:159) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.ExpandSearchPhase.run(ExpandSearchPhase.java:120) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:166) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:159) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:206) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$2(FetchSearchPhase.java:104) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:110) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:44) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:86) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:43) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:747) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:834) [?:?]

.

[bpintea]

Works as expected (once request param field_multi_value_leniency is set and true).