@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 testDDLMetadata1() throws Exception {
String ddl =
"CREATE foreign FUNCTION sourceFunc(msg varchar) RETURNS varchar options (nameinsource 'a.sourcefunc') "
+ "CREATE foreign FUNCTION b.sourceFunc(msg varchar) RETURNS varchar "
+ "CREATE foreign table X (Y varchar);";
QueryMetadataInterface metadata = RealMetadataFactory.fromDDL(ddl, "x", "phy");
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
capFinder.addCapabilities("phy", caps); // $NON-NLS-1$
ProcessorPlan plan =
helpPlan(
"SELECT sourceFunc(g_0.Y), phy.b.sourceFunc(g_0.Y) FROM phy.X AS g_0",
metadata,
null,
capFinder,
new String[] {"SELECT sourceFunc(g_0.Y), phy.b.sourceFunc(g_0.Y) FROM phy.X AS g_0"},
ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
// ensure that the source query contains the function schemas
HardcodedDataManager dm = new HardcodedDataManager(metadata);
dm.addData("SELECT a.sourcefunc(g_0.Y), b.sourceFunc(g_0.Y) FROM X AS g_0", new List[0]);
TestProcessor.helpProcess(plan, dm, new List[0]);
}
@Test
public void testMustPushdownOverMultipleSources() throws Exception {
QueryMetadataInterface metadata =
RealMetadataFactory.createTransformationMetadata(
RealMetadataFactory.example1Cached().getMetadataStore(),
"example1",
new FunctionTree("foo", new FakeFunctionMetadataSource()));
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setFunctionSupport("misc.namespace.func", true);
capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$
capFinder.addCapabilities("pm2", caps); // $NON-NLS-1$
String sql = "select func(x.e1) from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2"; // $NON-NLS-1$
ProcessorPlan plan =
helpPlan(
sql,
metadata,
null,
capFinder,
new String[] {
"SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0",
"SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0"
},
ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager();
dataManager.addData(
"SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0",
new List[] {Arrays.asList(1, "a")});
dataManager.addData(
"SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0",
new List[] {Arrays.asList(1), Arrays.asList(2)});
TestProcessor.helpProcess(plan, dataManager, new List[] {Arrays.asList("a")});
}
@Test
public void testConcat2() throws Exception {
QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, true);
capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$
String sql = "select concat2(x.e1, x.e1) from pm1.g1 as x"; // $NON-NLS-1$
helpPlan(
sql,
metadata,
null,
capFinder,
new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"},
ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
// cannot pushdown
caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, false);
ProcessorPlan plan =
helpPlan(
sql,
metadata,
null,
capFinder,
new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"},
ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
HardcodedDataManager dataManager = new HardcodedDataManager();
dataManager.addData(
"SELECT g_0.e1 FROM pm1.g1 AS g_0",
new List[] {Arrays.asList("a"), Arrays.asList((String) null)});
TestProcessor.helpProcess(
plan, dataManager, new List[] {Arrays.asList("aa"), Arrays.asList((String) null)});
caps.setFunctionSupport(SourceSystemFunctions.CONCAT, true);
caps.setFunctionSupport(SourceSystemFunctions.IFNULL, true);
caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);
// will get replaced in the LanguageBridgeFactory
helpPlan(
sql,
metadata,
null,
capFinder,
new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"},
ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
}
/**
* 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);
}