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:
Table A:
| Column Name | Type |
|---|---|
| ID | Interger |
| NAME | Varchar |
Table B:
| Column Name | Type |
|---|---|
| ID | Integer |
| A_ID | Integer References (Table A) ID |
| NAME | Varchar |
When doing the following query everything work as expected.
SELECT a.NAME, b.NAME FROM A a JOIN B b ON b.A_ID = a.IDBut when using JOOQ and you want to do a result count, it wraps the query as follow:
SELECT COUNT(*) FROM (SELECT a.NAME, b.NAME FROM A a JOIN B b ON b.A_ID = a.ID) AS qThis 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:
SELECT COUNT(*) FROM (SELECT a.NAME AS name1, b.NAME AS name2 FROM A a JOIN B b ON b.A_ID = a.ID) AS qWeird thing is is works perfect in PostgreSQL without the aliases, just another reason I like PostgreSQL. Still need to test in Firebird DB.