When generating table alias names, if the column in a @Where clause starts with an underscore, then the alias for the table is omitted from the query.
Column column1 is missing the lines1 alias.
Where this is really a problem is when you have a relationship like the following:
Both _column1 are missing their alias and cause the following exception:
SQL error or missing database (ambiguous column name: _column1)
Most people don't use underscore's in their table names. I am working with a database where ALL primary keys to the tables start with underscore and I have to jump through modeling hoops in order to get these to work.
Please see the github repository dedicated to this issue with multiple examples:
This issue might be linked to HHH-2022, and but seems to be a distinct issue on its own.
Hibernate-core, Postgres, Sqlite
So I found this:
sqlWhereString = "( _column1 = '14' and column2 = '25')";
placeholder = "$PlaceHolder$";
dialect = org.hibernate.dialect.SQLiteDialect
functionRegistry = org.hibernate.dialect.function.SQLFunctionRegistry
ret = renderWhereStringTemplate(String sqlWhereString, placeholder, dialect, functionRegistry)
ret = "( _column1 = '14' and $PlaceHolder$.column2 = '25')"
which is not correct, should be:
ret = "( $PlaceHolder$._column1 = '14' and $PlaceHolder$.column2 = '25')"
isIdentifier("_column1") = false
isIdentifier("column2") = true
I would assume that this should not be used for column names?
Or is the solution to use backticks in the column names?
IIUC, ANSI SQL 92 does not allow an underscore as the first character.
Putting backticks around the column name should work.