Tableau query not working

[abbashus]

JDBC driver: opendistro-sql-jdbc-1.4.0.0.jar
SQL plugin: opendistro-1.6 release
Elasticsearch version: 7.6.1 OSS

Query:

POST _opendistro/_sql?
{
  "query": """SELECT `accounts`.`gender` AS `gender`,
      SUM(`accounts`.`balance`) AS `sum_balance_ok`
      FROM `accounts`
      GROUP BY `accounts`.`gender`
"""
}

Response:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "invalid value operation on MISSING_VALUE",
    "type": "IllegalStateException"
  },
  "status": 500
}

elasticsearch.log

[2020-04-14T10:27:47,759][WARN ][c.a.o.s.e.f.PrettyFormatRestExecutor] [f45c898a2ba7.ant.amazon.com] Error happened in pretty formatter
java.lang.IllegalStateException: invalid value operation on MISSING_VALUE
	at com.amazon.opendistroforelasticsearch.sql.expression.model.ExprValue.value(ExprValue.java:23) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.BindingTupleResultSet.lambda$buildDataRows$1(BindingTupleResultSet.java:55) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
	at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1621) ~[?:?]
	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
	at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.BindingTupleResultSet.buildDataRows(BindingTupleResultSet.java:58) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.BindingTupleResultSet.<init>(BindingTupleResultSet.java:35) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.loadResultSet(Protocol.java:99) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.<init>(Protocol.java:74) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:80) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:53) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:161) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:121) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.utils.LogUtils.lambda$withCurrentContext$0(LogUtils.java:72) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:633) [elasticsearch-7.6.1.jar:7.6.1]
	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:830) [?:?]

accounts index mappings: GET accounts/_mappings

{
  "accounts" : {
    "mappings" : {
      "properties" : {
        "account_number" : {
          "type" : "long"
        },
        "address" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "age" : {
          "type" : "long"
        },
        "balance" : {
          "type" : "long"
        },
        "city" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "email" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "employer" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "firstname" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "gender" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "lastname" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "state" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }
}

[dai-chen]

Similar error message as #401.

[dai-chen]

The issue has been fixed in new query engine (which is experimental and disabled by default). You can enable it to preview by changing the plugin setting: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-engine-new-enabled. Thanks!