Hello
We facing with issue with Apache Superset + elasticsearch. Apache Superset use the SQL (this one Elasticsearch SQL: Query Elasticsearch Indices with SQL | Elastic) and we notice problems with fetching old data. Our data are indexed by logstash with daily index rolling. I tested this query:
POST _sql
{
"query": "SELECT COUNT(*) AS count FROM \"logstash-data-*\" WHERE \"@timestamp\" >= CAST('2021-03-18T00:00:00' AS DATETIME) AND \"@timestamp\" < CAST('2021-03-19T00:00:00' AS DATETIME)"
}
As you can see, it is standard query with date between. Similar query is executed by Apache Superset.
If we query for data later than 8 days (for example today is 04-06, and we set range between 03-27 and 03-28) then we do not have any results and count is always "0". Same is for select with single column.
When i made this time range in kibana directly, by using the discovery tab, then we got documents correctly.
We using Elasticsearch 7.4.0 for Debian build connected with three node cluster.