[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-526) Problem finding records with compound primary keys

[<leg...@at...>]

The following comment has been added to this issue:

     Author: David R Robison
    Created: Wed, 10 Dec 2003 6:39 AM
       Body:
The problem turned out to be that I had a compound key that referenced a row in another table that also had a compound key. however, the individual columns making up the key were not in a consistent order. In the referenced table the compound primary key was columns A and B, but in the referencing table, the compound primary key was column C, B, and A. The fact that the A and B columns were reversed in order seemed to be the problem.

I hope this makes sence.

David Robison
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-526


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-526
    Summary: Problem finding records with compound primary keys
       Type: Bug

     Status: Closed
   Priority: Major
 Resolution: REJECTED

    Project: Hibernate2
 Components: 
             core
   Versions:
             2.1 rc1

   Assignee: 
   Reporter: David R Robison

    Created: Fri, 5 Dec 2003 7:53 AM
    Updated: Wed, 10 Dec 2003 6:39 AM
Environment: postgresql on W2K

Description:
I'm trying to find a record with a compound key. The following is my session.find call:

Session hbSession = HibernateSession.currentSession();
types = hbSession.find("from com.orci.ITSAM.hibernate.SegmentType sgType " +
	"where sgType.comp_id.siteDatabase.comp_id.site = ? " + 
	"and sgType.comp_id.siteDatabase.comp_id.dbId = ? ", 
	new Object[] {
		SiteDatabaseServices.MyDbSiteCode(context),
		SiteDatabaseServices.MyDbId(context) }, 
	new Type[] {
		Hibernate.INTEGER,
		Hibernate.INTEGER });	

I'v set show_sql to true. When this line is executed, the following SQL statement prints:

select segmenttyp0_.sg_type_code as sg_type_1_, segmenttyp0_.sg_db_site as sg_db_site, segmenttyp0_.sg_db_id as sg_db_id, segmenttyp0_.name as name, segmenttyp0_.default_mnemonic as default_5_, segmenttyp0_.gmt_last_updated as gmt_last6_, segmenttyp0_.rstat_type_code as rstat_ty7_, segmenttyp0_.last_upd_db_site as last_upd8_, segmenttyp0_.last_upd_db_id as last_upd9_ from segment_type segmenttyp0_ where (segmenttyp0_.sg_db_id=? )and(segmenttyp0_.sg_db_site=? )

What is interesting is that the WHERE clause checks the site first and then the dbId but in the printed SQL statement, the WHERE clause is db_id first and then site. This causes problems with the parameters passed into the find call. Any ideas why the SQL WHERE clause is being rearanged?

Thanks,
David Robison


---------------------------------------------------------------------
JIRA INFORMATION:
This message is automatically generated by JIRA.

If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira