SQL Plugin: Date filter causes error

SQL
#1

The is the query I am trying to execute:

POST _opendistro/_sql/_explain
{
  "query": "SELECT `test-data1`.`event_type` AS `event_type` FROM `test-data1` 
        WHERE ((`test-data1`.`start_time_UTC` >= DATE('2020-10-17'))) 
        GROUP BY `test-data1`.`event_type`"
}

I get the folloiwng error:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": ">= function expected {[BYTE,BYTE],[SHORT,SHORT],[INTEGER,INTEGER],[LONG,LONG],[FLOAT,FLOAT],[DOUBLE,DOUBLE],[BOOLEAN,BOOLEAN],[STRING,STRING],[TIMESTAMP,TIMESTAMP],[DATE,DATE],[TIME,TIME],[DATETIME,DATETIME],[INTERVAL,INTERVAL],[STRUCT,STRUCT],[ARRAY,ARRAY]}, 
        but get [TIMESTAMP,DATE]",
    "type": "ExpressionEvaluationException"
  },
  "status": 503
}

start_time_UTC is a date type…

"start_time_UTC": { - 
  "type": "date"
},

This query is being launched thru Tableau. Experimental engine is enabled.. Elastic version 7.8.

Help will be appriciated.

#2

Hi @vijay1 ,

I reproduced this error in my local machine. The error point is “test-data1.start_time_UTC >= DATE(‘2020-10-17’)”.

The thing is the elasticsearch date and time types are not matching very well with the JDBC standard data types. So to make for the JDBC driver working well with the new engine, we currently converted elasticsearch date type to timestamp type in the query engine, and also matches to jdbc timestamp type. That explains why you got ExpressionEvaluationExpression saying there is an error to make comparison between timestamp value and date value.

I would suggest you to keep both sides the same type using the functions timestamp(), date() etc. for example:
WHERE DATE(start_time_UTC) >= DATE(‘2020-10-17’)
or
WHERE start_time_UTC >= TIMESTAMP(‘2020-10-17 00:00:00’)

Either should be working fine. Thanks!

#3

Unfortunately, the issue is that with Tablaeu I have very little control over the date functions it uses…

#4

In this case, could you transfer this issue to our SQL repository by creating a new issue here: https://github.com/opendistro-for-elasticsearch/sql/issues/new and we will work around it once we have solution and resources

#5

Similar issue.

#6

Hi @vijay1

Are you try SQL on AWS Elasticsearch 7.8?