Jooq: Emulate SET [ ROW ] = [ ROW ] syntax on all databases

[dustContributor]

Expected behavior and actual behavior:

Hi! When you read the docs of 'set' in update(TABLE).set(DSL.row(fields), DSL.row(values)), it says:

Specify a multi-column set clause for the UPDATE statement.
This is emulated using a subquery for the value, where row value expressions aren't supported.

But no such emulation occurs. For MySQL dialect I get:

update `TABLE` set (`FLD1`, `FLD2`, `FLD3`) = ('val1', 'val2', 'val3') where `TABLE`.`ID` = 1

Which isn't supported on MySQL as far as I am aware, so the driver throws an exception.

Now, I believe this line here might be related to the problem.

That check always returns true in this case, regardless of the dialect:

if (multiValue != null && !asList().contains(ctx.family())) {
                ctx.visit(multiValue);
            }

Because it creates an empty list and checks if the family is present in it, which will never be true.

While I see this check in 3.10 branch (the version I am using), I dont see it anymore in the master branch, because of a recent commit that did a lot of cleanup removing many of those "asList" that were called just to compare one element in a bunch of cases.

Although, it seems to be an unintended consequence of the cleanup that the "asList" was removed as part of the if.

Steps to reproduce the problem:

Just issue a row update in the form of:

ctx.update(TABLE)
  .set(DSL.row(FLD1, FLD2), DSL.row("val1", "val2))
  .where(TABLE.ID.eq(id))
  .execute();

On a MySQL database, any version should do I believe.

Versions:

• jOOQ: 3.10.2
• Java: 8u111
• Database (include vendor): MySQL 5.6.21
• JDBC Driver: MySQL Connector 5.1.43

[lukaseder]

Thank you very much for your report. Perhaps, the Javadoc's intent was not very clear. In Oracle and Ingres, which do not support

SET (a, b, c) = (1, 2, 3)

This can be emulated using a subquery:

SET (a, b, c) = (SELECT 1, 2, 3 FROM dual)

Currently, we don't emulate this feature by unwrapping the row into three distinct single-column set specifications, but there's not really any reason why we shouldn't.

Will look into this for jOOQ 3.11

[lukaseder]

Side-note:

removing many of those "asList" that were called just to compare one element in a bunch of cases.

Oh, those lists may have more than one element in the commercial distributions... :) But I admit they may look interesting in the Open Source Edition, where the commercial dialects are removed.

[dustContributor]

Currently, we don't emulate this feature by unwrapping the row into three distinct single-column set specifications, but there's not really any reason why we shouldn't.

Ah right, MySQL doesn't supports the sub-query thingy either.

Will look into this for jOOQ 3.11

Sweet, that'd be nice since the API is much nicer that way. Thanks!

Oh, those lists may have more than one element in the commercial distributions... :)

Right, makes sense. I see you replaced them all for static final EnumSets which is way better anyway.

Well, I've used jOOQ briefly and so far it has been a really nice experience, I'll be waiting for 3.11 then :smile:

[lukaseder]

This is implemented for jOOQ 3.11. Thanks again for the suggestion!

[dustContributor]

Awesome!