Jooq: Add support for UPDATE .. RETURNING in MySQL

[hepin1989]

Expected behavior and actual behavior:

returning should work just as do a select

Steps to reproduce the problem:

and I have to make use of code below to make it work

this can't give me the result too:),
the returning value is null.

Versions:

• jOOQ: 3.10.1
• Java:1.8.0
• Database (include vendor):MySQL
• JDBC Driver (include name if inofficial driver):

[lukaseder]

Thank you very much for your report. UPDATE .. RETURNING is currently only supported on databases that have native support for this feature. This can be seen in the Javadoc, which has a @Support annotation for this purpose:

@Support(value={DB2,FIREBIRD,ORACLE,POSTGRES})
UpdateResultStep<R> returning(Field<?>... fields)

https://www.jooq.org/javadoc/latest/org/jooq/UpdateReturningStep.html#returning-org.jooq.Field...-

[hepin1989]

But I think if it's not supported directly by the database, could we emulate it?

[lukaseder]

Yes, that's why I renamed the issue to investigate the options. I'm not too optimistic for MySQL, though...

[chrisawad]

Given that INSERT .. RETURNING works for MySQL, I would think UPDATE .. RETURNING wouldn't be too much of an add. They would both use the same identity keys if using generated data, correct?

Adding a WHERE to the end of the UPDATE to set LAST_INSERT_ID(identity_key) should allow LAST_INSERT_ID() to provide the identity of the updated row:

UPDATE table
    SET key = val
    WHERE condition
        AND LAST_INSERT_ID(identity_key) 

It would then be the same code as the RETURNING part of INSERT .. RETURNING for MySQL.

[chrisawad]

Based on my analysis above, this works as a temporary hack:

return ctx.update(TABLE)
    .set(TABLE.FIELD, value)
    .where(TABLE.ID.eq(id))
    **.and("LAST_INSERT_ID(id)")**
    .returning()
    .fetchOptional();

As long as the last_insert_id is set, returning() will work

[lukaseder]

@chrisawad Thanks for your comments. I'm not sure how this is going to help here. I mean, your suggestion would just limit the number of rows being updated, with respect to a previous INSERT statement, which has nothing to do with PostgreSQL's UPDATE .. RETURNING syntax. What might I be missing?

Here's a PostgreSQL example:

create table t (i int, j int);
create function t1 () returns trigger as $$ begin new.j = new.i; return new; end $$ language plpgsql;
create trigger t2 before insert or update on t for each row execute procedure t1 ();
insert into t (i) values (1) returning *;
update t set i = 2 returning *;

Yielding

i|j|
-|-|
1|1|

i|j|
-|-|
2|2|

How can that be achieved with LAST_INSERT_ID()? Alternatively, what are you really trying to achieve here?

[chrisawad]

Unless I misunderstood the original poster, they wanted UPDATE...RETURNING to return the record that was updated when using a MySQL delegate. Since MYSQL doesn't support UPDATE...RETURNING, like PostgreSQL, the way to emulate returning the same record that was updated would be as follows:

For mysql to return the updated record, adding where LAST_INSERT_ID(identity) to the execute sql would prep the returning() function to return the updated record. This doesn't limit anything because it returns non-zero so the where condition still passes. It just preps the updated id for later queries.

I would suggest an improvement where updates that use the returning function with a mysql delegate, add in where LAST_INSERT_ID(identity), making returning() useful for mysql.

[lukaseder]

@chrisawad Thanks for your message. I really fail to see how last_insert_id() will produce what you're thinking of here. Can you please show me some example SQL / JDBC code that does exactly what I've done with PostgreSQL here: https://github.com/jOOQ/jOOQ/issues/6865#issuecomment-541588932

I'd love to see an example, because I really fail to see how we can access arbitrary updated records in MySQL, after the update. Maybe, work with this example:

create table t (i int not null primary key auto_increment, j int);
insert into t (j) values (1), (2), (3);
update t set j = 3 where j <= 2; -- How to achieve RETURNING * semantics here?

[guozhao265]

@lukaseder can jooq support for UPDATE .. RETURNING in MySQL? or have other way? I also this needs

[lukaseder]

@guozhao265 Thanks for your comment. Of course this is a direly needed feature in MySQL, but as you can see from my many comments in this issue (including e.g. https://github.com/jOOQ/jOOQ/issues/6865#issuecomment-549261051), I don't think it is possible, currently.