SQlite 3.43.0 Windows 10

SELECT round(146.3599,1) 146.4

SELECT round(146.3599,2) 146.3599999999

Thanks

-jm

In version 3.44.0 is fixed...

-- Loading resources from C:\Users\e608313/.sqliterc
SQLite version 3.44.0 2023-09-05 15:03:23
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT round(146.3599,1);
┌───────────────────┐
│ round(146.3599,1) │
├───────────────────┤
│ 146.4             │
└───────────────────┘
VM-steps: 9
Run Time: real 0.009 user 0.000000 sys 0.015625
sqlite> SELECT round(146.3599,2);
┌───────────────────┐
│ round(146.3599,2) │
├───────────────────┤
│ 146.36            │
└───────────────────┘
VM-steps: 9
Run Time: real 0.009 user 0.015625 sys 0.000000

I'm unable to reproduce this problem. I get:

SELECT round(146.3599,1), round(146.3599,2);
┌───────────────────┬───────────────────┐
│ round(145.3599,1) │ round(145.3599,2) │
├───────────────────┼───────────────────┤
│ 145.4             │ 145.36            │
└───────────────────┴───────────────────┘

I tried this using MSVC builds of 3.43.0, both 64-bit and 32-bit. Same answer ever time.

Thanks all for your replies

Below are other examples on my PC (DELL computer Intel xeon):

round(123.12345,1) 123.1

round(123.12345,2) 123.12

round(123.12399,3) 123.124

round(123.12399,4) 123.124

round(146.36,1) 146.4

round(146.36,2) 146.35999999999999

??

What possible explanation ?

SELECT sqlite_source_id() 2023-08-24 12:36:59 0f80b798b3f4b81a7bb4233c58294edd0f1156f36b6ecf5ab8e83631d468778c

round(146.36,2) 146.35999999999999


??

What possible explanation ?

Try this: SELECT round(146.36,2) - 146.36;

If you are still puzzled, read up on the limitations of floating point representation of real numbers. Or search through the never-ending posts here on that topic.

Are you using the official CLI, or are you using a 3rd-party tool? If the latter, I suggest the problem is in the 3rd-party tool that you are using.

SQLite uses the IEEE 754 binary64 format to represent floating point values. But 10.555 does not exist as a number in that format. The closest available number that can be represented as binary64 is

10.55499999999999971578290569595992565155029296875

And, if you round that number to two decimal places, you get 10.55.

Please reread https://sqlite.org/floatingpoint.html if you have not done so already.

Expected result: 10.56

You may want to read this post. It's long, but worth it.

Sorry M. Hipp for this late reply

We don't use any 3rd-party tool

Our app is written in Tcl/Tk with SQLite interface

While this interface is bundled in relases of Tcl langage, we don't want to wait for new release (once a year) to benefit for improvement of each SQLite version.

So we found Mr Werner's (talented dev) site which provides a compilation service on line for various Tcl packages see : http://www.ch-werner.de/cgi-bin/luck.pl

So our SQLite library commes from there

We are trying to get in touch with Mr Werner

Thanks

Sorry M. Brasfield, I didn't saw your post at first

On my PC : SELECT round(146.36,2) - 146.36

 0.0

Thanks

AND (repeating myself, sorry) :

SELECT round(146.36,2)

146.35999999999999

SELECT round(146.36,3)

146.35999999999999

SELECT round(146.36,1)

146.4

SELECT round(146.36,0)

146.0

Thanks

We cannot reproduce your problem. You can repeat yourself all you want, but that will not help us to reproduce your problem. If we cannot reproduce the problem, there is no hope of getting it fixed.

You say you are using TCL. Please show us a short TCL script that will reproduce the problem, then.

Your problem is difficult to reproduce. Hence, it is on you to be very precise in your demonstration of the problem. Throwing some queries and answers on the screen is not sufficient. Show us exactly what you did to submit those queries and get the result.

Will you copy the command prompt that starts your CLI? I.e.

12:00:46.31>sqlite3
-- Loading resources from C:\Users\e608313/.sqliterc
SQLite version 3.44.0 2023-09-05 15:03:23
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Forgot to mention that on my PC tk_messageBox fires and displays

"result : 146.35999999999999"

Thanks

Add one more db1 eval with this string,

"SELECT sqlite_version(),sqlite_source_id();"

and print the output to your messageBox and tell us what it gives you.

Sorry for this late reply

I have no CLI installed on my PC, but if important I can download and install it

If you want the result of SELECT sqlite_source_id(), here it is :

2023-08-24 12:36:590b798b3f4b81a7bb4233c58294edd0f1156f36b6ecf5ab8e83631d468778c

