Quoted identifier bug?

LostLeviathan · **Joined:** Sun Mar 23, 2008 3:46 pm **Posts:** 3

Hi,

I'm running hbm2ddl on an entity (corresponding to a table named "Children" for this example) with the following annotation:

Code:

@JoinColumn(name = "`familyId`", nullable = false)
@ManyToOne
@ForeignKey(name = "`fkFamily`")
@Index(name = "`familyIndex`")
private Family family;

When I do, I get the following error:

Quote:

[hibernatetool] An exception occurred while running exporter #2:hbm2ddl (Generates database schema)
[hibernatetool] To get the full stack trace run ant with -verbose
[hibernatetool] org.hibernate.MappingException: Unable to find logical column name from physical name familyId in table Children

The error goes away if I remove the backticks from around familyId, or if I remove the @Index annotation (even if I add the same index via the org.hibernate.annotations.Table annotation).

Perhaps more disturbingly, the @Index annotation doesn't replace backticks with quotes. If I use these annotations:

Code:

@JoinColumn(name = "familyId", nullable = false)
@ManyToOne
@ForeignKey(name = "`fkFamily`")
@Index(name = "`familyIndex`")
private Family family;

then I get this:

Quote:

create index `familyIndex` on "Children" (family);

(Those are backticks around familyIndex, which would be troublesome if I tried to execute the code.) Are other people able to use backticks to quote their index identifiers? It's not a big deal, but it's my aesthetic preference. I'm using Hibernate Tools v3.20 GA and org.hibernate.dialect.PostgreSQLDialect.

LostLeviathan · **Joined:** Sun Mar 23, 2008 3:46 pm **Posts:** 3

Update: I see that the lack of backtick processing is a known limitation of the @Index annotation, which doesn't go through NamingStrategy the way that table names do. There is an open ticket on this:
http://opensource.atlassian.com/projects/hibernate/browse/HB-892

The error comes from the same cause: The @Index annotation bypasses the NamingStrategy, so if the NamingStrategy alters the column name that the index refers to (e.g. by replacing backticks with quotes), then the @Index annotation can't find the column. The annotation also seems to ignore the columnNames attribute. So, overriding NamingStrategy is insufficient to solve the problem.

I hope someone will fix this behavior. However, I should emphasize for anyone encountering the same problem that there is a workaround: Rather than using @Index at the desired property, use this annotation on the entity:

Code:

@org.hibernate.annotations.Table(appliesTo = "Children", indexes = { @Index(name = "familyIndex", columnNames = { "familyId" }) })

It's not very pretty, but it works.

max · **Posted:** Tue Mar 25, 2008 1:57 pm

btw. why are you using backticks when you don't need them ? (I see no spaces or special characters in there?)

LostLeviathan · **Joined:** Sun Mar 23, 2008 3:46 pm **Posts:** 3

If I don't use backticks, then everything would become lower case; I'd find camelCase easier to read when maintaining the database. Also, more importantly, backticks let me use keywords such as "user" as column names.

max · **Posted:** Wed Mar 26, 2008 3:13 am

ah got ya!