Thank you very much for your report and for your patience. I finally got around to reproducing this issue. When unknown data types are encountered, jOOQ generates (in PostgreSQL) the type "USER-DEFINED", which is the reported type from PostgreSQL's INFORMATION_SCHEMA.PARAMETERS.DATA_TYPE. This name is useless, we should generate the content from PARAMETERS.UDT_SCHEMA and PARAMETERS.UDT_NAME, instead, if available. This will improve the behaviour in the absence of a custom binding *and* in the presence of such a binding.
I have fixed this for jOOQ 3.11 through: https://github.com/jOOQ/jOOQ/issues/7547 Thanks again for your report. Lukas 2018-05-12 0:32 GMT+02:00 <[email protected]>: > I've been trying to use postgis with jooq and have run into a problem when > attempting to use postgis functions. I've defined a converter and binder > for the "geometry" data type and it appears to work fine in queries that > don't use postgis functions. > > For example, for the following table: > CREATE TABLE example ( > id BIGSERIAL PRIMARY KEY, > location GEOGRAPHY(POINT, 4326) NOT NULL > ); > > queries like these work fine: (dbSessionManager is just a thin wrapper > around DSLContext) > > fun createExample(location: Location): ExampleRecord { > return dbSessionManager.session() > .insertInto(EXAMPLE) > .set(EXAMPLE.LOCATION, location.convertToPoint()) > .returning() > .fetchOne() > } > > or > > fun getExampleByLocation(location: Location): ExampleRecord? { > return dbSessionManager.session() > .selectFrom(EXAMPLE) > .where(EXAMPLE.LOCATION.eq(location.convertToPoint())) > .limit(1) > .fetchOne() > } > > > But this query > fun getExampleInRadius(location: Location, radius: Double):ExampleRecord? > { > val routine = StDwithin3() > routine.set__1(EXAMPLE.LOCATION) > routine.set__2(location.convertToPoint()) > routine.set__3(radius) > return dbSessionManager.session() > .selectFrom(EXAMPLE) > .where(routine.asField()) > .limit(1) > .fetchOne() > } > fails with the following error: > > org.jooq.exception.DataAccessException: SQL [select "public"."example". > "id", "public"."example"."location" from "public"."example" where > "postgis"."st_dwithin"(cast("public"."example"."location" as USER-DEFINED > ), cast(?::geography as USER-DEFINED), cast(? as double precision)) limit > ?]; ERROR: syntax error at or near "USER" > > because for some reason jooq is trying to cast the location to > "USER-DEFINED" instead of geography. It appears that > `AbstractRoutine.pgArgNeedsCasting` > forces all parameters of overloaded functions to be cast, but I'm not sure > why its being cast to "USER-DEFINED" instead of "geography". Maybe I've set > up the converter/binder wrong, but its very close to what was recommended > here: https://groups.google.com/forum/#!topic/jooq-user/TBQZCPTCvnk > > In the interest of making this easier to debug, I've put together a very > stripped down version of my codebase that still exhibits this behavior. If > you feel like taking a look, it is here: https://github.com/amc6/ > jooq_postgis_bug_example > > The key files are: > > - db/migration/V2__add_postgis.sql > > <https://github.com/amc6/jooq_postgis_bug_example/blob/master/database/migrations/src/main/resources/db/migration/V2__add_postgis.sql> > - Contains the example table definitions and postgis extension > installation. > - The one non-standard thing I am doing here is installing postgis > into a separate schema, but that does not appear to affect the presence > of > this bug. > - com/sharespace/database/PostGisGeographyBinding.java > > <https://github.com/amc6/jooq_postgis_bug_example/blob/master/database/jooq/src/main/java/com/sharespace/database/PostGisGeographyBinding.java> > - Contains the Binding and Converter. Very similar to what was > suggested in https://groups.google.com/forum/#!topic/jooq-user/ > TBQZCPTCvnk. > - com/sharespace/persistence/Persister.kt > > <https://github.com/amc6/jooq_postgis_bug_example/blob/master/server/src/main/kotlin/com/sharespace/persistence/Persister.kt> > - com/sharespace/persistence/PersisterTest.kt > > <https://github.com/amc6/jooq_postgis_bug_example/blob/master/server/src/test/kotlin/com/sharespace/persistence/PersisterTest.kt> > - Contains a few tests to show exactly what works and what does not. > In particular, use the function ST_DWithin using jooq does not work, but > using ST_DWithin as raw sql does work. > - There are a few other miscellaneous files that are probably > unnecessary for such a small project, but are leftover from me paring down > a full project/repo. I'm fairly confident that this bug isn't a result of > them. > > Any insight would be greatly appreciated! > > Best, > Adam > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
