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'