Round function returning -0.0
using SQLite 3.36.0 on a Windows 10 system, I noticed that the
round() function will return
-0.0 when trying to round
Specifically, assuming that
myVar has the value
0.0, I noticed that
round(myVar, 1)will return
Is this the expected behavior? Can I have a work around to always round zero to
0.0 without a sign?
Is it possible that if you started with -0 as the variable that was bound to myVar that it just stays that way because round then does nothing? I couldn't make it fail no matter what floating point numbers I put in there which makes me think that the problem is that maybe a bad value was just passed through.
Thank you for the fast response, I think this is not the case though. I explicitly called
round(0.0, 1) on my system and got
-0.0 as a result.
Did you try the query I mentioned from a Windows
select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);
Thanks for the help, I was using version 3.36.0. The issue was caused on my side due to the flags I was using during the build, specifically the flag for floating point numbers. The issue has been resolved on my side, I believe it was specific to my setup.
(13.1) By Rico Mariani (rmariani) on 2021-07-30 19:05:34 edited from 13.0 in reply to 11 [link] [source]
I don't have a windows build of sqlite3 on my system.
But on your system, what does .version say
Can you also show us the actual code that started this whole thread?
sqlite3 CLI and try the following query:
select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);
(4.1) By Rico Mariani (rmariani) on 2021-07-30 06:58:51 edited from 4.0 in reply to 3 [link] [source]
On a Mac.
sqlite> select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1); 0.0|0.0|0.0|0.0 sqlite> .version SQLite 3.32.3 2020-06-18 14:16:19 02c344aceaea0d177dd42e62c8541e3cab4a26c757ba33b3a31a43ccc7d4aapl clang-12.0.5 sqlite>
On Windows but under Ubuntu (Linux on Windows)
sqlite> select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1); 0.0|0.0|0.0|0.0 sqlite> .version SQLite 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1 zlib version 1.2.11 gcc-9.3.0
I don't have an MSC build handy.
Maybe the issue is with the floating point library provided by MSC on Windows?
Running SQLite 3.35.4 on Windows 10: select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1); 0.0|0.0|0.0|0.0
What did .version say?
.version SQLite 3.35.4 2021-04-02 15:20:15 5d4c65779dab868b285519b19e4cf9d451d50c6048f06f653aa701ec212df45e zlib version 1.2.11 gcc-5.2.0
(10) By RandomCoder on 2021-07-30 13:39:45 in reply to 5 [link] [source]
I don't think so:
sqlite> .version SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5 zlib version 1.2.11 msvc-1926 sqlite> select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1); 0.0|0.0|0.0|0.0
Well this one is very interesting. MSVC... so MSC doesn't universally get this wrong. The plot thickens!
Here is the result on Ubuntu SQLite 3.35 : sqlite> select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1); 0.0|0.0|0.0|0.0
So I guess that this is a Windows issue
While flailing about looking for the math library that produced this result may be entertaining, it has zero value because the answer is correct.
Like coloured folk, they may look different on the outside, but on the inside, they are the same. That is, while -0.0 may appear different from +0.0, they are nevertheless the same value.
Did you just call him a minuscist?
Better than "nattering nabob of negativism". :)
While true, getting results that no one else can replicate may be a hint that other aspects of SQLite on the OP's machine may also not work as expected.
Or perhaps something in the OP's problem description is off. (The actual code that caused the error was never posted, for instance.)
Either way, it may strictly be nobody's business but the OP's, but it's certainly an intriguing situation...and an excuse for me to post one of my favorite "guilty pleasure" songs. :)
Rounding with 4/5 method to full numbers looses sign between -0.4999999 and 0.0 . That is systematic.
To keep the sign for small negative values one would need a „floor“ Style rounding algorithm.
(21.1) By Rico Mariani (rmariani) on 2021-07-31 16:43:10 edited from 21.0 in reply to 20 [link] [source]
Yes but that's not happening...
sqlite> select round(-.1); 0.0
Something much more exotic is happening on some builds.
While it may be true that Beezlebub or God Herself (she is black you know) is providing "exotic" results, those "exotic results" are not incorrect. In fact, I would posit that their "exotic" nature is entirely (and only) in the eye of the beholder.
I care about this because I ship to a lot of devices.
Now if I knew which library it was I could try some other things so as to be well informed. This particular behavior is indeed innocuous, but one finds problems by following up on smoke to see if there's fire. If there was an issue with the MSC floating point library I have friends there, I could see it fixed.
I can respect that you think this is a waste of time.
I'm confident that no devils or angels are causing this behavior.
I really wish the original report had included the .version info.
I just this hour downloaded the only sqlite3.exe published at the download page for Windows, and ran it on my up-to-date Windows 10 laptop. I thereby obtained this session screen-scrape:
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .parameter init
sqlite> .parameter set @n 0.0
sqlite> select round(@n, 1);
. Hence, my answers to your questions are:
No, the results you claim to have gotten from "SQLite 3.36.0 on a Windows 10 system" are not to be expected if so obtained.
A "work around to always round zero to 0.0 without a sign" would be to use something properly described as "SQLite 3.36.0 on a Windows 10 system" instead of whatever you used to "return -0.0".
I feel compelled to add: I think this thread has been an utter waste of time and computation resources, particularly after repro steps were requested and never provided. I consider post #1 to be either a false report or one so poorly summarized that nobody should treat it as factual.
FWIW, fresh download of the amalgam and clean build with MSVC.
SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .parameter init sqlite> .parameter set @n -0.0 sqlite> select round(@n, 1); 0.0 sqlite> .parameter set @n -.049 sqlite> select round(@n, 1); 0.0 sqlite> .version SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5 msvc-1928 sqlite>