SQLite Forum

Round function returning -0.0
Login

Round function returning -0.0

(1) By anonymous on 2021-07-30 04:06:19 [link] [source]

Hi, using SQLite 3.36.0 on a Windows 10 system, I noticed that the round() function will return -0.0 when trying to round 0.0.

Specifically, assuming that myVar has the value 0.0, I noticed that

  • round(myVar) will return 0.0, while
  • round(myVar, 1) will return -0.0

Is this the expected behavior? Can I have a work around to always round zero to 0.0 without a sign?

(2) By Rico Mariani (rmariani) on 2021-07-30 05:24:56 in reply to 1 [link] [source]

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.

(11) By Zois (zoistas) on 2021-07-30 15:06:10 in reply to 2 [link] [source]

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.

(12) By Adrian Ho (lexfiend) on 2021-07-30 15:10:00 in reply to 11 [link] [source]

Did you try the query I mentioned from a Windows sqlite3 CLI?

select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);

(26) By Zois (zoistas) on 2021-08-04 02:45:53 in reply to 12 [link] [source]

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 [source]

I don't have a windows build of sqlite3 on my system.

But on your system, what does .version say

(19) By Adrian Ho (lexfiend) on 2021-07-31 02:10:07 in reply to 11 [link] [source]

Can you also show us the actual code that started this whole thread?

(3) By Adrian Ho (lexfiend) on 2021-07-30 05:34:46 in reply to 1 [link] [source]

Run the 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.

(5) By Rico Mariani (rmariani) on 2021-07-30 06:47:59 in reply to 4.0 [link] [source]

Maybe the issue is with the floating point library provided by MSC on Windows?

(6) By John Dennis (jdennis) on 2021-07-30 07:07:34 in reply to 5 [link] [source]

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

(8) By Rico Mariani (rmariani) on 2021-07-30 08:14:51 in reply to 6 [link] [source]

What did .version say?

(9) By John Dennis (jdennis) on 2021-07-30 08:58:01 in reply to 8 [link] [source]

.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

(14) By Rico Mariani (rmariani) on 2021-07-30 19:04:26 in reply to 10 [link] [source]

Well this one is very interesting. MSVC... so MSC doesn't universally get this wrong. The plot thickens!

(7) By anonymous on 2021-07-30 07:15:54 in reply to 4.1 [link] [source]

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

(15) By Keith Medcalf (kmedcalf) on 2021-07-30 19:22:27 in reply to 1 [link] [source]

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.

(16) By Ryan Smith (cuz) on 2021-07-30 21:04:40 in reply to 15 [link] [source]

Did you just call him a minuscist?

(17) By Adrian Ho (lexfiend) on 2021-07-31 01:59:38 in reply to 16 [link] [source]

Better than "nattering nabob of negativism". :)

(18) By Adrian Ho (lexfiend) on 2021-07-31 02:08:44 in reply to 15 [link] [source]

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. :)

(20) By anonymous on 2021-07-31 16:37:39 in reply to 1 [link] [source]

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.

(22) By Keith Medcalf (kmedcalf) on 2021-07-31 19:41:37 in reply to 21.1 [link] [source]

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.

(23) By Rico Mariani (rmariani) on 2021-07-31 19:57:18 in reply to 22 [link] [source]

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.

(24) By Larry Brasfield (larrybr) on 2021-07-31 20:28:12 in reply to 1 [link] [source]

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); 0.0 sqlite> . Hence, my answers to your questions are:

  1. 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.

  2. 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.

(25) By Rico Mariani (rmariani) on 2021-07-31 22:50:12 in reply to 24 [link] [source]

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>

No repro.