@Test
public void testPartialProjection() throws TeiidComponentException, TeiidProcessingException {
String sql =
"SELECT user() AS a, "
+ " AVG(e2) OVER ( ) AS b,"
+ " MAX(e2) OVER ( ) AS b"
+ " FROM pm1.g1";
HardcodedDataManager dataMgr = new HardcodedDataManager();
dataMgr.addData(
"SELECT ROUND(convert((g_0.L_DISCOUNT - AVG(g_0.L_DISCOUNT) OVER ()), FLOAT), 0) FROM TPCR_Oracle_9i.LINEITEM AS g_0",
Arrays.asList(2.0f),
Arrays.asList(2.0f));
BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
bsc.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
bsc.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true);
bsc.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true);
ProcessorPlan plan =
TestOptimizer.helpPlan(
sql,
RealMetadataFactory.example1Cached(),
null,
new DefaultCapabilitiesFinder(bsc),
new String[] {"SELECT AVG(g_0.e2) OVER (), g_0.e2 FROM pm1.g1 AS g_0"},
ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
checkNodeTypes(
plan,
new int[] {1, 1, 1},
new Class<?>[] {AccessNode.class, WindowFunctionProjectNode.class, ProjectNode.class});
List<?>[] expected =
new List<?>[] {
Arrays.asList(null, BigDecimal.valueOf(1.5), 2),
Arrays.asList(null, BigDecimal.valueOf(1.5), 2)
};
dataMgr.addData(
"SELECT AVG(g_0.e2) OVER (), g_0.e2 FROM pm1.g1 AS g_0", //$NON-NLS-1$
Arrays.asList(1.5, 2),
Arrays.asList(1.5, 1));
helpProcess(plan, dataMgr, expected);
// should completely eliminate the window function node
plan =
TestOptimizer.helpPlan(
"SELECT uuid() AS a, AVG(e2) OVER ( ) AS b FROM pm1.g1",
RealMetadataFactory.example1Cached(),
null,
new DefaultCapabilitiesFinder(bsc),
new String[] {"SELECT AVG(g_0.e2) OVER () FROM pm1.g1 AS g_0"},
ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
checkNodeTypes(plan, new int[] {1, 1}, new Class<?>[] {AccessNode.class, ProjectNode.class});
}
@Test
public void testDeleteComplex() throws Exception {
String userSql = "delete from vm1.gx where e2 < 10"; // $NON-NLS-1$
String viewSql = "select g2.* from pm1.g1 inner join pm1.g2 on g1.e1 = g2.e1";
HardcodedDataManager dm = new HardcodedDataManager();
dm.addData(
"SELECT g_1.e2 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_1.e2 < 10)",
new List[] {Arrays.asList(2)});
dm.addData("DELETE FROM pm1.g2 WHERE pm1.g2.e2 = 2", new List[] {Arrays.asList(1)});
helpTest(
userSql,
viewSql,
"CREATE VIRTUAL PROCEDURE\nBEGIN ATOMIC\nDECLARE integer VARIABLES.ROWS_UPDATED = 0;\nLOOP ON (SELECT pm1.g2.e2 AS s_0 FROM pm1.g1 INNER JOIN pm1.g2 ON g1.e1 = g2.e1 WHERE pm1.g2.e2 < 10) AS X\nBEGIN\nDELETE FROM pm1.g2 WHERE pm1.g2.e2 = X.s_0;\nVARIABLES.ROWS_UPDATED = (VARIABLES.ROWS_UPDATED + 1);\nEND\nSELECT VARIABLES.ROWS_UPDATED;\nEND",
dm);
}
/**
* Note that we've optimized the ordering to be performed prior to the windowing. If we change the
* windowing logic to not preserve the incoming row ordering, then this optimization will need to
* change
*
* @throws Exception
*/
@Test
public void testCountDuplicates() throws Exception {
String sql = "select e1, count(e1) over (order by e1) as c from pm1.g1 order by e1";
List<?>[] expected =
new List[] {
Arrays.asList("a", 2), Arrays.asList("a", 2), Arrays.asList("b", 3),
};
HardcodedDataManager dataManager = new HardcodedDataManager();
dataManager.addData(
"SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0",
new List[] {Arrays.asList("a"), Arrays.asList("a"), Arrays.asList("b")});
ProcessorPlan plan =
helpGetPlan(sql, RealMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder());
helpProcess(plan, dataManager, expected);
}
@Test
public void testUpdateComplex() throws Exception {
String userSql = "update vm1.gx set e1 = e2 where e3 is null"; // $NON-NLS-1$
String viewSql = "select g2.* from pm1.g1 inner join pm1.g2 on g1.e1 = g2.e1";
HardcodedDataManager dm = new HardcodedDataManager();
dm.addData(
"SELECT convert(g_1.e2, string), g_1.e2 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_1.e3 IS NULL)",
new List[] {Arrays.asList("1", 1)});
dm.addData(
"UPDATE pm1.g2 SET e1 = convert(pm1.g2.e2, string) WHERE pm1.g2.e2 = 1",
new List[] {Arrays.asList(1)});
helpTest(
userSql,
viewSql,
"CREATE VIRTUAL PROCEDURE\nBEGIN ATOMIC\nDECLARE integer VARIABLES.ROWS_UPDATED = 0;\nLOOP ON (SELECT convert(pm1.g2.e2, string) AS s_0, pm1.g2.e2 AS s_1 FROM pm1.g1 INNER JOIN pm1.g2 ON g1.e1 = g2.e1 WHERE pm1.g2.e3 IS NULL) AS X\nBEGIN\nUPDATE pm1.g2 SET e1 = convert(pm1.g2.e2, string) WHERE pm1.g2.e2 = X.s_1;\nVARIABLES.ROWS_UPDATED = (VARIABLES.ROWS_UPDATED + 1);\nEND\nSELECT VARIABLES.ROWS_UPDATED;\nEND",
dm);
}