Elasticsearch not showing correct data for SQL api

Question

es version : 6.7

Hello i'm using elasticsearch sql api i found that results not correct when i add DISTINCT

es code

require('./elasticsearch/').transport.request({
        method : "POST",
        path   : '/_xpack/sql',
        body : {
            query : "SELECT COUNT(DISTINCT id)  FROM orders"
        }  
    },(err,res) => {
        console.log("error",err)
        return res.status(200).send(res)
    })

results :

{
   "columns":[{"name":"COUNT(DISTINCT id)","type":"long"}],
    "rows":[[11459]] <----------------------------------------------- MUST BE (11501)
}

i execute same query in mysql

SELECT COUNT(DISTINCT id)  FROM orders

results

Any Helps ? Thank you

I believe this answer may be useful: https://stackoverflow.com/a/44501242/5095957 — Nikolay Vasiliev, Dec 20 '19 at 11:48

score 0 · Accepted Answer · answered Dec 25 '19 at 09:39

According to ES member

COUNT(DISTINCT) from SQL is using a cardinality aggregation behind the scene which, by design, doesn't offer accurate results in all scenarios:

https://github.com/elastic/elasticsearch/issues/50354

So basically you should do it with DSL instead of SQL API

Elasticsearch not showing correct data for SQL api

1 Answers1