Strange behavior when setting a field to NULL in UPDATE statement

[Andrey Antukh]

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 - Андрей Антух - <ni...@niwi.nz>

http://www.niwi.nz

https://github.com/niwinz

[Lukas Eder]

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

--
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+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andrey Antukh]

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

[Lukas Eder]

2016-06-05 21:10 GMT+03:00 Andrey Antukh <ni...@niwi.nz>:

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...

Not every database will agree with this :) and unfortunately, changing these things inside of jOOQ might yield difficult to foresee regressions, so I'm not removing the cast lightly, even if I can see that it is clearly wrong in your case. There's a pending feature request to let users turn off all of these bind variable casts at their own risk, but it has not been implemented yet:

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

I have registered an issue for this. I'm afraid I cannot give you any further feedback on how to work around this right now, I'll have to investigate more deeply first:

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

 

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).

That could be. In PostgreSQL, casts are necessary:

- For some specific data types

- For function calls (to disambiguate overloads)