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); }