# Elasticsearch returns different values from DSL and SQL query **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [January 1, 2021, 8:08am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984 "2021-01-01T08:08:32Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![pkshara](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pkshara/32/48222_2.png) [@pkshara](https://discuss.elastic.co/u/pkshara)\ **Post date:** [January 1, 2021, 8:08am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/1 "2021-01-01T08:08:32Z")
I am trying to query index using dsl and sql method, while comparing the results i found that the values are different.Below are the query and required details. DSL query and result ![image](https://us1.discourse-cdn.com/elastic/original/3X/6/b/6bac0191da0508529101ea8d9182e11098830335.png) SQL query and result ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/a/caba68d6127e34c069319fccab630fa324e98291.png) Result from Kibana ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/6/0632a2a5ffd1eeaeae6661fb8050dda92ce905b3.png) Values of chargeInUSD from both the result set differs. I am trying to understand the logic behind the value changes for it. TIA ---
**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\ **Post date:** [January 1, 2021, 8:43am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/2 "2021-01-01T08:43:04Z")
May be i'm blind 🙂 but i see exactly the same results ---
**Author:** ![pkshara](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pkshara/32/48222_2.png) [@pkshara](https://discuss.elastic.co/u/pkshara)\ **Post date:** [January 1, 2021, 10:02am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/3 "2021-01-01T10:02:18Z")
Value of ChargeInUSD is different. docId:ad2a5fb4bf83f9b71947d8acf1a3a28b58d9115a value in SQL query = 472.46060 **18066406** dsl query = 472.46060 **38057** docID:c806e6213793f13726ed2cdb95b439b97e5011ec value in SQL = 195.5008 **2397460938** dsl query = 195.5008 **179837** highlighted the differed value . ---
**Author:** ![pkshara](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pkshara/32/48222_2.png) [@pkshara](https://discuss.elastic.co/u/pkshara)\ **Post date:** [January 5, 2021, 6:51am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/4 "2021-01-05T06:51:13Z")
@ylasri Values are different.. have highlighted them ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [January 5, 2021, 7:10am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/5 "2021-01-05T07:10:39Z")
Please don't post pictures of text, they are difficult to read, impossible to search and replicate (if it's code), and some people may not be even able to see them 🙂 It'd help a lot if you copied the queries and responses and posted them in the topic. ---
**Author:** ![pkshara](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pkshara/32/48222_2.png) [@pkshara](https://discuss.elastic.co/u/pkshara)\ **Post date:** [January 5, 2021, 9:17am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/6 "2021-01-05T09:17:23Z")
Query in DSL GET prv\_charge\_data\_extract/\_search { "\_source": ["chargeInUSD","chargeInCntryCurr","exchangeRate"], "query": { "terms": { "docID": [ "c806e6213793f13726ed2cdb95b439b97e5011ec", "ad2a5fb4bf83f9b71947d8acf1a3a28b58d9115a" ] } } } Result -: { "\_index" : "prv\_charge\_data\_extract", "\_type" : "\_doc", "\_id" : "ad2a5fb4bf83f9b71947d8acf1a3a28b58d9115a", "\_score" : 1.0, "\_source" : { "chargeInUSD" : **472.4606038057** , "exchangeRate" : 0.817084, "chargeInCntryCurr" : 386.04 } }, { "\_index" : "prv\_charge\_data\_extract", "\_type" : "\_doc", "\_id" : "c806e6213793f13726ed2cdb95b439b97e5011ec", "\_score" : 1.0, "\_source" : { "chargeInUSD" : **195.5008179837** , "exchangeRate" : 0.735956, "chargeInCntryCurr" : 143.88 } } Query IN SQL -: GET \_sql?format=txt { "query":""" SELECT docID, chargeInUSD, chargeInCntryCurr,exchangeRate FROM prv\_charge\_data\_extract WHERE docID in ('c806e6213793f13726ed2cdb95b439b97e5011ec','ad2a5fb4bf83f9b71947d8acf1a3a28b58d9115a') """ } Result -: docID | chargeInUSD |chargeInCntryCurr| exchangeRate ----------------------------------------+------------------+-----------------+------------------ ad2a5fb4bf83f9b71947d8acf1a3a28b58d9115a| **472.4606018066406** |386.0400085449219|0.817084014415741 c806e6213793f13726ed2cdb95b439b97e5011ec| **195.50082397460938** |143.8800048828125|0.7359560132026672 the bold highlighted values differs in SQL and DSL results. After i tried it many queries for different indices, i observed that the values for float datatype filed gives different result. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [January 5, 2021, 9:19am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/7 "2021-01-05T09:19:23Z")
What is the mapping on the `chargeInUSD` field. Please format your code/logs/config using the `` button, or markdown style back ticks. It helps to make things easy to read which helps us help you 🙂 ---
**Author:** ![pkshara](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pkshara/32/48222_2.png) [@pkshara](https://discuss.elastic.co/u/pkshara)\ **Post date:** [January 5, 2021, 9:21am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/8 "2021-01-05T09:21:49Z")
> [@warkolm](#): > > chargeInUSD chargeInUSD is an float datatype field ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [January 6, 2021, 10:56am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/9 "2021-01-06T10:56:11Z")
What you get from Elasticsearch in query DSL is the \_source meaning the exact "text" those documents were indexed with. What you get from SQL is very likely to be the value that was actually indexed. Try testing the same query DSL but adding `docvalue_fields` to it and put there your `chargeInUSD` field and see what you get back. Documentation about `docvalue_fields` can be checked [here](https://www.elastic.co/guide/en/elasticsearch/reference/7.9/search-fields.html#docvalue-fields). ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [January 11, 2021, 10:53am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/10 "2021-01-11T10:53:18Z")
@pkshara, The difference stems from the fact that the floating point types have a discrete resolution: not all numbers within their value range can be represented with them. `472.4606038057` is such a number and the closest value that Elasticsearch JVM can assign to it is the one you got back. You can improve the precision of your values by mapping these fields as `double` instead of `float` and that should likely cover your precision needs (but still does't rule out issues like this towards the end of the precision range, after some 15 decimal digits). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 8, 2021, 10:53am UTC](https://discuss.elastic.co/t/elasticsearch-returns-different-values-from-dsl-and-sql-query/259984/11 "2021-02-08T10:53:24Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.