Regression when using INSERT .. RETURNING pre MariaDB 10.5

[oflege]

Expected behavior

for a simple table like this

CREATE TABLE avro_schema (
    id INTEGER NOT NULL AUTO_INCREMENT, md5 VARCHAR(32) NOT NULL, schema_json TEXT NOT NULL
);

and jooq configured w/ spring.jooq.sql-dialect=MARIADB_10_3
using the following code

 val r = context().newRecord(AVRO_SCHEMA).setMd5("").setSchemaJson("")
 r.insert()

inserts a new record and the record's id is available from r.getId()
(works as expected in jooq 3.17.8)

Actual behavior

bad SQL grammar [insert into avro_schema (md5, schema_json) values (?, ?) returning id]; nested exception is java.sql.SQLSyntaxErrorException: (conn=45420) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'returning id' at line 1

According to https://mariadb.com/kb/en/insertreturning/ , insert...returning is only available in mariadb 10.5 and later

Steps to reproduce the problem

use mariadb v10.4, generated code for table with auto generated key, configure jooq with mariadb dialect before 10.5, and try to insert a new record.

jOOQ Version

jOOQ Prof. 3.18.0, 3.18.1

Database product and version

10.4.28-MariaDB-1:10.4.28+maria~ubu2004

Java Version

No response

OS Version

No response

JDBC driver name and version (include name if unofficial driver)

No response

[lukaseder]

Thanks a lot for your report. Before I investigate, does Spring Boot correctly configure your versioned dialect to SQLDialect.MARIADB_10_3?

As a workaround, you could configure Settings.returnIdentityOnUpdatableRecord to be false to turn off this behaviour.

[lukaseder]

Before I investigate, does Spring Boot correctly configure your versioned dialect to SQLDialect.MARIADB_10_3?

Ah, probably no need. It looks as though this change introduced a regression:
39935d1

[oflege]

definitely SQLDialect.MARIADB_10_3, suggested workaround fixes sql but we need/access the identity id from inserted records in numerous places, so not feasible for us.

[lukaseder]

The integration tests do fail. There has not been a notification email for the failure of these dialect versions. Will investigate why that happened.

The fix is on the way and will be released later today, given the severity.

[lukaseder]

Fixed in jOOQ 3.19.0 and 3.18.2 (#14873). Thanks again for your report!

[lukaseder]

The patch releases have been published yesterday.