[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3897) <One-to-one> Filter Required

[Sandeep Vaid (JIRA)]

<One-to-one> Filter Required
----------------------------

                 Key: HHH-3897
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3897
             Project: Hibernate Core
          Issue Type: Improvement
    Affects Versions: 3.3.1
            Reporter: Sandeep Vaid
            Priority: Critical


Consider this scenatio :
    I have one-to-one relation between Product and ProductBasic BUT at database level i have one-to-many between 
    PRODUCT and PRODUCTBASIC tables as history is mainted in the same table :
    
    PRODUCT ----> PID (PK)
    PRODUCTBASIC ---> PID (FK), USECODE forms Composite-key
    			
    PRODUCT
     PID	PNAME	
      1		Pen
    
    PRODUCTBASIC
     PID 	USECODE		STARTTIME		ENDTIME
     1		  100	       06-MAY-2009 11:00	07-MAY-2009 10:00
     1		  100	       07-MAY-2009 10:00	31-DEC-9999 00:00
     
     Now while querying for ProductBasic, i will always fetch the row which satisfies the condition STARTTIME<CURRENTTIME<ENDTIME
     and this will surely return me only one row.
     
     Ideally the generated sql query should be :
     
     select * from  PRODUCT product  left outer join PRODUCTBASIC productbasic  on '100'=productbasic.USETYPE 
        and product.PID=productbasic.PID 
        and (
         productbasic.STARTTIME<'09-MAY-2009 10:00' 
         and '09-MAY-2009 10:00'<productbasic.ENDTIME
        ) 
      where
  product.PID='1'

-- 
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
-
For more information on JIRA, see: http://www.atlassian.com/software/jira