SQL: _id field made available as column to drivers

[bpintea]

If a mapping defines an _id [sub]field, this is returned in SYS COLUMNS.
When querying this field however, an error is returned.

Example: Kibana's "ecommerce" sample data mapping sample:

 "products" : {
          "properties" : {
            "_id" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },

being listed as

distribution_run|null           |kibana_sample_data_ecommerce|products._id                 |12             |TEXT           |0              |2147483647     |null           |null           |1              |null           |null           |12             |0               |2147483647       |1               |YES            |null           |null           |null           |null            |NO

and triggering the exception on query:

sql> select products._id from kibana_sample_data_ecommerce;
Server error [Server encountered an error [Cannot extract value [products._id] from source]. [SqlIllegalArgumentException[Cannot extract value [products._id] from source]
        at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extractFromSource(FieldHitExtractor.java:176)
        at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extract(FieldHitExtractor.java:105)
        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)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.execution.search.Querier$ScrollActionListener.handleResponse(Querier.java:506)
        at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:580)
        at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:556)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:316)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:51)
        at org.elasticsearch.action.search.FetchSearchPhase$3.run(FetchSearchPhase.java:213)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:166)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:159)
        at org.elasticsearch.action.search.ExpandSearchPhase.run(ExpandSearchPhase.java:120)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:166)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:159)
        at org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:206)
        at org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$2(FetchSearchPhase.java:104)
        at org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:110)
        at org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:44)
        at org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:86)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:41)
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:751)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
]]

[elasticmachine]

Pinging @elastic/es-search

[astefan]

I think this one is a duplicate of #39738.

[costin]

With #39738 closed, @bpintea can you confirm whether this issue persists?

[astefan]

I'm investigating.

[bpintea]

@astefan , @costin With a build from [default/zip/be7ee7d/2019-03-15T09:34:08.151420900Z] - so should be one day past the merge of #39738 - I still get the products._id listed, both in CLI sys columns table 'kibana_sample_data_ecommerce' and driver mode -- field listed by a BI tool:

[bpintea]

Additional clarification: the issue in original interpretation (=as the field being listed) is still there. But the error returned when selecting the fields is now different ([SqlIllegalArgumentException[Arrays (returned by [products._id]) are not supported]), which is actually a different issue (and probably addressed accordingly, #39700).

[astefan]

@bpintea the second error message is correct, that's how it's supposed to happen imo. But, after this PR we will display one value and not issue an error message anymore, for drivers.

Regarding the _id field, that's a sub-field, and user-defined and it should be returned by SYS COLUMNS, even for drivers. What we shouldn't return, and we do not from what I know, are the meta fields. Their names start with underscore, but they have a different type.

[bpintea]

@astefan Sounds good. I'm, fine with having this issue closed then.

[bpintea]

@astefan With a fresh build, for:

{"query": "SELECT kibana_sample_data_ecommerce.products._id\r\nFROM distribution_run:kibana_sample_data_ecommerce kibana_sample_data_ecommerce", "field_multi_value_leniency": true, "time_zone": "Z", "mode": "ODBC", "client_id": "odbc64"}

I now get back an(other) error:

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

and stacktrace:

org.elasticsearch.xpack.sql.SqlIllegalArgumentException: Objects (returned by [products._id]) are not supported
        at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.unwrapMultiValue(FieldHitExtractor.java:134) ~[?:?]
        at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.unwrapMultiValue(FieldHitExtractor.java:127) ~[?:?]
        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) [?:?]

[astefan]

Hm, this seems to happen for text fields only? If I use products._id.keyword or products.created_on it works.

[astefan]

This seems to be a separate issue, so I created #40296. Closing this one.