ALTER TABLE .. RENAME TO .. should fully qualify tables if supported by the database

[lukaseder]

Currently, the ALTER TABLE schema.table1 RENAME TO schema.table2 statement does not qualify the table2 table, because that's not supported in a few databases. We should always qualify the table if such qualification is provided, as the semantics of the statement may change depending on what the session's default schema / database may be.

---

See: https://stackoverflow.com/q/53210516/521799

[do4gr]

It seems like now the second table is always qualified, which leads to a failure on Postgres with 3.11.9 for us. MySQL was failing before because of the missing qualification but is working now since the table2 is now qualified.

This is what is now generated for the Postgres Profile:
alter table "schema1"."table1" rename to "schama1"."table2"

[lukaseder]

@do4gr Thanks for your comment. How are you calling the API to get this behaviour? Using generated code? The workaround is obviously to create an unqualified table reference, e.g. by calling DSL.table(Name)...

[do4gr]

Thanks for the quick response!

This is the jooq call we're making: sql.alterTable(table(name(schema1, table1))).renameTo(name(schema1, table2))

This generates alter table "schema1"."table1" rename to "schema1"."table2" on both MySQL and Postgres (different quotation of course). But Postgres does not accept the qualification on the rename to part and throws a syntax error.

So I think the correct behaviour would be to qualify the renameTo on MySql but not on Postgres. We would like to have the same jooq call for different SQL variants and I was hoping this bug fix would enable this. Having separate jooq calls for different dialects is of course a possibility, but something we would like to avoid since we are passing in different dialects at runtime and really love that jooq in most cases allows us to still only have one definition per statement.

[lukaseder]

Thanks for the additional comments. Yes of course, I agree. The same statement should work on all RDBMS, if it can be reasonably emulated. I was just pointing out a workaround for the time being, and also for others who may stumble upon this.

The issue title is quite clear, and according to that title, this issue was not implemented correctly.

[lukaseder]

Fixed for jOOQ 3.12. Backport will be scheduled again under #8043, for 3.11.10