Author: shawkins Date: 2010-06-22 15:56:42 -0400 (Tue, 22 Jun 2010) New Revision: 2280 Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java Log: TEIID-1107 adding the ability to raise criteria if it's been pushed too far. 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 2010-06-22 19:24:56 UTC (rev 2279) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-06-22 19:56:42 UTC (rev 2280) @@ -164,10 +164,66 @@ return performRaise(rootNode, accessNode, parentNode); case NodeConstants.Types.SELECT: { - if (!parentNode.hasBooleanProperty(NodeConstants.Info.IS_DEPENDENT_SET) && canRaiseOverSelect(accessNode, metadata, capFinder, parentNode)) { + if (parentNode.hasBooleanProperty(NodeConstants.Info.IS_DEPENDENT_SET)) { + return null; + } + if (canRaiseOverSelect(accessNode, metadata, capFinder, parentNode)) { RulePushSelectCriteria.satisfyAccessPatterns(parentNode, accessNode); return performRaise(rootNode, accessNode, parentNode); - } + } + //determine if we should push the select back up + if (parentNode.getParent() == null) { + return null; + } + PlanNode selectRoot = parentNode; + while (selectRoot.getParent() != null && selectRoot.getParent().getType() == NodeConstants.Types.SELECT) { + selectRoot = selectRoot.getParent(); + } + if (selectRoot.getParent() == null || selectRoot.getParent().getType() == NodeConstants.Types.PROJECT) { + return null; + } + PlanNode grandParent = selectRoot.getParent(); + boolean isLeft = false; + isLeft = grandParent.getFirstChild() == selectRoot; + if (grandParent.getType() == NodeConstants.Types.JOIN) { + JoinType jt = (JoinType)grandParent.getProperty(NodeConstants.Info.JOIN_TYPE); + if (jt == JoinType.JOIN_FULL_OUTER || (jt == JoinType.JOIN_LEFT_OUTER && !isLeft)) { + return null; + } + } + grandParent.removeChild(selectRoot); + if (isLeft) { + grandParent.addFirstChild(accessNode); + } else { + grandParent.addLastChild(accessNode); + } + PlanNode newParent = grandParent.getParent(); + //TODO: use costing or heuristics instead of always raising + PlanNode newRoot = raiseAccessNode(rootNode, accessNode, metadata, capFinder, afterJoinPlanning); + if (newRoot == null) { + //return the tree to its original state + parentNode.addFirstChild(accessNode); + if (isLeft) { + grandParent.addFirstChild(selectRoot); + } else { + grandParent.addLastChild(selectRoot); + } + } else { + //attach the select nodes above the access node + accessNode = grandParent.getParent(); + if (newParent != null) { + isLeft = newParent.getFirstChild() == accessNode; + if (isLeft) { + newParent.addFirstChild(selectRoot); + } else { + newParent.addLastChild(selectRoot); + } + } else { + newRoot = selectRoot; + } + parentNode.addFirstChild(accessNode); + return newRoot; + } return null; } case NodeConstants.Types.SOURCE: Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2010-06-22 19:24:56 UTC (rev 2279) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2010-06-22 19:56:42 UTC (rev 2280) @@ -922,5 +922,84 @@ 0 // UnionAll }); } + + + /** + * Test for Case 836073: + */ + @Test public void testForCase836073_1() { + String sql = "select bqt1.smalla.intkey, bqt1.smallb.intkey from bqt1.smalla, bqt1.smallb WHERE formatdate(bqt1.smalla.DateValue,'yyyyMM') = '200309' AND bqt1.smalla.intkey = bqt1.smallb.intkey"; //$NON-NLS-1$ + + // Plan query + ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.exampleBQTCached(), new String[] {"SELECT bqt1.smalla.DateValue, bqt1.smalla.intkey, bqt1.smallb.intkey FROM bqt1.smalla, bqt1.smallb WHERE bqt1.smalla.intkey = bqt1.smallb.intkey"}); //$NON-NLS-1$ + + TestOptimizer.checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // Join + 0, // MergeJoin + 0, // Null + 0, // PlanExecution + 1, // Project + 1, // Select + 0, // Sort + 0 // UnionAll + }); + } + + @Test public void testForCase836073_2() { + String sql = "select bqt1.smalla.intkey, bqt1.smallb.intkey from bqt1.smalla left outer join bqt1.smallb on bqt1.smalla.intkey = bqt1.smallb.intkey WHERE formatdate(bqt1.smalla.DateValue,'yyyyMM') = '200309'"; //$NON-NLS-1$ + // Plan query + ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.exampleBQTCached(), new String[] {"SELECT bqt1.smalla.DateValue, bqt1.smalla.intkey, bqt1.smallb.intkey FROM bqt1.smalla LEFT OUTER JOIN bqt1.smallb ON bqt1.smalla.intkey = bqt1.smallb.intkey"}); //$NON-NLS-1$ + + TestOptimizer.checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // Join + 0, // MergeJoin + 0, // Null + 0, // PlanExecution + 1, // Project + 1, // Select + 0, // Sort + 0 // UnionAll + }); + } + + /** + * Note that we don't allow pushdown here because the criteria placement matters + */ + @Test public void testForCase836073_3() { + String sql = "select bqt1.smalla.intkey, b.intkey from bqt1.smalla left outer join (select * from bqt1.smallb where formatdate(bqt1.smallb.DateValue,'yyyyMM') = '200309') b on bqt1.smalla.intkey = b.intkey"; //$NON-NLS-1$ + + // Plan query + ProcessorPlan plan = TestOptimizer.helpPlan(sql, FakeMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.intkey AS c_0 FROM bqt1.smalla AS g_0 ORDER BY c_0", "SELECT g_0.DateValue AS c_0, g_0.IntKey AS c_1 FROM bqt1.smallb AS g_0 ORDER BY c_1"}); //$NON-NLS-1$ //$NON-NLS-2$ + + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // Join + 1, // MergeJoin + 0, // Null + 0, // PlanExecution + 1, // Project + 1, // Select + 0, // Sort + 0 // UnionAll + }); + } + } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2010-06-22 19:24:56 UTC (rev 2279) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2010-06-22 19:56:42 UTC (rev 2280) @@ -4386,17 +4386,17 @@ "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT1.SmallB WHERE (BQT1.SmallA.IntKey = lookup('BQT1.SmallB', 'IntKey', 'StringKey', BQT1.SmallB.StringKey)) AND (BQT1.SmallA.IntKey = 1)", //$NON-NLS-1$ metadata, null, capFinder, - new String[] {"SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.IntKey = 1", "SELECT BQT1.SmallB.StringKey FROM BQT1.SmallB"}, //$NON-NLS-1$ //$NON-NLS-2$ + new String[] {"SELECT g_1.StringKey, g_0.IntKey FROM BQT1.SmallA AS g_0, BQT1.SmallB AS g_1 WHERE g_0.IntKey = 1"}, //$NON-NLS-1$ //$NON-NLS-2$ SHOULD_SUCCEED ); checkNodeTypes(plan, new int[] { - 2, // Access + 1, // Access 0, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping - 1, // NestedLoopJoinStrategy + 0, // NestedLoopJoinStrategy 0, // MergeJoinStrategy 0, // Null 0, // PlanExecution