Version 1.13 (might be a regression from previous versions)
The following que…ry:
```
POST my_test_index_date/_doc/
{
"field1": {
"subFieldA": "2020-02-02"
}
}
```
generates the following mapping specifying the field as **DATE**:
```
{
"mappings": {
"_doc": {
"properties": {
"field1": {
"properties": {
"subFieldA": {
"type": "date"
}
}
}
}
}
}
}
```
When I try to query it in different combinations that I feel should probably be acceptable, it always fail:
**Case 1**
```
POST _opendistro/_sql
{
"query": """SELECT * FROM
my_test_index_date as i
WHERE
i.field1.subFieldA > '2020-02-01'
LIMIT 50;"""
}
Response -->
{
"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,STRING]",
"type": "ExpressionEvaluationException"
},
"status": 503
}
```
Why the field is considered TIMESTAMP here instead of DATE (like in the mapping)?
It'd be very nice to have the two types (TIMESTAMP and STRING) comparable since we don't want the user to always know what kind of Date it is in the mapping
**Case 2**
```
POST _opendistro/_sql
{
"query": """SELECT * FROM
my_test_index_date as i
WHERE
i.field1.subFieldA > DATE('2020-02-01')
LIMIT 50;"""
}
Response -->
{
"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
}
```
Shouldn't TIMESTAMP and DATE be comparable?
**Case 3**
```
POST _opendistro/_sql
{
"query": """SELECT * FROM
my_test_index_date as i
WHERE
(i.field1.subFieldA) > TIMESTAMP('2020-02-01')
LIMIT 50;"""
}
Response --> timestamp parse error
{
"error": {
"type": "SearchPhaseExecutionException",
"reason": "Error occurred in Elasticsearch engine: all shards failed",
"details": "Shard[0]: NotSerializableExceptionWrapper[semantic_check_exception: timestamp:2020-02-01 in unsupported format, please use yyyy-MM-dd HH:mm:ss[.SSSSSS]]\n\nFor more details, please send request for Json format to see the raw response from elasticsearch engine."
},
"status": 503
}
```
**Case 4**, working, but unexpected output
```
POST _opendistro/_sql
{
"query": """SELECT * FROM
my_test_index_date as i
WHERE
DATE(i.field1.subFieldA) > DATE('2020-02-01')
LIMIT 50;"""
}
Response --> DATE in Timestamp format instead of DATE format like in mapping
{
"schema": [
{
"name": "field1",
"type": "object"
}
],
"datarows": [
[
{
"subFieldA": "2020-02-02 00:00:00"
}
]
],
"total": 1,
"size": 1,
"status": 200
}
```