Inaccurate floating point values since 3.43.0.0

[InitFlo]

Describe the bug
Since 3.43.0.0 some precision of numbers is lost.

To Reproduce

@Test
public void sqliteTest() throws SQLException {
	var testValue = new BigDecimal("3.14");

	try (var conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
		try (var createTblStmt = conn.prepareStatement("create table foo(nr number(10,2));")) {
			createTblStmt.execute();
		}
		try (var insStmt = conn.prepareStatement("insert into foo(nr) values(?);")) {
			insStmt.setBigDecimal(1, testValue);
			insStmt.execute();
		}

		try (var selStmt = conn.prepareStatement("select nr from foo;")) {
			if (selStmt.execute()) {
				try (var selResult = selStmt.getResultSet()) {
					var col1Value = selResult.getBigDecimal(1);
					assertEquals(testValue, col1Value);
				}
			}
		}
	}
}

Expected behavior
I expected 3.14 as the returned column value, but it is 3.1399999999999997.

Environment (please complete the following information):

• OS: Windows 11
• CPU architecture: x86_64
• sqlite-jdbc version 3.43.2.1

[gotson]

Maybe check #927

It's unlikely that it's coming from the driver. Can you try with sqlite cli and compare between versions using the cli? It's most probably coming from sqlite itself.

[gotson]

You could also add any slf4j implementation and enable trace logs to show what exact sql statements are used.

[InitFlo]

The issue is not present in sqlite-tools-win32-x86-3420000.zip and sqlite-tools-win32-x86-3430200.zip.

Yes, there were changes in sqlite: https://www.sqlite.org/changes.html. Probably

6. Enhancements to the decimal extension
   c) If X is a floating-point value, then the decimal(X) function now does a full expansion of that value into its exact decimal equivalent.

is the cause. There is the behaviour of both cli-Versions different.

Php had similar problems, see https://sqlite.org/forum/forumpost/abc65bec19475130.

Can you force sqlite to use the slower algorithm that gives full accuracy? I would prefer it at the cost a few nanosecods. The jni overhead might be bigger.

[gotson]

Can you force sqlite to use the slower algorithm that gives full accuracy? I would prefer it at the cost a few nanosecods. The jni overhead might be bigger.

Without even knowing where/why it happens, no.

[gotson]

I've added some tests to run on CI, which fail on Windows only: https://github.com/gotson/sqlite-jdbc/actions/runs/6649079467/job/18067046014

The 3 tests represent different column affinity.

You can see in the code of setBigDecimal that we use the String representation of the BigDecimal, which is then passed to Sqlite using bind_text. I believe this is done to avoid losing precision by converting into another Java type.

I don't see any good way to bind a BigDecimal using Sqlite: https://www.sqlite.org/c3ref/bind_blob.html

For me that's a Sqlite behaviour, nothing we can do in the driver about it.

[InitFlo]

How are the windows binaries built? Maybe it's a fault by the compiler. Sadly that is too deep for me. The members of the official forum might help.

Here is a test with double values, which surprisingly doesn't fail. So I have the conclusion that the bind_text is messed up if the column affinity is REAL.

@Test
public void sqliteTestDouble() throws SQLException {
	double testValue = 3.14;

	try (var conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {

		try (var createTblStmt = conn.prepareStatement("create table foo(nr number(15));")) {
			createTblStmt.execute();
		}
		try (var insStmt = conn.prepareStatement("insert into foo(nr) values(?);")) {
			insStmt.setDouble(1, testValue);
			insStmt.execute();
		}

		try (var selStmt = conn.prepareStatement("select nr from foo;")) {
			if (selStmt.execute()) {
				try (var selResult = selStmt.getResultSet()) {
					var col1Value = selResult.getDouble(1);
					assertEquals(Double.valueOf(testValue), Double.valueOf(col1Value));
				}
			}
		}
	}
}

[gotson]

Note that if you specify the column as number(15) its affinity will be numeric (rule 5 in section 3.1).

The windows binaries are built using dockcross

[github-actions]

🎉 This issue has been resolved in 3.43.2.2 (Release Notes)