We're updating the issue view to help you get more done. 

SQL alias generation strategy for join tables problematic for some databases

Description

When criteria is declared that references another entity (requires table join), generated SQL statement uses a random alias for the joined table: occasionally, the random string begins with an integer, and this is problematic for certain databases/versions.

For example, the following generated query statement (trimmed):

1 select this_.id as id1_47_1_ from users this_ inner join user_profile 3941463957x1_ on this_.iuser_id=3941463957x1_.id where (((((3941463957x1_.id=?))))) order by this_.id desc limit ?

Complains about "Syntax error at or near '.'"

Next invocation of criteria succeeds because generated alias is different (begins with alpha char). This causes intermittent errors on some platforms/stacks, which are difficult to diagnose.

Note: this particular error was encountered with Grails (GORM), Hibernate4 plugin + postgres, using grails criteria builder + aliases.

Environment

Hibernate: 5.0.1, postgres: 5.0.1, grails 3.1.0, plugin:org.grails.plugins:hibernate4:5.0.1

Status

Assignee

Unassigned

Reporter

David Rose

Fix versions

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.0.1

Priority

Minor