Why am I receiving NonUniqueDiscoveredSqlAliasException?

The database have book and book versions. I need to get only books which:

The version it is the latest version or v.generated_from another is null (it means that isn't a stable version)
Get by a specific type

SELECT b.id, b.book_name, v.id, v.majorVersion, v.minorVersion, v.generated_from_another IS NULL as isNotStable
FROM book b
         INNER JOIN book_version v ON b.id = v.book_id
WHERE b.type = :typeId
  and (v.id in (SELECT max(v.id)
                FROM book b
                         INNER JOIN book_version v ON b.id = v.book_id
                WHERE v.generated_from_another IS NULL
                  AND b.type = :typeId
                GROUP BY b.id) or v.generated_from_another is not null)
ORDER BY b.book_name, v.majorVersion DESC, v.minorVersion DESC;

It works well in mysql but I'm receiving

Caused by: org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [id] during auto-discovery of a native-sql query at org.hibernate.loader.custom.CustomLoader.validateAliases(CustomLoader.java:520) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final] at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:497) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final] at org.hibernate.loader.Loader.preprocessResultSet(Loader.java:2343) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final] at org.hibernate.loader.Loader.getResultSet(Loader.java:2299) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]

What is wrong?

Solution

As your query:

SELECT 
b.id <----ID, 
b.book_name, 
v.id <----ID, 
v.majorVersion, 
v.minorVersion, 
v.generated_from_another IS NULL as isNotStable 
FROM book b 
INNER JOIN book_version v ON b.id = v.book_id 
WHERE b.type = :typeId 
AND 
(v.id in (
          SELECT 
          max(v.id) 
          FROM book b 
          INNER JOIN book_version v ON b.id = v.book_id 
WHERE v.generated_from_another IS NULL 
AND b.type = :typeId GROUP BY b.id) 
or v.generated_from_another is not null) 
ORDER BY b.book_name, 
v.majorVersion DESC, 
v.minorVersion DESC;

Is fetching two columns with alias id, you are getting the exception.

If you look at your error log properly, you can see it Encountered a duplicated sql alias [id] during auto-discovery of a native-sql

You can do something like:

SELECT 
b.id AS bookId, 
b.book_name, 
v.id AS versionId, 
...

SELECT 
b.id  bookId, 
b.book_name, 
v.id  versionId, 
...

This way the error can be avoided.