3:16 p.m.
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$
+ }
+
}