Hi Lukas, I understand, but I have a question regarding that issues:
I understand that inference of a type at rumtime can be a problem but null is almost in all cases is null (I mean no casting is necessary). Maybe a partial fix to that have a special case for null in jOOQ, and forward it to the database as null without any special casting. Maybe I'm missing something of jOOQ internals about this... Also it seems that this issue appears on the last major release of jOOQ, I have't observed that in previous releases (maybe I'm wrong, because I'm not extensive user of the DSL, personally I use jOOQ just for execute plain sql). In any case, thanks for your answer and the good work at jOOQ. Regards. Andrey On Sun, Jun 5, 2016 at 8:45 PM, Lukas Eder <[email protected]> wrote: > 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. > -- 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.
