Issue with exponents in numbers being dropped when using updateRaw with Postgres

[kcbanner]

I used using an updateRaw statement of the form:

marketOrdersDao.updateRaw(
    "INSERT INTO market_orders_temp " + "(" + columnList + ") " + "VALUES " + "(?, ?, ?, ?, ?, ?,...)",
    ... snip ...
    order.getPrice().toString(), // getPrice() returns Double
    ... snip ...
);

For some values of price, specifically ones with exponents, such as 7.527E7. The value would end up in the database as 7.527.

The database in question is my local testing database: PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit. However, I didn't see the issue on my production DB PostgreSQL 13.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 11.2.1 20211203 (Red Hat 11.2.1-7), 64-bit. Both running the same version of ormlite.

If it's relevant, market_orders_temp is a temp table created within the same transaction (ie, not the same table as the dao that updateRaw is called on).

I was able to work around the issue by using a CompiledStatement directly:

try {
	CompiledStatement compiledStatement = connection.compileStatement(
	  "INSERT INTO market_orders_temp (" + columnList + ") VALUES (?, ?, ?, ?, ?, ?, ...)",
	  StatementBuilder.StatementType.UPDATE,
	  new FieldType[0],
	  DatabaseConnection.DEFAULT_RESULT_FLAGS,
	  false);

	try {
		// Insert changed orders into temporary table
		for (MarketOrdersResponse order : changedOrders) {
... snip ...												
		compiledStatement.setObject(6, order.getPrice(), SqlType.DOUBLE);
... snip ...
		}
	} finally {
		IOUtils.closeThrowSqlException(compiledStatement, "compiled statement");
	}
} finally {
	database.getConnectionSource().releaseConnection(connection);
}

I'm thinking this is some oddity with converting strings to numbers with specific database versions, or possibly differing JDKs?

[j256]

Did you try order.getPrice() as opposed to the toString()? I think that you are right that this is some string to number conversion issue.

[kcbanner]

I believe updateRaw takes String[] and not Object[], it looks like that code path uses only the JDBC String type when it ends up setting the objects on the compiled statement?

[j256]

Shit right. Sorry. Yeah I can't reproduce this using H2 and my unit tests. It's the raw methods that need the strings.

[kcbanner]

Yea, it was really strange, the same code running in production was not producing the issue. Issuing queries in pgAdmin manually using the same syntax (7.527E7), to the DB instance that had the issue for me locally were completely fine as well. I didn't get as far as logging out the exact queries that ormlite was generating, but if that's helpful I can go back and try to repro this again.

[j256]

I bet it is the postgres driver that is doing the conversion of 7.527E7 to the double. I bet it is doing a isDigit() or == '.' and just stopping at the E naively.

In terms of the pgAdmin, did you try '7.527E7' i.e. in quotes?

[j256]

I think you should be able to use the Dao and UpdateBuilder for this. Something like:

ub = dao.updateBuilder();
ub.updateColumnValue("price", 7.527E7D);
ub.update();

Does that work? See: https://ormlite.com/docs/update-builder

You can also set the compiled expression and then update it a bunch:

ub = dao.updateBuilder();
ub.updateColumnValue("price", 0.0D);
PreparedUpdate<Foo> pu = ub.prepare();
pu.setArgumentHolderValue(1, 7.527E7D);
dao.update(pu);
pu.setArgumentHolderValue(1, 7.527E7D + 2);
dao.update(pu);

[kcbanner]

Here is a quick test with API tracing on in the JDBC driver (PGJDBC-NG):

I changed the above code to do:

    compiledStatement.setObject(6, order.getPrice().toString(), SqlType.STRING);

// From my logs
services.tasks.MarketUpdateTask - Changed order ID 6252789586 Price 7.579E8

// API trace
PreparedStatement.executeUpdate() returned(1)
... snip ...
PreparedStatement.setObject(parameterIndex=6, x=6252789586, targetSqlType=-5) returned()
PreparedStatement.setObject(parameterIndex=7, x=7.579E8, targetSqlType=12) returned()
... snip ...
PreparedStatement.executeUpdate() returned(1)

Querying for that order in pgAdmin after the update shows me the column has the value 7.579, so it seems something is going on in the JDBC driver, as you suspect.

I can investigate this more this evening and I'll let you know if I find anything out.

The reason I'm not using the update builder here is I'm inserting into a temporary table with a different name than my row class (market_orders_temp vs market_orders), because I insert everything into a temp table then batch insert later from the temp able to the main table with ON CONFLICT SET.

[j256]

Good test. Sounds like you've got a handle on it. Doesn't seem to the ORMLite's fault which is good. ;-)

I'd create a market_orders_temp entity that extends market_orders so you can use the temp DAO.

Have you tried some of the postgres CAST or other internal functions? https://learnsql.com/cookbook/how-to-convert-a-string-to-a-numeric-value-in-postgresql/

[kcbanner]

Yep, agreed this seems to be out of ORMLite's realm. Feel free to close this out :)

Ah, of course - yes I'll extend the entity and just do that. That will clean things up nicely.

Haven't tried casting - I am curious about the root cause. Kind of a scary silent parsing failure somewhere, heh.

Cheers, and thanks for the help.

[j256]

Happy to help Casey. Best of luck. Let me know if there is anything ORMLite could have done better.

I think that I'm going to add a Dao.updateRaw(...) method that takes in an array of ArgumentHolder types so you can do something like:

marketOrdersDao.updateRaw("update ...", new SelectArg(SqlType.DOUBLE, 7.527E7);

[kcbanner]

That would be perfect for cases like this!

[rahulstech]

1. error may be due the data type of price in the database table. table price column may be set to double or real and for which it turncats the E and the rest. if possible change the data type to varchar.

2. i don't know what other reason to use that _tmp table. but if you want to achieve a batch insert or update such that on fail one all will be roll backed, then there is an option TransactionManger.callInSingleTransaction(ConnectionSource, Callable<?>). run your query in the callable. it will run all of the updates in single transaction or rollback transaction on single query failure.

hope it helps you.