With the currently released SQLite3 and its Tcl adapter, that little program produces: 146.36 , without any message box.

What do you get with: db1 eval { select sqlite_version() } , (within the lifetime of the object known as "db1")?

I did this in posts #20 in this thread

our both posts reached this forum at same time I guess

Thanks

In the previous script, I added the following lines

set version [db1 eval "SELECT sqlite_version()"] set sourceId [db1 eval "SELECT sqlite_source_id()"]

tk_messageBox -message "$version n $sourceId"

The first line of message is :

3.43.0

However I can't copy and paste the value of $souceID from the tk_messageBox

So I copied it from a widget acting as a console in the app where the pb apeared at first, here it is :

2023-08-24 12:36:59 0f80b798b3f4b81a7bb4233c58294edd0f1156f36b6ecf5ab8e83631d468778c

(there is only one version of Tcl + SQlite on this PC)

From the replies of everybody, especialy from M. Hipp, and you, that nobody can reproduce the pb at all, it is easy to guess that even coming from the same source code, the executable is not exactly the same (see my post #12 in reply to M. Hipp)

If somebody is interresed in the result of our quest, let me know

So thank you for your replies and don't waste your time

Thanks again

If somebody is interresed in the result of our quest, let me know

If would be beneficial if you place what you find out on this post. There may be others with the same problem and can find the solution when you post it.

The end of this story can be read in :

https://www.androwish.org/home/tktview/193726689b

Thanks for providing the end of the story.

Hmm, I'm still pretty unsure. I bet we need Episode 2 of it to be convinced, that toolchain mixing ain't no good idea. Had jmc used the 32 bit version, things were even more blurred, since my Win32 builds are without the shinyness of MMX/SSE/AVX/+++ but support even the good ole i486 dinosaurs. Which certainly the highly optimized MSVC tools learned to forget since a decade.

While waiting for progress toward Episode 2, and just for curious people as I'm the only one to experience this, below is a new script in Tcl exhibiting the pb on my PC (sorry for poor formating). This scrip produces 2 text files : msg_round1.txt and msg_round2.txt

package require sqlite3

sqlite3 db1 :memory:

set version  [db1 eval "SELECT sqlite_version()"]
set sourceId [db1 eval "SELECT sqlite_source_id()"]

db1 eval "CREATE TABLE T1 ( A1 REAL )"
        
db1 eval "INSERT INTO T1 ( A1 )
          VALUES (1.125 ),
                 (1.125 )"

set res [db1 eval "SELECT round(total(round(A1,2)),2)
                   FROM T1"]

if {$res != 2.26} {
    
    set msgRound1 [open msg_round1.txt w]
    puts $msgRound1 "version : $version \nsourceId : $sourceId \nresult : $res"
 
    close $msgRound1
}

db1 eval "SELECT round(1.12515,0) AS A, round(1.12515,1) AS B, round(1.12515,2) AS C, round(1.12515,3) AS D, round(1.12515,4) AS E" {}

set msgRound2 [open msg_round2.txt w]
 
puts $msgRound2 "A = $A \nB = $B \nC = $C \nD = $D \nE = $E"
 
close $msgRound2

On my PC the content of the file msg_round1.txt is :

version : 3.43.0 
sourceId : {2023-08-24 12:36:59 0f80b798b3f4b81a7bb4233c58294edd0f1156f36b6ecf5ab8e83631d468778c} 

result : 2.2600000000000002

And the content of file msg_round2.txt is :

A = 1.0 

B = 1.1 

C = 1.1300000000000001 

D = 1.125 

E = 1.1252

If you're asking for 146.3599, why is it saying 145.3599 in the column headers ?

From SQLite version 3.39.5 2022-10-14 20:58:05 I get

 sqlite> .mode box
sqlite> SELECT round(146.3599,1), round(146.3599,2);
┌───────────────────┬───────────────────┐
│ round(146.3599,1) │ round(146.3599,2) │
├───────────────────┼───────────────────┤
│ 146.4             │ 146.36            │
└───────────────────┴───────────────────┘

Have you tried your experiments using the latest trunk check-in of SQLite?

Sorry Mr Hipp for this late reply

I can't compile myself any C source code as, unfortunately, I have not the necessary skills to do so (nor a C compiler)

However, I have recently obtained the latest release (3.43.1) of SQLite compiled whith latest Visual Studio from the person distributing the Tcl langage for Windows we use (both compiled with MSVC but maybe not exactly same version)

All our tests this morning with this 3.43.1 compiled with MSVC are OK and our previous problem has disapeared.

So it seems the source of our pb was wixing Tcl langage compiled with MSVC and SQLite extension for Tcl compiled with MingW

Thank you again