2:29 p.m.
Author: shawkins
Date: 2012-05-30 08:29:32 -0400 (Wed, 30 May 2012)
New Revision: 4149
Modified:
trunk/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java
trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java
trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java
trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java
Log:
TEIID-2058 refining criteria placement
Modified:
trunk/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java
===================================================================
---
trunk/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/api/src/main/java/org/teiid/translator/BaseDelegatingExecutionFactory.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -418,6 +418,10 @@
return delegate.supportsAdvancedOlapOperations();
}
@Override
+ public boolean supportsSubqueryInOn() {
+ return delegate.supportsSubqueryInOn();
+ }
+ @Override
public boolean supportsConvert(int fromType, int toType) {
return delegate.supportsConvert(fromType, toType);
}
Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java
===================================================================
--- trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-05-29 20:25:52
UTC (rev 4148)
+++ trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-05-30 12:29:32
UTC (rev 4149)
@@ -968,5 +968,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:
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java
===================================================================
---
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/BaseDB2ExecutionFactory.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -173,4 +173,9 @@
return true;
}
+ @Override
+ public boolean supportsSubqueryInOn() {
+ return false;
+ }
+
}
Modified:
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -112,6 +112,7 @@
tgtCaps.setCapabilitySupport(Capability.ONLY_FORMAT_LITERALS,
srcCaps.supportsOnlyFormatLiterals());
tgtCaps.setCapabilitySupport(Capability.CRITERIA_ONLY_LITERAL_COMPARE,
srcCaps.supportsOnlyLiteralComparison());
tgtCaps.setCapabilitySupport(Capability.DEPENDENT_JOIN,
srcCaps.supportsDependentJoins());
+ tgtCaps.setCapabilitySupport(Capability.CRITERIA_ON_SUBQUERY,
srcCaps.supportsSubqueryInOn());
List<String> functions = srcCaps.getSupportedFunctions();
if(functions != null && functions.size() > 0) {
Modified:
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -330,7 +330,8 @@
DEPENDENT_JOIN,
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:
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -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:
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java
===================================================================
---
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -55,6 +55,7 @@
import org.teiid.query.sql.symbol.Expression;
import org.teiid.query.sql.symbol.GroupSymbol;
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;
@@ -687,6 +688,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: trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java
===================================================================
--- trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2012-05-29
20:25:52 UTC (rev 4148)
+++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -2787,7 +2787,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$
@@ -2807,7 +2807,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: trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java
===================================================================
---
trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -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$
+
+
+ }
+
}
Modified:
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java
===================================================================
---
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2012-05-29
20:25:52 UTC (rev 4148)
+++
trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2012-05-30
12:29:32 UTC (rev 4149)
@@ -201,7 +201,7 @@
ProcessorPlan plan = TestOptimizer.helpPlan("select S_ACCTBAL, S_NAME,
N_NAME, P_PARTKEY, P_MFGR, S_ADDRESS, S_PHONE, S_COMMENT from (SELECT SUPPLIER.S_ACCTBAL,
SUPPLIER.S_NAME, NATION.N_NAME, PART.P_PARTKEY, PART.P_MFGR, SUPPLIER.S_ADDRESS,
SUPPLIER.S_PHONE, SUPPLIER.S_COMMENT FROM PART, SUPPLIER, PARTSUPP, NATION, REGION WHERE
(PART.P_PARTKEY = PS_PARTKEY) AND (S_SUPPKEY = PS_SUPPKEY) AND (P_SIZE = 15) AND (P_TYPE
LIKE '%BRASS') AND (S_NATIONKEY = N_NATIONKEY) AND (N_REGIONKEY = R_REGIONKEY) AND
(R_NAME = 'EUROPE') AND (PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST) FROM PARTSUPP,
SUPPLIER, NATION, REGION WHERE (PART.P_PARTKEY = PS_PARTKEY) AND (S_SUPPKEY = PS_SUPPKEY)
AND (S_NATIONKEY = N_NATIONKEY) AND (N_REGIONKEY = R_REGIONKEY) AND (R_NAME =
'EUROPE'))) ORDER BY SUPPLIER.S_ACCTBAL DESC, NATION.N_NAME, SUPPLIER.S_NAME,
PART.P_PARTKEY) as x", //$NON-NLS-1$
METADATA, null, finder,
- new String[] {"SELECT g_1.S_ACCTBAL, g_1.S_NAME, g_3.N_NAME,
g_0.P_PARTKEY, g_0.P_MFGR, g_1.S_ADDRESS, g_1.S_PHONE, g_1.S_COMMENT FROM
TPCR_Oracle_9i.PART AS g_0, TPCR_Oracle_9i.SUPPLIER AS g_1, TPCR_Oracle_9i.PARTSUPP AS
g_2, TPCR_Oracle_9i.NATION AS g_3, TPCR_Oracle_9i.REGION AS g_4 WHERE (g_3.N_REGIONKEY =
g_4.R_REGIONKEY) AND (g_1.S_NATIONKEY = g_3.N_NATIONKEY) AND (g_1.S_SUPPKEY =
g_2.PS_SUPPKEY) AND (g_0.P_PARTKEY = g_2.PS_PARTKEY) AND (g_2.PS_SUPPLYCOST = (SELECT
MIN(g_5.PS_SUPPLYCOST) FROM TPCR_Oracle_9i.PARTSUPP AS g_5, TPCR_Oracle_9i.SUPPLIER AS
g_6, TPCR_Oracle_9i.NATION AS g_7, TPCR_Oracle_9i.REGION AS g_8 WHERE (g_6.S_SUPPKEY =
g_5.PS_SUPPKEY) AND (g_6.S_NATIONKEY = g_7.N_NATIONKEY) AND (g_7.N_REGIONKEY =
g_8.R_REGIONKEY) AND (g_5.PS_PARTKEY = g_0.P_PARTKEY) AND (g_8.R_NAME =
'EUROPE'))) AND (g_0.P_SIZE = 15.0) AND (g_0.P_TYPE LIKE '%BRASS') AND
(g_4.R_NAME = 'EUROPE')"}, ComparisonMode.EXACT_COMMAND_STRING);
//$NON-NLS-1$
+ new String[] {"SELECT g_1.S_ACCTBAL, g_1.S_NAME, g_3.N_NAME,
g_0.P_PARTKEY, g_0.P_MFGR, g_1.S_ADDRESS, g_1.S_PHONE, g_1.S_COMMENT FROM
TPCR_Oracle_9i.PART AS g_0, TPCR_Oracle_9i.SUPPLIER AS g_1, TPCR_Oracle_9i.PARTSUPP AS
g_2, TPCR_Oracle_9i.NATION AS g_3, TPCR_Oracle_9i.REGION AS g_4 WHERE (g_3.N_REGIONKEY =
g_4.R_REGIONKEY) AND (g_1.S_NATIONKEY = g_3.N_NATIONKEY) AND (g_1.S_SUPPKEY =
g_2.PS_SUPPKEY) AND (g_2.PS_SUPPLYCOST = (SELECT MIN(g_5.PS_SUPPLYCOST) FROM
TPCR_Oracle_9i.PARTSUPP AS g_5, TPCR_Oracle_9i.SUPPLIER AS g_6, TPCR_Oracle_9i.NATION AS
g_7, TPCR_Oracle_9i.REGION AS g_8 WHERE (g_6.S_SUPPKEY = g_5.PS_SUPPKEY) AND
(g_6.S_NATIONKEY = g_7.N_NATIONKEY) AND (g_7.N_REGIONKEY = g_8.R_REGIONKEY) AND
(g_5.PS_PARTKEY = g_0.P_PARTKEY) AND (g_8.R_NAME = 'EUROPE'))) AND (g_0.P_PARTKEY
= g_2.PS_PARTKEY) AND (g_0.P_SIZE = 15.0) AND (g_0.P_TYPE LIKE '%BRASS') AND
(g_4.R_NAME = 'EUROPE')"}, ComparisonMode.EXACT_COMMAND_STRING);
//$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}