using backticks generates incorrect SQL

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

In using backticks to quote an SQL Server reserved word (in an arbitrary where clause on a collection mapping), and I have found the the SQL generated by Hibernate changes.
The immediate obvious solution is to remove the backticks and change the table name, but surely this is a bug? Has anyone else come across this?
I include the generated SQL both with and without the backticks:

Hibernate version: 3.1

Mapping documents:
joined-subclasses:
Person<-Advisor<-User (User extends Advisor which extends Person)

Code:

<class name="Person" table="person">
       <joined-subclass name="Advisor" table="advisor">
            <key column="person_id" />
          <joined-subclass name="User" table="`user`">
            <key column="advisor_id" />
          </joined-subclass>
        </joined-subclass>
</class>

a Company has a getAdvisors() and getUsers() method:

Code:

<class name="Company" table="company">
        <bag name="advisors" inverse="true" cascade="all" where="id IN (SELECT a.contact_id FROM advisor a)" >
            <key column="company_id"/>
            <one-to-many class="Person"/> <!-- should be class Advisor, but company_id is field on the "person" table -->
      </bag>
        <bag name="users" inverse="true" cascade="all" where="id IN (SELECT u.advisor_id FROM `user` u)" > <!-- note the backticks: "user" is a reserved word for SQL Server -->
            <key column="company_id"/>
            <one-to-many class="Person"/>  <!-- should be class User, but company_id is a field on the "person" table -->
      </bag>
</class>

Name and version of the database you are using: MS SQL Server

The generated SQL (show_sql=true):
SQL generated with backticks on the where clause id "IN (SELECT u.advisor_id FROM `user` u)" (mapping for getUsers() method) :

Code:

select users0_.company_id as company2_2_,
users0_.id as id2_,
users0_.id as id3_1_,
users0_.company_id as company2_3_1_,
users0_.first_name as first3_3_1_,
users0_.last_name as last4_3_1_,
users0_2_.logon as logon6_1_,
users0_2_.password as password6_1_,
case
  when users0_2_.advisor_id is not null then 2
  when users0_1_.person_id is not null then 1
  when users0_.id is not null then 0
end as clazz_1_,
from person users0_
left outer join advisor users0_1_ on users0_.id=users0_1_.person_id
left outer join [user] users0_2_ on users0_.id=users0_2_.advisor_id
left outer join address address1_ on users0_.address_id=address1_.id
where users0_.id IN (SELECT u.advisor_id FROM users0_.[user] u) -- Why is it refering to users0_.[user]? Removing backticks solves this, but backticks are needed
and users0_.company_id=?

SQL generated without backticks on the where clause id "IN (SELECT u.advisor_id FROM user u)" :

Code:

select users0_.company_id as company2_2_,
users0_.id as id2_,
users0_.id as id3_1_,
users0_.company_id as company2_3_1_,
users0_.first_name as first3_3_1_,
users0_.last_name as last4_3_1_,
users0_2_.logon as logon6_1_,
users0_2_.password as password6_1_,
case
  when users0_2_.advisor_id is not null then 2
  when users0_1_.person_id is not null then 1
  when users0_.id is not null then 0
end as clazz_1_,
from person users0_
left outer join advisor users0_1_ on users0_.id=users0_1_.person_id
left outer join [user] users0_2_ on users0_.id=users0_2_.advisor_id
left outer join address address1_ on users0_.address_id=address1_.id
where  users0_.id IN (SELECT u.advisor_id FROM user u) -- SQL is correct, but "user" is reserved word
and users0_.company_id=?

Another solution could be:
If I could use joins instead of left outer joins, I wouldn't need to filter the results with a where clause. How do I get a set/bag mapping to use inner joins instead of outer joins?

Also, as this is a problem of polymorphism, I wondered if the class level attribute polymorphism="implicit|explicit" could help, but I find documentation on this attribute a little sparse. Are there any good articles/FAQ response?

Many thanks,

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

This doesn't answer your main query, but it will solve your problem: to get your server to not parse "user" as a keyword, you can refer to it by its qualified name: that is, use <schema>.user. For SQLServer, that would be dbo.user.

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

Thank you Tenwit for your suggestion. Unfortunately it doesn't work :-(
Here are the results:
Using dbo.user

Code:

where="id IN (SELECT u.advisor_id FROM dbo.user u)"

generates

Code:

where users0_.id IN (SELECT u.advisor_id FROM dbo.user u)

results in

Code:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near the keyword 'user'.

Looks like when refering to dbo.user you still need to escape the keyword, so you still need to refer to dbo.[user]. Ok, let's try that:

using dbo.[user] (also tried using dbo.`user` - same results)

Code:

where="id IN (SELECT u.advisor_id FROM dbo.[user] u)"

generates

Code:

where users0_.id IN (SELECT u.advisor_id FROM dbo.users0_.[user] u)

results in

Code:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid object name 'dbo.users0_.user'.

It was a good suggestion though!

I think I'm going to report this as an actual bug...

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

I've submitted this to JIRA as report number 1511:
http://opensource2.atlassian.com/projec ... e/HHH-1511

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Ouch. It works when the keyword is "group". Nasty old Microsoft having different levels of keywordliness.
What happens if you change the name in the class mapping to "[user]"?

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

I haven't tried yet, but I don't rate the chances much, as this bug is happening on MySQL too. The only difference is the quoted table is `user` instead of [user]. This makes me think it is not that MS have different levels of "keywordliness", but actually a bug in hibernate.

If it worked for you, maybe you're using a different verison of hibernate or there is something else different in your config that made it work?

What version of hibernate are you using please?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I've used this technique in 2.1 and 3.0.5. Have a go a changing the table="user" to table="[user]" in your mapping, see what it produces.

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

Well, I already had

Code:

table="`user`"

in the mapping and changing this to

Code:

table="[user]"

did not change the behaviour, I'm afraid.
My where clause on the Company object is

Code:

where="id IN (SELECT u.advisor_id FROM [dbo].[user] u)"

and the error is

Code:

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find server 'users0_' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

because the generated SQL is:

Code:

where users0_.id IN (SELECT u.advisor_id FROM users0_.[dbo].users0_.[user] u)

I'm using Hibernate 3.1

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Double ouch. Options are becoming limited. Does your DBA allow you to create views in your DB? And does Oracle support updateable views? You could create a view that is the User table and nothing else, and just use it to effectivley rename the table to something that isn't a keyword (UserTable sounds good).

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

tenwit wrote:

You could create a view that is the User table and nothing else, and just use it to effectivley rename the table to something that isn't a keyword (UserTable sounds good).

Yes indeed. As I said in my first post:

stewart.cambridge wrote:

The immediate obvious solution is to remove the backticks and change the table name

I will take this as confirmation that there is no other workaround, pending the bug itself being fixed. I guess by using a view, it'll be easy to revert back to using the table proper when the latest and greatest version of Hibernate is installed.

I've been working around using this up until now:

Code:

 /**
  * TODO: Fix this where clause:
  * where="id IN (SELECT u.advisor_id FROM [dbo].[user] u)" lazy="false"
  * @hib/ernate.bag inverse="true" cascade="all"
  * @hib/ernate.collection-key column="company_id"
  * @hib/ernate.collection-one-to-many class="Person"
  */
  public List getUsers()
  {
    return session.createCriteria(User.class).add(Restrictions.eq("company", this)).list();
    //return this.users;
  }
/*
  public void setUsers(List users)
  {
    this.users = users;
  }
  */

Thank you for your time and help Tenwit.