Double/Real data lost after INSERT and SELECT on Linux

[fkarger]

Describe the bug
After inserting a list of doubles I read them in order to verify if all inserted data is provided by the database in the same way like inserted. In a Linux environment this is not always the case. It seems that the last bit is lost.

To Reproduce
Use the attached Java program like described under Logs.
SqliteInsertReadEqualsTest.txt

Expected behavior
The database should provide inserted values exactly as they were initially.

Logs
Calling the attached program with java -jar SqliteInsertReadEqualsTest.jar 30000 creates the following logs:

1. On Linux Linux Ubuntu 18.04.6 LTS: Data is NOT equal which is the error.
   Creating sqlite table insertReadTest.sqlite.
   Inserting 30000 random doubles.
   Checking data. Sequential order.
   id: 29246; orig: -4.604223587690774; from Db: -4.6042235876907736 - NOT equal
   Checking data. Random order.
   id: 29246; orig: -4.604223587690774; from Db: -4.6042235876907736 - NOT equal
   Ready.

Java version: 17.0.4
Java vendor: GraalVM Community
OS name: Linux
OS version: 5.4.0-150-generic
CPU architecture: amd64
cpus: 16
free: 205 MB
max: 4002 MB
total: 256 MB

2. On Windows 10: "No problem". Much bigger numbers ( > 30k doubles) did not change this.
   Creating sqlite table insertReadTest.sqlite.
   Inserting 30000 random doubles.
   Checking data. Sequential order.
   No problem.
   Checking data. Random order.
   No problem.
   Ready.

Java version: 17.0.4.1
Java vendor: Eclipse Adoptium
OS name: Windows 10
OS version: 10.0
CPU architecture: amd64
cpus: 4
free: 47 MB
max: 2026 MB
total: 128 MB

Environment (please complete the following information):

• OS: Linux Ubuntu 18.04.6 LTS
• CPU architecture: amd64
• sqlite-jdbc version: 3.36.0 up to 3.42.0.0

Additional context
The probability of the error occurring increases with the number of data added. The number can be passed to the test program in the command line. In the test on my system the error occurred from number 30000.

SqliteInsertReadEqualsTest.txt

[gotson]

can you clarify if the data is incorrect in the SQLite db (once written), or when read by the driver ?

[fkarger]

I assume the data is incorrect in the SQLite db because when I try
select * from Doubles where id = 29246;
for the inserted double which is orig: -4.604223587690774
I get
-4.60422358769077 .
I also see -4.60422358769077 when using a viewer like DB Browser for SQLite.

[gotson]

can you make sure of this assumption, so we know where to look ?

I see you insert data with final PreparedStatement statement = connection.prepareStatement("insert into Doubles values(NULL, "+randomX+")");

But you also commented:

// Works, if we use a single preparedStatement like this:
// PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO Doubles (data) VALUES (?)");

Can you clarify this part ?

[fkarger]

If you replace the following loop which is in the attachment

for (int i = 0; i < numDoubles; i++) {
            final double randomX = -10 + 20 * rnd.nextDouble();

            final PreparedStatement statement = connection.prepareStatement("insert into Doubles values(NULL, "+randomX+")");
            statement.executeUpdate();

            // Get ID
            final ResultSet generatedKeys = statement.getGeneratedKeys();
            if (generatedKeys.next()) {
                final int id = generatedKeys.getInt(1);
                dataList.add(new DataEntry(id, randomX));
            }

            // Close.
            generatedKeys.close();
            statement.close();
        }

by

final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO Doubles (data) VALUES (?)");

        for (int i = 0; i < numDoubles; i++) {
            final double randomX = -10 + 20 * random.nextDouble();

            preparedStatement.setDouble(1, randomDouble);
            preparedStatement.executeUpdate();

            // Get ID
            final ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                final int id = generatedKeys.getInt(1);
                dataList.add(new DataEntry(id, randomDouble));
            }

            generatedKeys.close();
        }

        preparedStatement.close();

the problem does not occur.

[fkarger]

The problem also does not occur when I set the last bit of the inserted doubles to zero which means that a tiny amount of precision is potentially lost.

[gotson]

i have a feeling that the problem may not be in the driver.

Can you try to use the sqlite3 command line to do the insert, to confirm whether it is a problem of the driver or of sqlite? The implicit toString on the Double may be the problem.

[fkarger]

Using sqlite3 on the Linux system I tried:
insert into Doubles values(NULL, -4.604223587690774);
then
select * from Doubles where data < -4.604223587690 AND data > -4.60423;
results to
29246|-4.60422358769077 (this is from the mentioned test program)
30001|-4.60422358769077 (this is the manually inserted value)

[gotson]

Is there a specific value that consistently reproduce the issue, or is it random?

[fkarger]

Is there a specific value that consistently reproduce the issue, or is it random?

The problem is reproducible using the same values. I assume it depends on wether the last bit is important.
Maybe you are right and the implicit Double.toString() is the problem.
I will verify that and will report.

[fkarger]

Is there a specific value that consistently reproduce the issue, or is it random?

I printed out the critical statement. On both systems it is

insert into Doubles values(NULL, -4.604223587690774)

which indicates that Double.toString() is not the problem.

[fkarger]

I observed another interesting thing:
Using tools like sqlite3, SQLite Studio and DB Browser for SQLite on Windows 10 I get
-4.60422358769077 for that critical row (which is wrong).

If I read the same row using Java and sqlite-jdbc on the same System I get
-4.604223587690774
which is correct.

[fkarger]

I further tested the following:

1. Creating the Database on Linux using the attached program.
2. Reading the critical line of that Database on Windows using sqlite-jdbc.

I get:
-4.6042235876907736

Again all the other Windows Tools show -4.60422358769077 (wrong).

Maybe there are some rounding issues?

[gotson]

Doubles are approximation by design, could be linked to the jvm you use or the hardware, I don't know.

[fkarger]

Doubles are approximation by design, could be linked to the jvm you use or the hardware, I don't know.

Sure, but you would assume that the same bit pattern is saved.

Btw for someone having the same problem I used the following workaround:
setting the last bit of the double to zero (which can work depending on the application).

Cleaner (but cumbersome) approaches would be:

• using doubleToLongBits() and saving the Long
• using Double.toString() and saving the String

[gotson]

I see you tried a few things and it doesn't seem to be a problem in the driver, but also applies on other tools?

Should we be closing this?

[fkarger]

I see you tried a few things and it doesn't seem to be a problem in the driver, but also applies on other tools?

Should we be closing this?

I am not sure because there are different results on Linux and Windows using the same sqlite-jdbc.jar and the reason is not clear (at least for me). I assume other tools have the same problem or similar problems because they use the same binaries.

[gotson]

If you refer to the binary as the underlying sqlite C library, then the issue should be raised on their tracker.

[fkarger]

If you refer to the binary as the underlying sqlite C library, then the issue should be raised on their tracker.

Ok, thank you.

This could become complicated because sqlite-jdbc is made of Java and underlying libraries and a normal
user would assume consistent behaviour accross platforms (no matter how it is composed)
and cannot easily see who is guilty.

[fkarger]

According to this discussion it is better to use in general a version which directly inserts a double and avoid a double to text conversion:

https://sqlite.org/forum/forumpost/aaf79863d8

[gotson]

According to this discussion it is better to use in general a version which directly inserts a double and avoid a double to text conversion:

https://sqlite.org/forum/forumpost/aaf79863d8

Very good discussion over there. I will be closing this as this is not related to the JDBC driver.