Hit a interesting issue today using MariaDB with JOOQ. I have a project that can be configured to use different underlying databases depending on client requirements and I use JOOQ to simplify this for me. One of the tables are structured as follow:
|A_ID||Integer References (Table A) ID|
When doing the following query everything work as expected.
But when using JOOQ and you want to do a result count, it wraps the query as follow:
This will cause #1060 – Duplicate column name ‘NAME’.
The problem is due to MySQL/MariaDB requiring “Any columns in the subquery select list must have unique names.”
Easy workaround is to use aliases for the columns that solves the problem as follow:
Weird thing is is works perfect in PostgreSQL without the aliases, just another reason I like PostgreSQL. Still need to test in Firebird DB.