"Pure SQL" problem with JCA Adapter

I have wasted nearly a day chasing down a ghost problem with SOA Jca DB adapter. I thought I'll make a note of it here.

The database is SQLServer,we use SQLservre JDBC4.0 driver. I use "pure SQL" query for the adapter as following:

SELECT top 1 A.TNDR_CTL_ID, B.ACCOUNTING_DT

FROM PS_CI_TNDR_CTL A

,PS_CI_DEP_CTL B

WHERE A.TNDR_SOURCE_CD = #source

AND A.DEP_CTL_ID = B.DEP_CTL_ID

AND B.ACCOUNTING_DT <= convert(datetime,#date)

and A.TNDR_CTL_ST_FLG = '10' order by B.ACCOUNTING_DT desc

However, when I ran the BPEL test, it generated a nonsense error: 

oracle.sysman.emSDK.webservices.wsdlapi.SoapTestException: Client received SOAP Fault from server : Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'getTenderCtrlId' failed due to: Pure SQL Exception. Pure SQL Execute of SELECT top 1 A.TNDR_CTL_ID, B.ACCOUNTING_DT FROM PS_CI_TNDR_CTL A ,PS_CI_DEP_CTL B WHERE A.TNDR_SOURCE_CD = ? AND A.DEP_CTL_ID = B.DEP_CTL_ID AND B.ACCOUNTING_DT <= convert(datetime,?) and A.TNDR_CTL_ST_FLG = '10' order by B.ACCOUNTING_DT desc failed. Caused by java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Warning: Fatal error 823 occurred at Aug 17 2012 4:29PM

I initially thought this was an issue of multiple input parameters. So I stripped my query to the minimum as:

SELECT 

A.TNDR_CTL_ID, B.ACCOUNTING_DT 

FROM 

PS_CI_TNDR_CTL A , PS_CI_DEP_CTL B

and I still get that out of range error. That's when I tested the inner join and found a working query.

The query below is logically equivalent to the original version, except this one uses "inner join". And this one works. 

SELECT top 1 PS_CI_TNDR_CTL.TNDR_CTL_ID,

  PS_CI_DEP_CTL.ACCOUNTING_DT

FROM PS_CI_TNDR_CTL

INNER JOIN PS_CI_DEP_CTL

ON PS_CI_TNDR_CTL.DEP_CTL_ID        = PS_CI_DEP_CTL.DEP_CTL_ID

AND PS_CI_DEP_CTL.ACCOUNTING_DT    <= CONVERT(DATETIME,#dt)

WHERE PS_CI_TNDR_CTL.TNDR_SOURCE_CD = #source

AND PS_CI_TNDR_CTL.TNDR_CTL_ST_FLG  = '10'

ORDER BY PS_CI_DEP_CTL.ACCOUNTING_DT DESC

Beats me!