JOOQ, JDBC, PostgreSQL MONEY type inside UDT: possible bug?

[goo...@gtod.net]

Below are some example PostgreSQL (DB version 10.0) UDTs: one with a NUMERIC amount and one with a MONEY amount.

Inserting a Bar row and then calling getBars() works fine, while inserting a Foo row and then calling getFoos() does *not* work because (the debugger reveals) the string supplied to the BigDecimal constructor is "$10.00" and it chokes on that leading '$' character.

Frankly I don't know if this is a JDBC or a JOOQ problem and I am working around it by just using NUMERIC types but I did want to just flag this up...

Cheers,

Greg

PS: Thanks for JOOQ, is it truly marvellous!

----

----- PostgreSQL

CREATE TYPE MONEY_UDT AS (
  amount MONEY,
  description TEXT
);

CREATE TYPE NUMERIC_UDT AS (
  amount NUMERIC,
  description TEXT
);

CREATE TABLE foo (
  price MONEY_UDT
);

CREATE TABLE bar (
  price NUMERIC_UDT
);

----- Kotlin

// Assuming some java.sql.Connection conn:

val ctx = DSL.using(conn, SQLDialect.POSTGRES_9_5)

fun insertFoo() {
    ctx.insertInto(FOO, FOO.PRICE)
        .values(MoneyUdtRecord(BigDecimal.TEN, "Hi"))
        .execute()
}

fun getFoos(): List<Foo> =
    ctx.selectFrom(FOO)
        .fetch { record ->
            val price = record.price
            Foo(MoneyUdt(price.amount, price.description))
        }

fun insertBar() {
    ctx.insertInto(BAR, BAR.PRICE)
        .values(NumericUdtRecord(BigDecimal.TEN, "Hi"))
        .execute()
}

fun getBars(): List<Bar> =
    ctx.selectFrom(BAR)
        .fetch { record ->
            val price = record.price
            Bar(NumericUdt(price.amount, price.description))
        }

----- Kotlin

kotlin
insertBar()
insertFoo()
getBars()
getFoos()


----- Error message

org.jooq.exception.DataAccessException: SQL [select "cusoon"."foo"."price" from "cusoon"."foo"]; Error while reading field: "cusoon"."foo"."price", at JDBC index: 1
	at org.jooq_3.10.0.POSTGRES_9_5.debug(Unknown Source)
	at org.jooq_3.10.0.POSTGRES_9_5.debug(Unknown Source)
	at org.jooq.impl.Tools.translate(Tools.java:2199)
	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:677)
	at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1619)
	at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1570)
	at org.jooq.impl.CursorImpl.fetchNext(CursorImpl.java:392)
	at org.jooq.impl.CursorImpl.fetch(CursorImpl.java:378)
	at org.jooq.impl.CursorImpl.fetch(CursorImpl.java:289)
	at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:288)
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:344)
	at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:314)
	at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:1434)
	at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:3639)
	at net.gtod.cusoon.model.Model.getFoos(database.kt:268)
	at net.gtod.cusoon.integrationtest.ModelTest$1$1.invoke(ModelTest.kt:51)
	at net.gtod.cusoon.integrationtest.ModelTest$1$1.invoke(ModelTest.kt:40)
	at org.jetbrains.spek.engine.Scope$Test.execute(Scope.kt:106)
	at org.jetbrains.spek.engine.Scope$Test.execute(Scope.kt:83)
	at org.junit.platform.engine.support.hierarchical.Node.execute(Node.java:94)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.lambda$execute$1(HierarchicalTestExecutor.java:81)
	at org.junit.platform.engine.support.hierarchical.SingleTestExecutor.executeSafely(SingleTestExecutor.java:66)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:76)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.lambda$execute$1(HierarchicalTestExecutor.java:91)
	at org.junit.platform.engine.support.hierarchical.SingleTestExecutor.executeSafely(SingleTestExecutor.java:66)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:76)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.lambda$execute$1(HierarchicalTestExecutor.java:91)
	at org.junit.platform.engine.support.hierarchical.SingleTestExecutor.executeSafely(SingleTestExecutor.java:66)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:76)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:51)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:43)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:87)
	at org.jetbrains.spek.tooling.runner.junit.JUnitPlatformSpekRunner.run(JUnitPlatformSpekRunner.kt:107)
	at org.jetbrains.spek.tooling.MainKt.main(Main.kt:58)
Caused by: java.sql.SQLException: Error while reading field: "cusoon"."foo"."price", at JDBC index: 1
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1714)
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.operate(CursorImpl.java:1674)
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.operate(CursorImpl.java:1639)
	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:122)
	at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1603)
	... 30 more
Caused by: java.lang.NumberFormatException
	at java.math.BigDecimal.<init>(BigDecimal.java:494)
	at java.math.BigDecimal.<init>(BigDecimal.java:383)
	at java.math.BigDecimal.<init>(BigDecimal.java:806)
	at org.jooq.impl.DefaultBinding.pgFromString(DefaultBinding.java:2304)
	at org.jooq.impl.DefaultBinding.pgSetValue(DefaultBinding.java:2549)
	at org.jooq.impl.DefaultBinding$2.operate(DefaultBinding.java:2439)
	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:122)
	at org.jooq.impl.DefaultBinding.pgNewRecord(DefaultBinding.java:2432)
	at org.jooq.impl.DefaultBinding.get(DefaultBinding.java:1632)
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1704)
```

[Lukas Eder]

Thank you very much for your report. I can reproduce this issue:

https://github.com/jOOQ/jOOQ/issues/6850

Will look into this soon,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hmm, this will be tricky without actually supporting the money type formally (https://github.com/jOOQ/jOOQ/issues/4364).

The PostgreSQL JDBC driver will "toString" the monetary amount, which produces a locale-dependent value. This is hard to parse back again into jOOQ's current BigDecimal mapping. Will need to think about this a bit more thoroughly. Further discussion directly on the issue #6850

[Lukas Eder]

... the more I'm reading about this money type, the more I'm convinced it should not be used at all:

https://stackoverflow.com/a/18170030/521799

The decimal precision is fixed by the server's relevant lc_monetary setting, but what if that setting changes? Will we lose precision, suddenly? What if we're in Switzerland (0.## precision) but we want to trade with stocks (often usually 0.#### precision)? Is it still an appropriate type?

Given that not even the JDBC driver correctly handles the money type after a simple test (pardon my German):

ResultSet rs = s.executeQuery("SELECT 1234.50::money");

rs.next();

rs.getObject(1);

org.postgresql.util.PSQLException: Unzulässiger Wert für den Typ double : CHF 1'234.50.

at org.postgresql.jdbc.PgResultSet.toDouble(PgResultSet.java:2921)

at org.postgresql.jdbc.PgResultSet.getDouble(PgResultSet.java:2338)

at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:193)

at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2589)

at Postgres.main(Postgres.java:28)

I'm getting to the conclusion that we should unsupport the money type and recommend not to use it. Nevertheless, you can always implement your own data type binding for top-level money type usage:

https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

Which will probably, unfortunately, not work if your type is inside of a UDT

So, I'm afraid, I won't have a solution here.

Lukas

[goo...@gtod.net]

Thanks for looking at this Lukas, I quite agree.  In fact we have now switched from NUMERIC to storing cents in an INTEGER...