Hmmm, ran into another issue related to this.
I wanted to add another Oracle database to my VDB and be able to run spatial queries on both individually. I changed my VDB to look like this:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Foo" version="1">
<property name="UseConnectorMetadata" value="true"/>
<model name="foo">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaPattern" value="FOO"/>
<source name="foo" translator-name="oracle" connection-jndi-name="java:/foo-ds"/>
<metadata type="NATIVE,DDL"><![CDATA[
CREATE FOREIGN FUNCTION sdo_elem_info_array(variadic x integer) RETURNS object;
CREATE FOREIGN FUNCTION sdo_ordinate_array(variadic x double) RETURNS string;
CREATE FOREIGN FUNCTION sdo_geometry(a integer, b integer, c object, d object, e object) RETURNS object;
CREATE FOREIGN FUNCTION sdo_anyinteract(a object, b object) RETURNS string;
ALTER FOREIGN TABLE "STUFF" ALTER COLUMN "FOOTPRINT" OPTIONS(SET SEARCHABLE 'Searchable');
]]>
</metadata>
</model>
<model name="bar">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaPattern" value="BAR"/>
<source name="bar" translator-name="oracle" connection-jndi-name="java:/bar-ds"/>
<metadata type="NATIVE,DDL"><![CDATA[
CREATE FOREIGN FUNCTION sdo_elem_info_array(variadic x integer) RETURNS object;
CREATE FOREIGN FUNCTION sdo_ordinate_array(variadic x double) RETURNS string;
CREATE FOREIGN FUNCTION sdo_geometry(a integer, b integer, c object, d object, e object) RETURNS object;
CREATE FOREIGN FUNCTION sdo_anyinteract(a object, b object) RETURNS string;
ALTER FOREIGN TABLE "STUFF" ALTER COLUMN "FOOTPRINT" OPTIONS(SET SEARCHABLE 'Searchable');
]]>
</metadata>
</model>
</vdb>
This gives me two schemas with mostly the same DDL, but different data. The Oracle versions are slightly different (one is XE), but I don't think that is the problem.
This query works:
select id from bar.stuff where sdo_anyinteract(footprint,footprint) = 'TRUE';
This query does not work:
select id from foo.stuff where sdo_anyinteract(footprint,footprint) = 'TRUE';
13:59:50,088 ERROR [org.teiid.PROCESSOR] (Worker70_QueryProcessorQueue3266) 1GvvMGu0PSoi TEIID30019 Unexpected exception for request 1GvvMGu0PSoi.0: java.lang.NullPointerException
at org.teiid.query.optimizer.relational.rules.CapabilitiesUtil.getCapabilities(CapabilitiesUtil.java:436) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CapabilitiesUtil.isSameConnector(CapabilitiesUtil.java:423) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.visitor.EvaluatableVisitor.visit(EvaluatableVisitor.java:93) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:173) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CriteriaCapabilityValidatorVisitor$1.visitVisitor(CriteriaCapabilityValidatorVisitor.java:765) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:60) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:197) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:173) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CriteriaCapabilityValidatorVisitor$1.visitNode(CriteriaCapabilityValidatorVisitor.java:739) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:194) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:173) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CriteriaCapabilityValidatorVisitor$1.visitNode(CriteriaCapabilityValidatorVisitor.java:739) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:194) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:173) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CriteriaCapabilityValidatorVisitor$1.visitNode(CriteriaCapabilityValidatorVisitor.java:739) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:132) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.sql.lang.CompareCriteria.acceptVisitor(CompareCriteria.java:126) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CriteriaCapabilityValidatorVisitor.canPushLanguageObject(CriteriaCapabilityValidatorVisitor.java:785) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CriteriaCapabilityValidatorVisitor.canPushLanguageObject(CriteriaCapabilityValidatorVisitor.java:691) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.RuleRaiseAccess.canRaiseOverSelect(RuleRaiseAccess.java:481) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.RulePushSelectCriteria.examinePath(RulePushSelectCriteria.java:448) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.RulePushSelectCriteria.pushTowardOriginatingNode(RulePushSelectCriteria.java:359) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.RulePushSelectCriteria.execute(RulePushSelectCriteria.java:123) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:606) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:255) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:408) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:435) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_45]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_45]
at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_45]
If I swap the model names, I get the same results. It looks like the first one (alphabetically) works.
I believe I found a workaround for this, which is to use SDO_RELATE which is defined in Teiid:
These both work:
select id from bar.stuff where sdo_relate(footprint,footprint,'mask=ANYINTERACT') = 'TRUE';
select id from foo.stuff where sdo_relate(footprint,footprint,'mask=ANYINTERACT') = 'TRUE';
These also both work:
select id from bar.stuff where sdo_relate(footprint,'sdo_geometry(2003, 8307,null,sdo_elem_info_array(1, 1003, 3),sdo_ordinate_array(0, 0, 100, 100))','mask=ANYINTERACT') = 'TRUE';
select id from foo.stuff where sdo_relate(footprint,'sdo_geometry(2003, 8307,null,sdo_elem_info_array(1, 1003, 3),sdo_ordinate_array(0, 0, 100, 100))','mask=ANYINTERACT') = 'TRUE';