Author: loleary Date: 2009-04-29 16:16:12 -0400 (Wed, 29 Apr 2009) New Revision: 852 Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java trunk/engine/src/test/java/com/metamatrix/query/rewriter/TestQueryRewriter.java Log: TEIID-539: Added test cases for use of BETWEEN expression Reviewed by: jhelbling Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-04-29 18:47:52 UTC (rev 851) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-04-29 20:16:12 UTC (rev 852) @@ -7187,6 +7187,91 @@ helpPlan(sql, FakeMetadataFactory.exampleBQT(), new String[] {}); } + /** + * Test <code>QueryOptimizer</code>'s ability to plan a fully-pushed-down + * query containing a <code>BETWEEN</code> comparison in the queries + * <code>WHERE</code> statement. + * <p> + * For example: + * <p> + * SELECT * FROM pm1.g1 WHERE e2 BETWEEN 1 AND 2 + */ + public void testBetween() { + helpPlan("select * from pm1.g1 where e2 between 1 and 2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE (e2 >= 1) AND (e2 <= 2)"} ); //$NON-NLS-1$ + } + + /** + * Test <code>QueryOptimizer</code>'s ability to plan a fully-pushed-down + * query containing a <code>CASE</code> expression in which a + * <code>BETWEEN</code> comparison is used in the queries + * <code>SELECT</code> statement. + * <p> + * For example: + * <p> + * SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1 + */ + public void testBetweenInCase() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + helpPlan("select case when e2 between 3 and 5 then e2 else -1 end from pm1.g1", //$NON-NLS-1$ + FakeMetadataFactory.example1Cached(), null, capFinder, + new String[] { "SELECT CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END FROM pm1.g1"}, //$NON-NLS-1$ + TestOptimizer.SHOULD_SUCCEED); + } + + /** + * Test <code>QueryOptimizer</code>'s ability to plan a fully-pushed-down + * query containing an aggregate SUM with a <code>CASE</code> expression + * in which a <code>BETWEEN</code> comparison is used in the queries + * <code>SELECT</code> statement. + * <p> + * For example: + * <p> + * SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1 + */ + public void testBetweenInCaseInSum() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + helpPlan("select sum(case when e2 between 3 and 5 then e2 else -1 end) from pm1.g1", //$NON-NLS-1$ + FakeMetadataFactory.example1Cached(), null, capFinder, + new String[] { "SELECT SUM(CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END) FROM pm1.g1"}, //$NON-NLS-1$ + TestOptimizer.SHOULD_SUCCEED); + } + + /** + * Test <code>QueryOptimizer</code>'s ability to plan a fully-pushed-down + * query containing an aggregate SUM with a <code>CASE</code> expression + * in which a <code>BETWEEN</code> comparison is used in the queries + * <code>SELECT</code> statement and a GROUP BY is specified. + * <p> + * For example: + * <p> + * SELECT e1, SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) + * FROM pm1.g1 GROUP BY e1 + */ + public void testBetweenInCaseInSumWithGroupBy() { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + helpPlan("select sum(case when e2 between 3 and 5 then e2 else -1 end) from pm1.g1 group by e1", //$NON-NLS-1$ + FakeMetadataFactory.example1Cached(), null, capFinder, + new String[] { "SELECT SUM(CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END) FROM pm1.g1 GROUP BY e1"}, //$NON-NLS-1$ + TestOptimizer.SHOULD_SUCCEED); + } + private static final boolean DEBUG = false; } Modified: trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java 2009-04-29 18:47:52 UTC (rev 851) +++ trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java 2009-04-29 20:16:12 UTC (rev 852) @@ -256,7 +256,7 @@ id = processor.getResultsID(); processor.process(); assertEquals(0, bufferMgr.getPinnedCount()); - examineResults(expectedResults, bufferMgr, processor.getResultsID()); + if ( expectedResults != null ) examineResults(expectedResults, bufferMgr, processor.getResultsID()); } finally { bufferMgr.removeTupleSource(id); } @@ -4439,6 +4439,134 @@ helpProcess(plan, dataManager, expected); } + /** + * Test <code>QueryProcessor</code>'s ability to process a query containing + * a <code>CASE</code> expression in which a <code>BETWEEN</code> + * comparison is used in the queries <code>SELECT</code> statement. + * <p> + * For example: + * <p> + * SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1 + */ + public void testBetweenInCase() { + // Create query + final String sql = "SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ + + // Create expected results + List[] expected = new List[] { + Arrays.asList(new Object[] { new Integer(-1) }), + Arrays.asList(new Object[] { new Integer(-1) }), + Arrays.asList(new Object[] { new Integer(3) }), + Arrays.asList(new Object[] { new Integer(-1) }), + Arrays.asList(new Object[] { new Integer(-1) }), + Arrays.asList(new Object[] { new Integer(-1) }) + }; + + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + // Plan query + ProcessorPlan plan = helpGetPlan(sql, FakeMetadataFactory.example1Cached()); + + // Run query + helpProcess(plan, dataManager, expected); + } + + /** + * Test <code>QueryProcessor</code>'s ability to process a query containing + * an aggregate SUM with a <code>CASE</code> expression in which a + * <code>BETWEEN</code> comparison is used in the queries <code>SELECT</code> + * statement. + * <p> + * For example: + * <p> + * SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1 + */ + public void testBetweenInCaseInSum() { + // Create query + final String sql = "SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ + + // Create expected results + List[] expected = new List[] { + Arrays.asList(new Object[] { new Long(-2) }) + }; + + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + // Plan query + ProcessorPlan plan = helpGetPlan(sql, FakeMetadataFactory.example1Cached()); + + // Run query + helpProcess(plan, dataManager, expected); + } + + /** + * Test <code>QueryProcessor</code>'s ability to process a query containing + * an aggregate SUM with a <code>CASE</code> expression in which a + * <code>BETWEEN</code> comparison is used in the queries <code>SELECT</code> + * statement and a GROUP BY is specified. + * <p> + * For example: + * <p> + * SELECT e1, SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) + * FROM pm1.g1 GROUP BY e1 ORDER BY e1 + */ + public void testBetweenInCaseInSumWithGroupBy() { + // Create query + final String sql = "SELECT e1, SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1 GROUP BY e1 ORDER BY e1"; //$NON-NLS-1$ + + // Create expected results + List[] expected = new List[] { + Arrays.asList(new Object[] { null, new Long(-1) }), + Arrays.asList(new Object[] { "a", new Long(1) }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "b", new Long(-1) }), //$NON-NLS-1$ + Arrays.asList(new Object[] { "c", new Long(-1) }) //$NON-NLS-1$ + }; + + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + // Plan query + ProcessorPlan plan = helpGetPlan(sql, FakeMetadataFactory.example1Cached()); + + // Run query + helpProcess(plan, dataManager, expected); + } + + /** + * Test <code>QueryProcessor</code>'s ability to process a query containing + * an aggregate COUNT with a <code>CASE</code> expression in which a + * <code>BETWEEN</code> comparison is used in the queries <code>SELECT</code> + * statement. + * <p> + * For example: + * <p> + * SELECT COUNT(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 END) FROM pm1.g1 + */ + public void testBetweenInCaseInCount() { + // Create query + final String sql = "SELECT COUNT(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 END) FROM pm1.g1"; //$NON-NLS-1$ + + // Create expected results + List[] expected = new List[] { + Arrays.asList(new Object[] { new Integer(1) }) + }; + + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + // Plan query + ProcessorPlan plan = helpGetPlan(sql, FakeMetadataFactory.example1Cached()); + + // Run query + helpProcess(plan, dataManager, expected); + } + public void testCase() { // Create query String sql = "SELECT e2, CASE e2 WHEN 1 THEN 2 ELSE 3 END FROM pm1.g1 WHERE e2 BETWEEN 1 AND 2"; //$NON-NLS-1$ Modified: trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java 2009-04-29 18:47:52 UTC (rev 851) +++ trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java 2009-04-29 20:16:12 UTC (rev 852) @@ -4656,5 +4656,47 @@ public void testSecondPassFunctionResolving() { helpResolve("SELECT pm1.g1.e1 FROM pm1.g1 where lower(?) = e1 "); //$NON-NLS-1$ } + + /** + * Test <code>QueryResolver</code>'s ability to resolve a query that + * contains an aggregate <code>SUM</code> which uses a <code>CASE</code> + * expression which contains <code>BETWEEN</code> criteria as its value. + * <p> + * For example: + * <p> + * SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1 + */ + public void testAggregateWithBetweenInCaseInSelect() { + String sql = "SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ + helpResolve(sql); + } + /** + * Test <code>QueryResolver</code>'s ability to resolve a query that + * contains a <code>CASE</code> expression which contains + * <code>BETWEEN</code> criteria in the queries <code>SELECT</code> clause. + * <p> + * For example: + * <p> + * SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1 + */ + public void testBetweenInCaseInSelect() { + String sql = "SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ + helpResolve(sql); + } + + /** + * Test <code>QueryResolver</code>'s ability to resolve a query that + * contains a <code>CASE</code> expression which contains + * <code>BETWEEN</code> criteria in the queries <code>WHERE</code> clause. + * <p> + * For example: + * <p> + * SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END + */ + public void testBetweenInCase() { + String sql = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END"; //$NON-NLS-1$ + helpResolve(sql); + } + } \ No newline at end of file Modified: trunk/engine/src/test/java/com/metamatrix/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/rewriter/TestQueryRewriter.java 2009-04-29 18:47:52 UTC (rev 851) +++ trunk/engine/src/test/java/com/metamatrix/query/rewriter/TestQueryRewriter.java 2009-04-29 20:16:12 UTC (rev 852) @@ -62,6 +62,7 @@ import com.metamatrix.query.sql.symbol.ExpressionSymbol; import com.metamatrix.query.sql.symbol.GroupSymbol; import com.metamatrix.query.sql.symbol.Reference; +import com.metamatrix.query.sql.symbol.SearchedCaseExpression; import com.metamatrix.query.sql.symbol.SingleElementSymbol; import com.metamatrix.query.sql.visitor.CorrelatedReferenceCollectorVisitor; import com.metamatrix.query.unittest.FakeMetadataFacade; @@ -2103,5 +2104,120 @@ } } - + + /** + * Test <code>QueryRewriter</code>'s ability to rewrite a query that + * contains an aggregate function which uses a <code>CASE</code> + * expression which contains <code>BETWEEN</code> criteria as its value. + * <p> + * An aggregate function list is defined and queries are created that + * use each function from the list. The list includes: + * <p> + * "SUM", "MAX", "MIN", "AVG", "COUNT" + * <p> + * It is expected that the BETWEEN expression will be rewritten as + * <code>CompoundCriteria</code>. + * <p> + * <table> + * <tr><th align="left" colspan=2>For example: + * <tr><td width="10*"><td>SELECT SUM(CASE WHEN e2 BETWEEN 3 AND 5 + * THEN e2 ELSE -1 END) FROM pm1.g1 + * <tr><th align="left" colspan=2>Is rewritten as: + * <tr><td width="10*"><td>SELECT SUM(CASE WHEN (e2 >= 3) AND (e2 <= 5) + * THEN e2 ELSE -1 END) FROM pm1.g1 + * </table> + * + * @see com.metamatrix.query.rewriter.QueryRewriter + * @see com.metamatrix.query.sql.lang.BetweenCriteria + * @see com.metamatrix.query.sql.lang.CompoundCriteria + * @see com.metamatrix.query.sql.symbol.AggregateSymbol + * @see com.metamatrix.query.sql.symbol.SearchedCaseExpression + */ + public void testAggregateWithBetweenInCaseInSelect() { + // Define a list of aggregates to test against + List<String> aggregateCommands = Arrays.asList( new String[] { "SUM", "MAX", "MIN", "AVG", "COUNT" } ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ + + // Define a query and the expected rewritten query + // ?AGGREGATE? represents the string substitution for an aggregate from aggregateCommands + String sqlBefore = "SELECT ?AGGREGATE?(CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ + String sqlAfter = "SELECT ?AGGREGATE?(CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END) FROM pm1.g1"; //$NON-NLS-1$ + + // Iterate through the aggregateCommands + for ( String aCmd : aggregateCommands ) { + // Replace ?AGGREGATE? with the command from aggregateCommands + String sql = sqlBefore.replace("?AGGREGATE?", aCmd); //$NON-NLS-1$ + String exSql = sqlAfter.replace("?AGGREGATE?", aCmd); //$NON-NLS-1$ + // Test QueryRewriter + Command cmd = helpTestRewriteCommand( sql, exSql ); + // Check the rewritten command to verify that CompundCriteria replaced BetweenCriteria + CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((ExpressionSymbol) cmd.getProjectedSymbols().get(0)).getExpression()).getWhen().get(0); + assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ + assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ + } + } + + /** + * Test <code>QueryRewriter</code>'s ability to rewrite a query that + * contains a <code>CASE</code> expression which contains + * <code>BETWEEN</code> criteria in the queries <code>SELECT</code> clause. + * <p> + * It is expected that the BETWEEN expression will be rewritten as + * <code>CompoundCriteria</code>. + * <p> + * <table> + * <tr><th align="left" colspan=2>For example: + * <tr><td width="10*"><td>SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 + * ELSE -1 END FROM pm1.g1 + * <tr><th align="left" colspan=2>Is rewritten as: + * <tr><td width="10*"><td>SELECT CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 + * ELSE -1 END FROM pm1.g1 + * </table> + * + * @see com.metamatrix.query.rewriter.QueryRewriter + * @see com.metamatrix.query.sql.lang.BetweenCriteria + * @see com.metamatrix.query.sql.lang.CompoundCriteria + * @see com.metamatrix.query.sql.symbol.SearchedCaseExpression + */ + public void testBetweenInCaseInSelect() { + String sqlBefore = "SELECT CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ + String sqlAfter = "SELECT CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END FROM pm1.g1"; //$NON-NLS-1$ + + Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); + CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((ExpressionSymbol) cmd.getProjectedSymbols().get(0)).getExpression()).getWhen().get(0); + assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ + assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ + } + + /** + * Test <code>QueryRewriter</code>'s ability to rewrite a query that + * contains a <code>CASE</code> expression which contains + * <code>BETWEEN</code> criteria in the queries <code>WHERE</code> clause. + * <p> + * It is expected that the BETWEEN expression will be rewritten as + * <code>CompoundCriteria</code>. + * <p> + * <table> + * <tr><th align="left" colspan=2>For example: + * <tr><td width="10*"><td>SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 + * BETWEEN 3 AND 5 THEN e2 ELSE -1 END + * <tr><th align="left" colspan=2>Is rewritten as: + * <tr><td width="10*"><td>SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN + * (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END + * </table> + * + * @see com.metamatrix.query.rewriter.QueryRewriter + * @see com.metamatrix.query.sql.lang.BetweenCriteria + * @see com.metamatrix.query.sql.lang.CompoundCriteria + * @see com.metamatrix.query.sql.symbol.SearchedCaseExpression + */ + public void testBetweenInCase() { + String sqlBefore = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END"; //$NON-NLS-1$ + String sqlAfter = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN (e2 >= 3) AND (e2 <= 5) THEN e2 ELSE -1 END"; //$NON-NLS-1$ + + Command cmd = helpTestRewriteCommand( sqlBefore, sqlAfter ); + CompoundCriteria ccrit = (CompoundCriteria) ((SearchedCaseExpression) ((CompareCriteria) ((Query) cmd).getCriteria()).getRightExpression()).getWhen().get(0); + assertEquals( "e2 >= 3", ccrit.getCriteria(0).toString() ); //$NON-NLS-1$ + assertEquals( "e2 <= 5", ccrit.getCriteria(1).toString() ); //$NON-NLS-1$ + } + }