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