[teiid-commits] teiid SVN: r2987 - in trunk/engine/src: main/java/org/teiid/query/sql/lang and 2 other directories.

Author: shawkins Date: 2011-03-09 17:28:50 -0500 (Wed, 09 Mar 2011) New Revision: 2987 Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryCompareCriteria.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java Log: TEIID-1497 adding rewrite logic for quantified some Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-03-09 20:30:47 UTC (rev 2986) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-03-09 22:28:50 UTC (rev 2987) @@ -1141,8 +1141,8 @@ } } else if (criteria instanceof SubquerySetCriteria) { SubquerySetCriteria sub = (SubquerySetCriteria)criteria; - if (isNull(sub.getExpression())) { - return UNKNOWN_CRITERIA; + if (rewriteLeftExpression(sub)) { + return UNKNOWN_CRITERIA; } rewriteSubqueryContainer(sub, true); if (!RelationalNodeUtil.shouldExecute(sub.getCommand(), false, true)) { @@ -1187,7 +1187,10 @@ private Criteria rewriteDependentSetCriteria(DependentSetCriteria dsc) throws TeiidComponentException, TeiidProcessingException{ if (!processing) { - return rewriteCriteria(dsc); + if (rewriteLeftExpression(dsc)) { + return UNKNOWN_CRITERIA; + } + return dsc; } SetCriteria setCrit = new SetCriteria(); setCrit.setExpression(dsc.getExpression()); @@ -1570,6 +1573,28 @@ * quantifier is replaced with the canonical and equivalent 'SOME' */ private Criteria rewriteCriteria(SubqueryCompareCriteria criteria) throws TeiidComponentException, TeiidProcessingException{ + + if (criteria.getCommand().getProcessorPlan() == null && criteria.getPredicateQuantifier() != SubqueryCompareCriteria.ALL) { + if (criteria.getOperator() == CompareCriteria.EQ || criteria.getOperator() == CompareCriteria.NE) { + SubquerySetCriteria result = new SubquerySetCriteria(criteria.getLeftExpression(), criteria.getCommand()); + result.setNegated(criteria.getOperator() == CompareCriteria.NE); + return rewriteCriteria(result); + } + CompareCriteria cc = new CompareCriteria(); + cc.setLeftExpression(criteria.getLeftExpression()); + Query q = createInlineViewQuery(new GroupSymbol("X"), criteria.getCommand(), metadata, criteria.getCommand().getProjectedSymbols()); //$NON-NLS-1$ + SingleElementSymbol ses = q.getProjectedSymbols().get(0); + Expression expr = SymbolMap.getExpression(ses); + q.getSelect().clearSymbols(); + AggregateSymbol.Type type = Type.MAX; + if (criteria.getOperator() == CompareCriteria.GT || criteria.getOperator() == CompareCriteria.GE) { + type = Type.MIN; + } + q.getSelect().addSymbol(new AggregateSymbol(ses.getName(), type.name(), false, expr)); + cc.setRightExpression(new ScalarSubquery(q)); + cc.setOperator(criteria.getOperator()); + return rewriteCriteria(cc); + } Expression leftExpr = rewriteExpressionDirect(criteria.getLeftExpression()); @@ -2132,14 +2157,14 @@ return FALSE_CRITERIA; } - private Criteria rewriteCriteria(AbstractSetCriteria criteria) throws TeiidComponentException, TeiidProcessingException{ + private boolean rewriteLeftExpression(AbstractSetCriteria criteria) throws TeiidComponentException, TeiidProcessingException{ criteria.setExpression(rewriteExpressionDirect(criteria.getExpression())); if (isNull(criteria.getExpression())) { - return UNKNOWN_CRITERIA; + return true; } - return criteria; + return false; } private Criteria rewriteCriteria(SetCriteria criteria) throws TeiidComponentException, TeiidProcessingException{ @@ -2149,7 +2174,7 @@ criteria.setExpression(rewriteExpressionDirect(criteria.getExpression())); - if (isNull(criteria.getExpression())) { + if (rewriteLeftExpression(criteria)) { return UNKNOWN_CRITERIA; } Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryCompareCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryCompareCriteria.java 2011-03-09 20:30:47 UTC (rev 2986) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/SubqueryCompareCriteria.java 2011-03-09 22:28:50 UTC (rev 2987) @@ -213,5 +213,15 @@ public Expression getRightExpression() { return new ScalarSubquery(getCommand()); } + + @Override + public void negate() { + super.negate(); + if (this.predicateQuantifier == ALL) { + this.predicateQuantifier = SOME; + } else { + this.predicateQuantifier = ALL; + } + } } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2011-03-09 20:30:47 UTC (rev 2986) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2011-03-09 22:28:50 UTC (rev 2987) @@ -1292,7 +1292,7 @@ } @Test public void testCompareSubquery1() { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 < any (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 < ALL (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -1312,27 +1312,6 @@ }); } - @Test public void testCompareSubquery2() { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 <= some (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ - new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$ - checkNodeTypes(plan, new int[] { - 1, // Access - 0, // DependentAccess - 1, // DependentSelect - 0, // DependentProject - 0, // DupRemove - 0, // Grouping - 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy - 0, // Null - 0, // PlanExecution - 1, // Project - 0, // Select - 0, // Sort - 0 // UnionAll - }); - } - @Test public void testCompareSubquery3() { ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 >= all (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$ @@ -2972,14 +2951,14 @@ } /** Case 1456, defect 10492*/ - @Test public void testAliasingDefect3(){ + @Test public void testAliasingDefect3() throws Exception { // Create query - String sql = "SELECT X.e1 FROM pm1.g2, vm1.g1 X WHERE X.e2 = ANY (SELECT MAX(e2) FROM vm1.g1 Y WHERE X.e1 = Y.e1) AND X.e1 = pm1.g2.e1";//$NON-NLS-1$ + String sql = "SELECT X.e1 FROM pm1.g2, vm1.g1 X WHERE X.e2 = ALL (SELECT MAX(e2) FROM vm1.g1 Y WHERE X.e1 = Y.e1) AND X.e1 = pm1.g2.e1";//$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true); - caps.setCapabilitySupport(Capability.CRITERIA_QUANTIFIED_SOME, true); + caps.setCapabilitySupport(Capability.CRITERIA_QUANTIFIED_ALL, true); caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); @@ -2993,7 +2972,7 @@ ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, - new String[] { "SELECT g_1.e1 FROM pm1.g2 AS g_0, pm1.g1 AS g_1 WHERE (g_1.e1 = g_0.e1) AND (g_1.e2 = SOME (SELECT MAX(g_2.e2) FROM pm1.g1 AS g_2 WHERE g_2.e1 = g_1.e1))" }, SHOULD_SUCCEED); //$NON-NLS-1$ + new String[] { "SELECT g_1.e1 FROM pm1.g2 AS g_0, pm1.g1 AS g_1 WHERE (g_1.e1 = g_0.e1) AND (g_1.e2 = ALL (SELECT MAX(g_2.e2) FROM pm1.g1 AS g_2 WHERE g_2.e1 = g_1.e1))" }, 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 2011-03-09 20:30:47 UTC (rev 2986) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2011-03-09 22:28:50 UTC (rev 2987) @@ -871,4 +871,10 @@ @Test public void testInvalidGeneratedSemijoinQuery1() throws Exception { TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 = (Select max(e2) from pm2.g2 where e1 = pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1 FROM pm2.g2 GROUP BY e1) AS X__1 WHERE (pm3.g1.e1 = X__1.e1) AND (pm3.g1.e2 = X__1.MAX)", FakeMetadataFactory.example4()); } + + @Test public void testCompareSubquery2() throws Exception { + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 <= some (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ + new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 <= (SELECT MAX(X.e1) FROM (SELECT e1 FROM pm2.g1) AS X)" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + checkNodeTypes(plan, FULL_PUSHDOWN); + } } Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-03-09 20:30:47 UTC (rev 2986) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-03-09 22:28:50 UTC (rev 2987) @@ -824,12 +824,12 @@ @Test public void testCompareSubqueryANY() { helpTestRewriteCommand("SELECT e1 FROM pm1.g1 WHERE '3' = ANY (SELECT e1 FROM pm1.g2)", //$NON-NLS-1$ - "SELECT e1 FROM pm1.g1 WHERE '3' = SOME (SELECT e1 FROM pm1.g2)"); //$NON-NLS-1$ + "SELECT e1 FROM pm1.g1 WHERE '3' IN (SELECT e1 FROM pm1.g2)"); //$NON-NLS-1$ } @Test public void testCompareSubquery() { helpTestRewriteCommand("SELECT e1 FROM pm1.g1 WHERE '3' = SOME (SELECT e1 FROM pm1.g2)", //$NON-NLS-1$ - "SELECT e1 FROM pm1.g1 WHERE '3' = SOME (SELECT e1 FROM pm1.g2)"); //$NON-NLS-1$ + "SELECT e1 FROM pm1.g1 WHERE '3' IN (SELECT e1 FROM pm1.g2)"); //$NON-NLS-1$ } @Test public void testCompareSubqueryUnknown() { @@ -2428,13 +2428,17 @@ @Test public void testRewritePredicateOptimizationOr() throws Exception { helpTestRewriteCriteria("pm1.g1.e2 in (5, 6) or pm1.g1.e2 = 2", "pm1.g1.e2 IN (2, 5, 6)"); } + + @Test public void testRewriteCritSubqueryNegate() { + helpTestRewriteCriteria("not(pm1.g1.e1 > SOME (select 'a' from pm1.g2))", "pm1.g1.e1 <= ALL (SELECT 'a' FROM pm1.g2)"); //$NON-NLS-1$ //$NON-NLS-2$ + } @Test public void testRewriteCritSubqueryFalse() { helpTestRewriteCriteria("exists(select 1 from pm1.g1 where 1=0)", "1 = 0"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testRewriteCritSubqueryFalse1() { - helpTestRewriteCriteria("not(pm1.g1.e1 < SOME (select 'a' from pm1.g1 where 1=0))", "1 = 0"); //$NON-NLS-1$ //$NON-NLS-2$ + helpTestRewriteCriteria("not(pm1.g1.e1 > SOME (select 'a' from pm1.g1 where 1=0))", "pm1.g1.e1 IS NOT NULL"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testRewriteCritSubqueryFalse2() {