Author: shawkins Date: 2011-05-24 16:46:38 -0400 (Tue, 24 May 2011) New Revision: 3194 Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java Log: TEIID-1603 fix for invalid rewrite of <> ANY Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-05-24 14:04:16 UTC (rev 3193) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-05-24 20:46:38 UTC (rev 3194) @@ -1589,26 +1589,29 @@ */ 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) { + if (criteria.getCommand().getProcessorPlan() == null) { + if ((criteria.getOperator() == CompareCriteria.EQ && criteria.getPredicateQuantifier() != SubqueryCompareCriteria.ALL) + || (criteria.getOperator() == CompareCriteria.NE && criteria.getPredicateQuantifier() == SubqueryCompareCriteria.ALL)) { 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; + if (criteria.getPredicateQuantifier() != SubqueryCompareCriteria.ALL && criteria.getOperator() != CompareCriteria.EQ && criteria.getOperator() != CompareCriteria.NE) { + 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); } - 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()); Modified: branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2011-05-24 14:04:16 UTC (rev 3193) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2011-05-24 20:46:38 UTC (rev 3194) @@ -836,6 +836,22 @@ TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 = all (select e2 FROM pm1.g1 where pm3.g1.e1 = e1)", "SELECT e1 FROM pm3.g1 WHERE pm3.g1.e2 = ALL (SELECT e2 FROM pm1.g1 WHERE e1 = pm3.g1.e1)", RealMetadataFactory.example4()); } + @Test public void testRewriteSubqueryCompare() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("select e1 from pm1.g1 where e1 <> ANY (select e1 from pm1.g1)", "SELECT e1 FROM pm1.g1 WHERE e1 <> SOME (SELECT e1 FROM pm1.g1)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteSubqueryCompare1() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("select e1 from pm1.g1 where e1 <> ALL (select e1 from pm1.g1)", "SELECT e1 FROM pm1.g1 WHERE e1 NOT IN (SELECT e1 FROM pm1.g1)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteSubqueryCompare2() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("select e1 from pm1.g1 where e1 = ANY (select e1 from pm1.g1)", "SELECT e1 FROM pm1.g1 WHERE e1 IN (SELECT e1 FROM pm1.g1)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Test public void testRewriteSubqueryCompare3() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("select e1 from pm1.g1 where e1 = ALL (select e1 from pm1.g1)", "SELECT e1 FROM pm1.g1 WHERE e1 = ALL (SELECT e1 FROM pm1.g1)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ + } + @Test public void testSubqueryExpressionJoin() throws Exception { System.setProperty(RuleMergeCriteria.UNNEST_DEFAULT, Boolean.TRUE.toString()); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (Select max(e2) from pm2.g2 where e1 = pm3.g1.e1 having convert(min(e2), string) > pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1, MIN(e2) AS MIN FROM pm2.g2 GROUP BY e1) AS X__1 WHERE (convert(X__1.MIN, string) > pm3.g1.e1) AND (pm3.g1.e2 < X__1.MAX) AND (pm3.g1.e1 = X__1.e1)", RealMetadataFactory.example4());