[SERVER-50541] Problem when the $trunc operator Created: 20/Aug/20  Updated: 27/Oct/23  Resolved: 09/Jan/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Ever N/A Assignee: Backlog - Query Execution
Resolution: Works as Designed Votes: 0
Labels: Operator, bug, mongodb, qexec-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2020-08-20-17-32-19-994.png     PNG File image-2020-08-20-17-33-08-167.png     PNG File image-2020-08-20-17-35-17-122.png     PNG File image-2020-08-20-17-35-41-798.png     PNG File image-2020-08-20-17-38-05-874.png     PNG File image-2020-08-20-17-39-09-282.png    
Issue Links:
Backports
Related
related to SERVER-71557 The server does not always round half... Closed
Assigned Teams:
Query Execution
Backport Requested:
v4.4, v4.2, v4.0
Sprint: Query 2021-01-11, Query Execution 2021-05-03, Query Execution 2021-05-17, QE 2021-10-18, QE 2021-11-01, QE 2021-11-15, QE 2021-11-29, QE 2021-12-13, QE 2021-12-27
Participants:

 Description   

I have a problem when I truncate a number that has the same decimals that have the  <place> value. the problem is that the value is round to down and give the wrong number. I have the following situation:
a number: 4.56 in my DB and the following query: { $project: { newField: { $trunc: ["$number", 2] }} }
the outcome of this query is 4.55 a wrong value, I was expecting to get the same number that I stored before. The documentation says the following:

Because of that, I don't understand the reason for the error and also the documentation doesn't show an example when the <place> and the <number> has the same digits.

 

Image reference:

Data on the DB

Output, after apply the query 

 

 

 

 



 Comments   
Comment by Ivan Fefer [ 09/Jan/23 ]

I left a detailed comment about floating point numbers in a similar ticket: SERVER-71557

Summary: internally floating point numbers represented in binary (as is everything in computers). When converting to binary, 4.56 is actually an infinite number: 4.56(10) = 100.1000 1111 0101 1100 0010 1000 1111 0101 1100 0010 1000 1111 0101 1

In 64-bit double precision floating point format it is stored approximately.
You can see this if you print it with fixed number of decimal digits:

JavaScript/MongoDB shell:

4.56.toFixed(20)
'4.55999999999999960920' 

So when you call {$trunc: [4.56, 2]}, it is correctly truncated to 4.55.

This is not something unique to MongoDB, it is a part of IEEE-754 standard, used commonly across databases and programming languages.

If you want more precision, you can try using Decimal128 data type.

We created a ticket to improve the documentation around this to avoid confusion around such issues.

 

Comment by Ever N/A [ 05/Oct/20 ]

Hey Eric don't worry for the delay, about the NumberDecimal type I don't do test with it but you have reason about the exact precision. The thing that I don't understand is the behavior when we do the operation and it has a specific number of decimal places this behavior is good?

Comment by Eric Sedor [ 05/Oct/20 ]

Hi evercabarcasmallarino@gmail.com, and sorry for the delay. I am passing this on to an appropriate team.

It's worth noting that the NumberDecimal type in the MongoDB shell (or Decimal128 in Compass), is intended for "exact precision when rounding" (and probably truncing). The behavior you describe doesn't seem to occur for that type.

Comment by Massimiliano Marcon [ 26/Aug/20 ]

Not a compass or shell issue.

Generated at Thu Feb 08 05:22:56 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.