Why between tag (in range query) doesn't work in Elastic Search queries?

[busra (Büşra)]

In Elasticsearch i have mapping such as ;

"body": {
"properties":{
"time_Stamp":{
"type" : "date",
"format": "dd.MM.yyyy HH:mm:ss"
},
"value1":{
....
}
....
}
}
}

I want to get data between two dates. If i explain with examples ;

It works and returns values:
POST /_sql
{
"query":"select time_Stamp, value1 from table1 where time_Stamp > '23.01.2020 08:30:00.000'"
}

It works and returns values:
POST /_sql
{
"query":"select time_Stamp, value1 from table1 where time_Stamp < '07.02.2020 08:30:00'"
}

But when i put together two range with 'between' tag it doesn't work:
POST /_sql
{
"query":"select time_Stamp, value1 from table1 where time_Stamp between '23.01.2020 08:30:00' and '07.02.2020 09:01:00'"
}

It couldn't give any error, just query return empty. And also when i write query with AND tag it returns empty.

POST /_sql
{
"query":"select time_Stamp, value1 from table1 where time_Stamp > '23.01.2020 08:30:00' and time_Stamp < '07.02.2020 09:01:00'"
}

It returns like that;
{"columns":[{"name":"time_Stamp", "type": "datetime"}, {"name":"value1", "type": "text"}], "rows":[ ]}

What is the problem in between tag? Is my query wrong or not? Do you have any idea or suggestion?

[Andrei_Stefan]

1. What ES version is this?
2. Please, run the "BETWEEN" query through the translate API and post here the result.

[busra (Büşra)]

My Elasticsearch version is 7.2.0.

when i run this query with translate;

POST /_sql/translate
{
"query":"select time_Stamp from table1 where code = '99fbd3e83d8f27b7dcd086ef2d5019c4' AND ( time_Stamp between '23.01.2020 08:30:00.000' and '24.01.2020 09:00:00.000') order by time_Stamp asc"
}

it returns ;

{
"size" : 1000,
"query" : {
"bool" : {
"must" : [
{
"term" : {
"code.keyword" : {
"value" : "99fbd3e83d8f27b7dcd086ef2d5019c4",
"boost" : 1.0
}
}
},
{
"range" : {
"time_Stamp " : {
"from" : "23.01.2020 08:30:00.000",
"to" : "24.01.2020 09:00:00.000",
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
"_source" : false,
"stored_fields" : "none",
"docvalue_fields" : [
{
"field" : "time_Stamp "
}
],
"sort" : [
{
"time_Stamp" : {
"order" : "asc",
"missing" : "_last",
"unmapped_type" : "date"
}
}
]
}

However when i changed date range it gives an error;

POST /_sql/translate
{
"query":"select time_Stamp from table1where code = '99fbd3e83d8f27b7dcd086ef2d5019c4' AND ( time_Stamp between '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000') order by time_Stamp asc"
}

{
"error": {
"root_cause": [
{
"type": "planning_exception",
"reason": "Cannot generate a query DSL for an SQL query that either its WHERE clause evaluates to FALSE or doesn't operate on a table (missing a FROM clause), sql statement: [select time_Stamp from table1where code = '99fbd3e83d8f27b7dcd086ef2d5019c4' AND ( time_Stamp between '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000') order by time_Stamp asc]"
}
],
"status": 400
}

I don't understand why it gives an error and doesn't work my query. How does 'between' tag work? Is it compare dates step by step? For instance;
this date range works : '23.01.2020 08:30:00.000' and '24.01.2020 09:00:00.000'
this date range doesn't work for me : '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000'

[Andrei_Stefan]

The reason in that error message is self-explanatory: "WHERE clause evaluates to FALSE".
And in your query's case between '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000' doesn't make sense since the upper limit is in the past relative to the lower limit which means that the query will always evaluate to FALSE.

Also, the translation of the initial query is correct. And I don't see why it wouldn't match your documents. Also, to my knowledge we didn't have any bugs in that area.

If you can create a simple reproduceable case, I can try this on my end to see if I find any issues. By "simple reproduceable case" I mean a simple index mapping, test documents and query that fails to return them. Thanks.