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$ + + + } + }