SQL query do not return whole data

[czmoku (Pawel Czmok)]

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.

[bogdan.pintea]

Hi @czmoku ,

Not sure why you'd get different results, if your index pattern is the same and no frozen indices are involved, it should return the same data.

You could try to investigate it by translating the query and executing the native Elasticsearch query directly and observing the results, so you could tweak your query if needed. The SQL engine does something similar.