Select ... more than one column having same name

andrewcole · **Posted:** Mon Oct 25, 2004 12:08 pm

Hibernate version: 2.1.6

Mapping documents: (Simplified for ease of posting):

<hibernate-mapping>
<class name="ClassA" table="CLASS_A" mutable="false" lazy="true">
<id name="classAId" type="integer" column="CLASS_A_ID">
<generator class="assigned"/>
</id>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="ClassB" table="CLASS_B" mutable="false" lazy="true">
<composite-id name="compositeId" class="ClassBPK">
<key-property name="classAId" column="CLASS_A_ID" type="integer"/>
<key-property name="classBId" column="CLASS_B_ID" type="integer"/>
</composite-id>
<many-to-one name="classA" column="CLASS_A_ID" class="ClassA" outer-join="false" insert="false" update="false"/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Just trying to do a get(Class, Serializable) on ClassB, where the ID object is an instance of ClassBPK (which implements equals() and hashCode()).

Full stack trace of any exception that occurs:
Caused by: com.sybase.jdbc2.jdbc.SybSQLException: Select expression results in more than one column having same name. Column name '<the shared ID between ClassA & ClassB>' is specified more than once

at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2884)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2206)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
at com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1596)
at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1581)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:96)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:911)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:931)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
... 28 more

Name and version of the database you are using: Sybase 12.5.

In summary, I have two tables, one with a composite key, where one of the columns of the composite key is also a FK to the PK of the other table. As far as I can see, section 8.3.1 of Hibernate in Action describes precisely how to handle this, but I keep getting this error:

com.sybase.jdbc2.jdbc.SybSQLException: Select expression results in more than one column having same name. Column name '<shared ID col>' is specified more than once.

From earlier posting, it sounds like this is a Sybase only issue. Note, this is a database that I have absolutely no control over the schema, so changing the schema is not an option.

Any assistance would be much appreciated.

Thanks,
Andy

mosquito · **Joined:** Mon Oct 25, 2004 1:47 pm **Posts:** 3

I have been troubleshooting what has turned out to be the exact same scenario this morning! I am also using Sybase 12.5. Unfortunately, that cannot be changed on my end either.

andrewcole · **Posted:** Mon Oct 25, 2004 5:11 pm

Well, at least I know I'm not the only one with this problem! ;-)

Any assistance from the Hibernate team would be much appreciated, as when working with legacy/reference/thirdparty databases, I would imagine that having a composite primary key with one of the columns being a FK to another table is extemely common.

The documentation suggestes that Hibernate can handle this, but in practice (with Sybase at least) I haven't been able to get it to work.

This is really a key requirement for my usage of Hibernate. I'm begining to wonder if this is a Hibernate bug, but I would be very happy to be proved wrong on that one!

Thanks,
Andy

gavin · **Posted:** Mon Oct 25, 2004 5:13 pm

This is a known problem affecting only sybase (stupid limitation of sybase), which has been resolved in HB3.

andrewcole · **Posted:** Mon Oct 25, 2004 5:24 pm

Thanks Gavin.

However, we are looking to put Hibernate in to our production environment in the not too distant future, and certainly won't be able to consider looking at Hibernate 3 until it is production status, and probably not for some time after that.

I realize this may be asking a lot, but any chance this fix could be patched in to a 2.1.7 release? It would be a *huge* help, and would certainly help me make the case for using Hibernate more extensively.

Thanks,
Andy

christian · **Posted:** Mon Oct 25, 2004 5:26 pm

You have the source code for both, go wild.

andrewcole · **Posted:** Mon Oct 25, 2004 5:37 pm

Thanks again, however I don't have the first clue where to start looking for where this fix has been made, which classes it affects etc, and also it would be a tough case for me to make to release a custom patch to a "third-party" product in my environment, even if it is open source.

If this fix has been made to version 3, couldn't it be patched in to a new release of 2.1.x? It is an existing bug in the current production version after all, even if it is down to a Sybase limitation.

Thanks again for your help.

Andy

gavin · **Posted:** Mon Oct 25, 2004 5:49 pm

Actually, I *may* already have fixed this in 2.1 CVS. Not quite sure.

andrewcole · **Posted:** Mon Oct 25, 2004 5:52 pm

Cool! :-) Would you be able to confirm this, and also let me know if this will be released as a formal 2.1 release on the Hibernate website? I will only be able to use an "official" production version in my environment.

Cheers,
Andy

gavin · **Posted:** Mon Oct 25, 2004 5:53 pm

Try it out for yourself.

andrewcole · **Posted:** Mon Oct 25, 2004 6:09 pm

I can try it out, but even if it works I won;t be able to use it unless there is an official version? Can I make an official version of Hibernate? I think not.

The road map page says that for version 2.1: "This branch of Hibernate has been stable since end 2003. We continue providing bugfixes and also integrate or backport minor new features throughout the next years."

What is the precedure for getting bugfixes integrated in to a official release?

christian · **Posted:** Tue Oct 26, 2004 2:30 am

The procedure is to wait for the next minor release. So far, we had releases every 2 months. The 2.1.7 release is close, maybe in the next 3 weeks.

andrewcole · **Posted:** Tue Oct 26, 2004 3:53 am

Great - thanks guys! I'll look forward to the 2.1.7 release :-)

andrewcole · **Posted:** Mon Nov 01, 2004 10:40 am

Hi,

I have written a JUnit test case that exposes this Sybase only issue with Hibernate 2.1.6. The other day I downloaded the Hibernate2 nightly snapshot and ran my unit tests with it, and WooHoo! - they all passed.

Now I'm keen to get this change in to my production env., so I was wondering if anyone from the Hibernate team could indicate when the next 2.1 (presumably 2.1.7) release is likely to be?

Thanks,

Andy

dlkpochtaws · **Joined:** Tue Nov 09, 2004 10:52 am **Posts:** 2

gavin wrote:

This is a known problem affecting only sybase (stupid limitation of sybase), which has been resolved in HB3.

Hi everybody.
Unfortunately this problem also affects Oracle (Oracle9i 9.2.0.1.0 in my case). It appears, if you apply Criteria.setMaxResults(int) to the search criteria. In this case, Hibernate generates following SQL:

select * from (YOUR SQL HERE) where rownum_ > ?

And "YOUR SQL" contain 2 columns with same name. So Oracle complains " ORA-00918: column ambiguously defined".

After some hours of researching the problem, it seems to me I've found a workaround, at least for Oracle. There is a code in the class "Alias.java"

Code:

public String toAliasString(String sqlIdentifier) {
      char begin = sqlIdentifier.charAt(0);
      int quoteType = Dialect.QUOTE.indexOf(begin);
      String unquoted = getUnquotedAliasString(sqlIdentifier, quoteType);
      if ( quoteType >= 0 ) {
         char endQuote = Dialect.CLOSED_QUOTE.charAt(quoteType);
         return begin + unquoted + endQuote;
      }
      else {
         return unquoted;
      }
   }

So, if we "quote" the second column definition, Hibernate will generate an alias for a column name, and the SQL will work!
There is an excerpt from my mapping:

Code:

<class name="AccountingClauseHistoryItem" table="ACC_HISTORY">
      <composite-id>
         <key-property name="parentId" column="ID_ACC"/>
         <key-property name="runDate" column="RUN_DATE" type="date"/>
      </composite-id>

      <many-to-one name="accountingClause" column='"ID_ACC"' insert="false" update="false"/>
........and so on..........
</class>

Please note column ID_ACC is double quoted when declared in "many-to-one".