OEP: specified predicate requires full scan of external source which is not supported Error

Recently, I was working on a POC, which involved coherence as one of the components in building OEP application.

There was a stream of events coming up from CSV and  I had to query coherence cache for correlation.  While building the application, I receved the below error

Invalid statement: "alter query >>GroupingQuery<< start"
Description: specified predicate requires full scan of external source which is not supported
Cause: specified predicate requires full scan of external source which is not supported
Action: please modify the join predicate

As per the documentation, the above error comes up if cache key is not used in the query, but in our case, it is one of the parameters within the query.

My CQL query was

<query id="GroupingQuery"><![CDATA[
select c.posid, c.tid as totalCount, c.tvalue as totalAmount, p.posname as POSNAME, p.poscity as POSCITY
from CSVIntoProcessor [now] as c, Pos as p
where p.posid<!-- java.lang.Integer -> = c.posid<!-- int ->]]></query>

Pos is my coherence & Posid was my coherence key.

After spending hrs in fixing this, I was able to fix this which was nothing but a data type issue.

To debug I first enable the cache logs

1. Go to ORACLE_CEP_HOME/user_projects/domains/DOMAIN_DIR/servername/config & edit config.xml & change the default values from Notice to Debug

<entry>
        <key>Cache</key>
        <value> Debug </value>
</entry>
<entry>
        <key>CQLProcessor</key>
        <value> Debug </value>
</entry>
<entry>
        <key>CQLEngine</key>
        <value> Debug </value>
</entry>
<entry>
        <key>CQLEngineTrace</key>
        <value>Debug</value>
</entry>


2. Restart the server and check the logs after deploying your app and look for the below lines

####<May 23, 2015 8:29:52 PM PDT> <Info> <CQLProcessor> <> <defaultserver> <SpringOsgiExtenderThread-154> <> <> <> <1464060592137> <BEA-000000> <execute DDL [register external relation Pos(posid java.lang.Integer, posname char(2147483647), poscity char(2147483647), _this oracle.coherence.Pos)]> 

####<May 23, 2016 8:29:52 PM PDT> <Info> <CQLProcessor> <> <defaultserver> <SpringOsgiExtenderThread-154> <> <> <> <1464060592168> <BEA-000000> <execute DDL [register stream channel(posId int, tDate java.util.Date, tValue float, tId int, cId int, _this com.oracle.oep.Transactions) is system timestamped]> 



3. Carefully observe the key data types. The external relation pos  key is java.lang.Integer, where as the key in my stream channel is posId which is int. (Shown this in red in the error message)

4. Change the data type of the external schema from java.lang.Integer to int and redeply the app, the issue should be fixed. Ideally , that shouldn't have been the problem since java offers auto-boxing but never the less, I am glad that this worked.

Happy Learning!!