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'