Hi Andrey, Thank you very much for your enquiry.
There are a couple of "weird" casts generated by jOOQ, mostly in SQL dialects that have issues inferring the type of a bind variable at runtime. PostgreSQL unfortunately is one of these databases. Mostly, type inference works perfectly, but sometimes it doesn't. In this case, it would work of course, as the SET col = ? clause of the UPDATE statement enforces a type upon the bind variable, but jOOQ currently isn't good enough to know this. What's more, this issue arises only when using plain SQL and null values, in case of which jOOQ doesn't know the bind variable type. I'd definitely say this is a bug in this particular case. Unfortunately, I currently don't see a workaround for you, unless you could do without using plain SQL? Best Regards, Lukas Related issues are: - https://github.com/jOOQ/jOOQ/issues/1749 - https://github.com/jOOQ/jOOQ/issues/1735 2016-05-31 10:54 GMT+02:00 Andrey Antukh <[email protected]>: > Hello, > > I found a very strange cast to character varying in UPDATE statement when > setting a field to NULL with postgresql dialect. When a default or mysql > dialect is used everything works as expected. > > The reproducing code is (clojure + suricatta): > > user=> (fmt/sqlvec (-> (dsl/update :foo) (dsl/set :foo nil))) > ["update foo set foo = ?" nil] > user=> (fmt/sqlvec (-> (dsl/update :foo) (dsl/set :foo nil)) {:dialect > :postgresql}) > ["update foo set foo = cast(? as varchar)" nil] > > That will translate to something like this in groovy/java: > > def update = DSL.update(DSL.table("foo")) > def conf = (new DefaultConfiguration()).set(org.jooq.SQLDialect.POSTGRES) > def ctx = DSL.using(conf) > ctx.render(update.set([((DSL/field "foo"))]: null])) > // => "update foo set foo = cast(? as varchar)" > > This behavior makes me imposible to set a field to NULL. I don't know if > is something related to Clojure<->java interop or is just a bug in jOOQ. > But my first impression is that something wrong is in postgresql dialect. > > Thanks. > Andrey > -- > Andrey Antukh - Андрей Антух - <[email protected]> > http://www.niwi.nz > https://github.com/niwinz > > -- > 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.
