Wrong cast generated in PostgreSQL when using plain SQL with UPDATE statements and null literals

[lukaseder]

As reported on the user group by @niwinz:

---

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.

---

See also:

• https://groups.google.com/forum/#!topic/jooq-user/nPLMn0Ry6f8
• NULL values incorrectly being cast to varchar for pgsql dialect funcool/suricatta#13

[wouterh-dev]

So is it impossible to set fields to NULL currently?

[lukaseder]

@wouterhund You can certainly set fields to null. Are you using Suricatta? This issue is really mostly related to Suricatta...

[wouterh-dev]

@lukaseder I am not, I am using a custom Binding class. However, I am not trying to reproduce the error I got earlier and am unable to do so. Very strange.

The output I got was

2017-06-02 17:16:22.795 DEBUG 13381 --- [http-nio-1234-exec-1] org.jooq.tools.LoggerListener            : Executing query          : update "subscription" set "start_date" = cast(? as timestamp), "end_date" = cast(? as varchar), "changed_date" = cast(? as varchar), "created_date" = cast(? as varchar) where "subscription"."id" = ?
2017-06-02 17:16:22.795 DEBUG 13381 --- [http-nio-1234-exec-1] org.jooq.tools.LoggerListener            : -> with bind values      : update "subscription" set "start_date" = timestamp '2013-01-02 00:02:00.0', "end_date" = null, "changed_date" = null, "created_date" = null where "subscription"."id" = 35

(Removed a few sensitive fields from the queries manually)

It's odd that there's a cast to varchar for the null fields, but now everything is correctly cast to timestamp. Perhaps in my case it was related to a code generation fluke of sorts. I'll report back if I can pin it down.

I'm using the generated Record classes from plain old Java.

[lukaseder]

@wouterhund Thanks for the feedback. Would you mind creating a new issue, then? Can you please include the exact code that you're using that produces the problems you're having?