Author: shawkins
Date: 2012-05-29 16:25:52 -0400 (Tue, 29 May 2012)
New Revision: 4148
Modified:
branches/7.7.x/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java
branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java
branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java
branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml
branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java
Log:
TEIID-2058 refining criteria placement
Modified:
branches/7.7.x/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java
===================================================================
---
branches/7.7.x/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -429,4 +429,8 @@
public boolean supportsAdvancedOlapOperations() {
return delegate.supportsAdvancedOlapOperations();
}
+ @Override
+ public boolean supportsSubqueryInOn() {
+ return delegate.supportsSubqueryInOn();
+ }
}
Modified: branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java
===================================================================
--- branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-05-29
18:56:00 UTC (rev 4147)
+++ branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -962,5 +962,13 @@
public boolean supportsFormatLiteral(String literal, Format format) {
return false;
}
+
+ /**
+ * Refines subquery support.
+ * @return true if subqueries are supported in the on clause.
+ */
+ public boolean supportsSubqueryInOn() {
+ return true;
+ }
}
Modified:
branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java
===================================================================
---
branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -173,4 +173,9 @@
return true;
}
+ @Override
+ public boolean supportsSubqueryInOn() {
+ return false;
+ }
+
}
Modified:
branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml
===================================================================
---
branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2012-05-29
20:25:52 UTC (rev 4148)
@@ -844,6 +844,17 @@
</row>
<row>
<entry>
+ <para>SubqueryInOn</para>
+ </entry>
+ <entry>
+ <para>Join and base subquery support, such as
ExistsCriteria</para>
+ </entry>
+ <entry>
+ <para>Translator can support subqueries in the ON clause.
Defaults to true.</para>
+ </entry>
+ </row>
+ <row>
+ <entry>
<para>InlineViews</para>
</entry>
<entry>
Modified:
branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java
===================================================================
---
branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -111,6 +111,7 @@
tgtCaps.setCapabilitySupport(Capability.CRITERIA_LIKE_REGEX,
srcCaps.supportsLikeRegex());
setSupports(connectorID, tgtCaps, Capability.WINDOW_FUNCTION_DISTINCT_AGGREGATES,
srcCaps.supportsWindowDistinctAggregates(), Capability.ELEMENTARY_OLAP,
Capability.QUERY_AGGREGATES_DISTINCT);
tgtCaps.setCapabilitySupport(Capability.ONLY_FORMAT_LITERALS,
srcCaps.supportsOnlyFormatLiterals());
+ tgtCaps.setCapabilitySupport(Capability.CRITERIA_ON_SUBQUERY,
srcCaps.supportsSubqueryInOn());
List<String> functions = srcCaps.getSupportedFunctions();
if(functions != null && functions.size() > 0) {
Modified:
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java
===================================================================
---
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -332,7 +332,8 @@
CRITERIA_LIKE_REGEX,
WINDOW_FUNCTION_DISTINCT_AGGREGATES("WindowDistinctAggregates"),
//$NON-NLS-1$
QUERY_ONLY_SINGLE_TABLE_GROUP_BY,
- ONLY_FORMAT_LITERALS;
+ ONLY_FORMAT_LITERALS,
+ CRITERIA_ON_SUBQUERY;
private final String toString;
Modified:
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java
===================================================================
---
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -129,10 +129,10 @@
case NodeConstants.Types.JOIN:
{
//pushing below a join is not necessary under an access node
- if (NodeEditor.findParent(critNode, NodeConstants.Types.ACCESS) == null) {
+ if (NodeEditor.findParent(critNode, NodeConstants.Types.ACCESS) == null
&& critNode.getSubqueryContainers().isEmpty()) {
moved = handleJoinCriteria(sourceNode, critNode, metadata);
- break;
}
+ break;
}
case NodeConstants.Types.GROUP:
{
Modified:
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java
===================================================================
---
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -56,6 +56,7 @@
import org.teiid.query.sql.symbol.GroupSymbol;
import org.teiid.query.sql.symbol.SingleElementSymbol;
import org.teiid.query.sql.util.SymbolMap;
+import org.teiid.query.sql.visitor.ValueIteratorProviderCollectorVisitor;
import org.teiid.query.util.CommandContext;
import org.teiid.translator.ExecutionFactory.SupportedJoinCriteria;
@@ -694,6 +695,10 @@
return false;
}
if (sjc == SupportedJoinCriteria.ANY) {
+ boolean subqueryOn = CapabilitiesUtil.supports(Capability.CRITERIA_ON_SUBQUERY,
accessModelID, metadata, capFinder);
+ if (!subqueryOn &&
!ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(crit).isEmpty()) {
+ return false;
+ }
return true;
}
//theta join must be between elements with a compare predicate
Modified:
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java
===================================================================
---
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -2786,7 +2786,7 @@
}
/** Case 1456, defect 10492*/
- @Test public void testAliasingDefect2(){
+ @Test public void testAliasingDefect2() throws TeiidComponentException,
TeiidProcessingException{
// Create query
String sql = "SELECT X.e1 FROM vm1.g1 X, vm1.g1 Z WHERE X.e2 = (SELECT
MAX(e2) FROM vm1.g1 Y WHERE X.e1 = Y.e1 AND Y.e2 = Z.e2) AND X.e1 =
Z.e1";//$NON-NLS-1$
@@ -2806,7 +2806,7 @@
ProcessorPlan plan = helpPlan(sql, metadata,
null, capFinder,
- new String[] { "SELECT g1__1.e1 FROM pm1.g1 AS g1__1, pm1.g1 AS g1__2
WHERE (g1__1.e2 = (SELECT MAX(pm1.g1.e2) FROM pm1.g1 WHERE (pm1.g1.e1 = g1__1.e1) AND
(pm1.g1.e2 = g1__2.e2))) AND (g1__1.e1 = g1__2.e1)" }, SHOULD_SUCCEED);
//$NON-NLS-1$
+ new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g1 AS g_1 WHERE
(g_0.e1 = g_1.e1) AND (g_0.e2 = (SELECT MAX(g_2.e2) FROM pm1.g1 AS g_2 WHERE (g_2.e1 =
g_0.e1) AND (g_2.e2 = g_1.e2)))" }, ComparisonMode.EXACT_COMMAND_STRING);
//$NON-NLS-1$
checkNodeTypes(plan, FULL_PUSHDOWN);
}
Modified:
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java
===================================================================
---
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2012-05-29
18:56:00 UTC (rev 4147)
+++
branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2012-05-29
20:25:52 UTC (rev 4148)
@@ -40,6 +40,7 @@
import org.teiid.query.processor.ProcessorPlan;
import org.teiid.query.rewriter.TestQueryRewriter;
import org.teiid.query.unittest.RealMetadataFactory;
+import org.teiid.translator.ExecutionFactory;
import org.teiid.translator.SourceSystemFunctions;
@SuppressWarnings("nls")
@@ -176,7 +177,7 @@
"(c37s.stringkey = ('1' || (m37s.intkey || '0'))) AND
" + //$NON-NLS-1$
"(m37s.stringkey = m37n.stringkey) ))"; //$NON-NLS-1$
- String sqlOut = "SELECT g_0.intkey FROM bqt1.mediuma AS g_0, bqt1.smallb AS
g_1 WHERE (g_0.stringkey = concat('1', concat(g_1.intkey, '0'))) AND
(g_0.datevalue = (SELECT MAX(g_2.datevalue) FROM bqt1.mediuma AS g_2, bqt1.smallb AS g_3
WHERE (g_2.stringkey = concat('1', concat(g_3.intkey, '0'))) AND
(g_3.stringkey LIKE '%0') AND (g_3.stringkey = g_1.stringkey))) AND (g_1.stringkey
LIKE '%0')"; //$NON-NLS-1$
+ String sqlOut = "SELECT g_0.intkey FROM bqt1.mediuma AS g_0, bqt1.smallb AS
g_1 WHERE (g_0.stringkey = concat('1', concat(g_1.intkey, '0'))) AND
(g_1.stringkey LIKE '%0') AND (g_0.datevalue = (SELECT MAX(g_2.datevalue) FROM
bqt1.mediuma AS g_2, bqt1.smallb AS g_3 WHERE (g_2.stringkey = concat('1',
concat(g_3.intkey, '0'))) AND (g_3.stringkey LIKE '%0') AND (g_3.stringkey
= g_1.stringkey)))"; //$NON-NLS-1$
ProcessorPlan plan = helpPlan(sqlIn, RealMetadataFactory.exampleBQTCached(),
null, capFinder,
@@ -1059,5 +1060,61 @@
});
checkJoinCounts(plan, 0, 0);
}
+
+ /**
+ * Shows the default preference against on subquery
+ */
+ @Test public void testSubuqeryOn() throws Exception {
+ BasicSourceCapabilities bsc = getTypicalCapabilities();
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
+ bsc.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED,
ExecutionFactory.SupportedJoinCriteria.ANY);
+ bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
+ bsc.setCapabilitySupport(Capability.CRITERIA_EXISTS, true);
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_ANSI_JOIN, true);
+ bsc.setCapabilitySupport(Capability.CRITERIA_ON_SUBQUERY, true);
+ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT 1 FROM bqt1.smalla as
Y93 INNER JOIN bqt1.smallb as AG5 ON 1 = 1 WHERE EXISTS (SELECT 'Y' FROM
bqt1.mediuma WHERE AG5.intkey = 1 AND Y93.intkey = 1 )", //$NON-NLS-1$
+ RealMetadataFactory.exampleBQTCached(), null, new
DefaultCapabilitiesFinder(bsc),
+ new String[] {
+ "SELECT 1 FROM bqt1.smalla AS g_0 CROSS
JOIN bqt1.smallb AS g_1 WHERE EXISTS (SELECT 'Y' FROM bqt1.mediuma AS g_2 WHERE
(g_1.intkey = 1) AND (g_0.intkey = 1))"}, ComparisonMode.EXACT_COMMAND_STRING);
//$NON-NLS-1$
+ TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN);
+ }
+
+ /**
+ * Shows the pushdown is inhibited due to lack of support
+ */
+ @Test public void testSubuqeryOn1() throws Exception {
+ BasicSourceCapabilities bsc = getTypicalCapabilities();
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
+ bsc.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED,
ExecutionFactory.SupportedJoinCriteria.ANY);
+ bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
+ bsc.setCapabilitySupport(Capability.CRITERIA_EXISTS, true);
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_ANSI_JOIN, true);
+ TestOptimizer.helpPlan("SELECT 1 FROM bqt1.smalla as Y93 LEFT OUTER JOIN
bqt1.smallb as AG5 ON EXISTS (SELECT 'Y' FROM bqt1.mediuma WHERE AG5.intkey = 1
AND Y93.intkey = 1 )", //$NON-NLS-1$
+ RealMetadataFactory.exampleBQTCached(), null, new
DefaultCapabilitiesFinder(bsc),
+ new String[] {
+ "SELECT g_0.intkey FROM bqt1.smalla AS
g_0", "SELECT g_0.intkey FROM bqt1.smallb AS g_0"},
ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
+ }
+ /**
+ * Shows pushdown of on subquery with support
+ */
+ @Test public void testSubuqeryOn2() throws Exception {
+ BasicSourceCapabilities bsc = getTypicalCapabilities();
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
+ bsc.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED,
ExecutionFactory.SupportedJoinCriteria.ANY);
+ bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
+ bsc.setCapabilitySupport(Capability.CRITERIA_EXISTS, true);
+ bsc.setCapabilitySupport(Capability.QUERY_FROM_ANSI_JOIN, true);
+ bsc.setCapabilitySupport(Capability.CRITERIA_ON_SUBQUERY, true);
+ TestOptimizer.helpPlan("SELECT 1 FROM bqt1.smalla as Y93 LEFT OUTER JOIN
bqt1.smallb as AG5 ON EXISTS (SELECT 'Y' FROM bqt1.mediuma WHERE AG5.intkey = 1
AND Y93.intkey = 1 )", //$NON-NLS-1$
+ RealMetadataFactory.exampleBQTCached(), null, new
DefaultCapabilitiesFinder(bsc),
+ new String[] {
+ "SELECT 1 FROM bqt1.smalla AS g_0 LEFT
OUTER JOIN bqt1.smallb AS g_1 ON EXISTS (SELECT 'Y' FROM bqt1.mediuma AS g_2 WHERE
(g_1.intkey = 1) AND (g_0.intkey = 1))"}, ComparisonMode.EXACT_COMMAND_STRING);
//$NON-NLS-1$
+
+
+ }
+
}