@Test public void testNestedTableNoSourcesMerge() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setFunctionSupport("convert", true); //$NON-NLS-1$ caps.setFunctionSupport("array_get", true); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select z.* from pm1.g1, arraytable(cast(pm1.g1.e1 as object) COLUMNS one integer, two integer, three integer) as z", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT convert(array_get(convert(g_0.e1, object), 1), integer), convert(array_get(convert(g_0.e1, object), 2), integer), convert(array_get(convert(g_0.e1, object), 3), integer) FROM pm1.g1 AS g_0 WHERE convert(g_0.e1, object) IS NOT NULL"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testNestedTableNoSourcesMerge1() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setFunctionSupport("convert", true); //$NON-NLS-1$ caps.setFunctionSupport("array_get", true); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select z.* from pm1.g1 inner join arraytable(cast(pm1.g1.e1 as object) COLUMNS one integer, two integer, three integer) as z on (pm1.g1.e2 = z.one)", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT convert(array_get(convert(g_0.e1, object), 1), integer), convert(array_get(convert(g_0.e1, object), 2), integer), convert(array_get(convert(g_0.e1, object), 3), integer) FROM pm1.g1 AS g_0 WHERE (convert(g_0.e1, object) IS NOT NULL) AND (g_0.e2 = convert(array_get(convert(g_0.e1, object), 1), integer))"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testSupportsScalar3() throws Exception { BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setFunctionSupport("now", false); //$NON-NLS-1$ Function func = new Function("NOW", new Expression[] { }); //$NON-NLS-1$ helpTestSupportsScalar(caps, func, false); }
@Test public void testSupportsScalar4() throws Exception { BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setFunctionSupport("now", true); //$NON-NLS-1$ Function func = new Function("NOW", new Expression[] { }); //$NON-NLS-1$ helpTestSupportsScalar(caps, func, true); }
@Test public void testHavingPushdown1() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = getAggregateCapabilities(); caps.setCapabilitySupport(Capability.QUERY_HAVING, false); caps.setFunctionSupport("concat", true); caps.setFunctionSupport("convert", true); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("select max(e2) from pm1.g2 group by e1 having min(e2) || e1 > '1'", RealMetadataFactory.example1Cached(), null, capFinder, //$NON-NLS-1$ new String[]{"SELECT v_0.c_0 FROM (SELECT MAX(g_0.e2) AS c_0, MIN(g_0.e2) AS c_1, g_0.e1 AS c_2 FROM pm1.g2 AS g_0 GROUP BY g_0.e1) AS v_0 WHERE concat(convert(v_0.c_1, string), v_0.c_2) > '1'"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testUpdate2() throws Exception { BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setFunctionSupport(SourceSystemFunctions.CONVERT, true); DefaultCapabilitiesFinder dcf = new DefaultCapabilitiesFinder(bsc); helpPlan("Update pm1.g1 Set pm1.g1.e1= LTRIM('MyString'), pm1.g1.e2= 1 where pm1.g1.e2= convert(pm1.g1.e4, integer)", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "UPDATE pm1.g1 SET e1 = 'MyString', e2 = 1 WHERE pm1.g1.e2 = convert(pm1.g1.e4, integer)"}, dcf, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ }
@Test public void testDelete() throws Exception { BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setFunctionSupport(SourceSystemFunctions.CONVERT, true); DefaultCapabilitiesFinder dcf = new DefaultCapabilitiesFinder(bsc); helpPlan("Delete from pm1.g1 where pm1.g1.e1 = cast(pm1.g1.e2 AS string)", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "DELETE FROM pm1.g1 WHERE pm1.g1.e1 = convert(pm1.g1.e2, string)"}, dcf, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ }
public ProcessorPlan helpTestMultiSourcePlan(QueryMetadataInterface metadata, String userSql, String multiModel, int sourceCount, ProcessorDataManager dataMgr, List<?>[] expectedResults, VDBMetaData vdb, List<?> params, Options options) throws Exception { BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true); bsc.setCapabilitySupport(Capability.ROW_LIMIT, true); bsc.setCapabilitySupport(Capability.QUERY_GROUP_BY, true); bsc.setFunctionSupport(SourceSystemFunctions.CONCAT, true); return helpTestMultiSourcePlan(metadata, userSql, multiModel, sourceCount, dataMgr, expectedResults, vdb, params, options, bsc); }
@Test public void testUnaliased() throws Exception { String sql = "SELECT x.count + 1 FROM agg x"; //$NON-NLS-1$ TransformationMetadata metadata = RealMetadataFactory.fromDDL("create foreign table smalla (intkey integer); create view agg (count integer) as select intkey from smalla order by intkey limit 1", "x", "y"); BasicSourceCapabilities bsc = TestAggregatePushdown.getAggregateCapabilities(); bsc.setFunctionSupport("+", true); TestOptimizer.helpPlan(sql, metadata, new String[] {"SELECT (v_0.c_0 + 1) FROM (SELECT g_0.intkey AS c_0 FROM y.smalla AS g_0 ORDER BY c_0 LIMIT 1) AS v_0"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); }
@Test public void testMergeGroupBy() throws Exception { BasicSourceCapabilities caps = TestAggregatePushdown.getAggregateCapabilities(); caps.setFunctionSupport("+", true); //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x FROM (select c.e1 as x from (select e1 from pm1.g1) as c, pm1.g2 as d) as a group by x", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(caps), new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 GROUP BY g_0.e1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testCopyCriteriaWithTransitivePushdown3() throws TeiidComponentException, TeiidProcessingException{ BasicSourceCapabilities caps = getTypicalCapabilities(); caps.setFunctionSupport(SourceSystemFunctions.CONVERT, true); ProcessorPlan plan = helpPlan("select pm1.g1.e1 from pm1.g1, pm1.g2, pm1.g3 where pm1.g1.e1 = pm1.g2.e1 and pm1.g1.e1 = pm1.g3.e2 and pm1.g3.e2 = pm1.g2.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1, pm1.g3 AS g_2 WHERE (g_0.e1 = g_1.e1) AND (g_0.e1 = convert(g_2.e2, string)) AND (convert(g_2.e2, string) = g_1.e1)" } , new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testPushMultiGroupJoinCriteria() throws Exception { String sql = "select bqt1.smalla.intkey, bqt1.smallb.intkey from bqt1.smalla right outer join (bqt1.smallb cross join (bqt1.mediuma cross join bqt1.mediumb)) on bqt1.smalla.stringkey = bqt1.smallb.stringkey" //$NON-NLS-1$ +" where bqt1.smallb.intkey + bqt1.mediuma.intkey + bqt1.mediumb.intkey = 1"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setFunctionSupport("+", true); //$NON-NLS-1$ capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ // Plan query ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), null, capFinder, new String[] {"SELECT g_3.IntKey, g_2.IntKey FROM (BQT1.MediumA AS g_0 CROSS JOIN BQT1.MediumB AS g_1) INNER JOIN (BQT1.SmallB AS g_2 LEFT OUTER JOIN BQT1.SmallA AS g_3 ON g_3.StringKey = g_2.StringKey) ON ((g_2.IntKey + g_0.IntKey) + g_1.IntKey) = 1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testCase4312() { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true); caps.setFunctionSupport("+", true); //$NON-NLS-1$ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ helpPlan("select ? + 1, pm1.g1.e1 AS EXPR_1 FROM pm1.g1", example1(), null, capFinder, //$NON-NLS-1$ new String[] { "SELECT (? + 1) AS expr, pm1.g1.e1 FROM pm1.g1"}, true); //$NON-NLS-1$ }
@Test public void testRecursivePushdown() throws TeiidComponentException, TeiidProcessingException { String sql = "WITH t(n) AS ( select e2 from pm1.g1 UNION SELECT n+1 FROM t WHERE n < 64 ) SELECT n FROM t"; //$NON-NLS-1$ BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true); bsc.setCapabilitySupport(Capability.RECURSIVE_COMMON_TABLE_EXPRESSIONS, true); bsc.setFunctionSupport("+", true); CapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc); TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"WITH t (n) AS (SELECT g_0.e2 FROM pm1.g1 AS g_0 UNION SELECT (g_0.n + 1) FROM t AS g_0 WHERE g_0.n < 64) SELECT g_0.n FROM t AS g_0"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); }
@Test public void testOuterPreservation() throws Exception { String sql = "select pm1.g1.e2, pm1.g2.e2 from pm1.g1 left outer join pm1.g2 on (pm1.g1.e2 = pm1.g2.e2) where pm1.g2.e1 = 'a'"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.ANY); caps.setFunctionSupport("+", true); //$NON-NLS-1$ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e2, g_1.e2 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g2 AS g_1 ON g_0.e2 = g_1.e2 WHERE g_1.e1 = 'a'"}, capFinder, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testProjectionRaisingWithComplexOrdering() { String sql = "select e1 || 1, e2 / 2 from pm1.g1 as x order by e1 || 1 limit 2"; //$NON-NLS-1$ BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setFunctionSupport(SourceSystemFunctions.CONCAT, true); bsc.setCapabilitySupport(Capability.ROW_LIMIT, true); CapabilitiesFinder finder = new DefaultCapabilitiesFinder(bsc); RelationalPlan plan = (RelationalPlan)helpPlan(sql, RealMetadataFactory.example1Cached(), null, finder, new String[] {"SELECT concat(g_0.e1, '1') AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0 LIMIT 2"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ assertTrue(plan.getRootNode() instanceof ProjectNode); }
@Test public void testOuterPreservation1() throws Exception { String sql = "select pm1.g1.e2, pm1.g2.e2 from pm1.g1 inner join pm1.g2 on (pm1.g1.e2 = pm1.g2.e2)"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.ANY); caps.setFunctionSupport("+", true); //$NON-NLS-1$ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e2, g_1.e2 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g2 AS g_1 ON g_0.e2 = g_1.e2 WHERE g_1.e2 IS NOT NULL"}, capFinder, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testScalarFunction1() throws Exception { TransformationMetadata metadata = RealMetadataFactory.example1Cached(); Object modelID = metadata.getMetadataStore().getSchema("PM1"); FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true); caps.setFunctionSupport("curtime", true); //$NON-NLS-1$ capFinder.addCapabilities("pm1", caps); helpTestVisitor("curtime() = {t'10:00:00'}", modelID, metadata, capFinder, true, false); }
@Test public void testOuterRestriction() throws Exception { String sql = "select pm1.g1.e2, pm1.g2.e2 from pm1.g1 inner join pm1.g2 on (pm1.g1.e2 + pm1.g2.e2 = 5)"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false); caps.setCapabilitySupport(Capability.CRITERIA_ISNULL, false); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.ANY); caps.setFunctionSupport("+", true); //$NON-NLS-1$ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e2 FROM pm1.g2 AS g_0", "SELECT g_0.e2 FROM pm1.g1 AS g_0"}, capFinder, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testTimestampAddRestriction() throws Exception { String sql = "SELECT timestampadd(sql_tsi_second, 1, timestampvalue) from bqt1.smalla"; //$NON-NLS-1$ BasicSourceCapabilities bsc = getTypicalCapabilities(); bsc.setFunctionSupport(SourceSystemFunctions.TIMESTAMPADD, true); bsc.setCapabilitySupport(Capability.ONLY_TIMESTAMPADD_LITERAL, true); TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT timestampadd(sql_tsi_second, 1, g_0.TimestampValue) FROM BQT1.SmallA AS g_0"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); sql = "SELECT timestampadd(sql_tsi_second, intkey, timestampvalue) from bqt1.smalla"; //$NON-NLS-1$ //TODO - when possible this should modify the match pattern to use the required escape TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.IntKey, g_0.TimestampValue FROM BQT1.SmallA AS g_0"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); bsc.setCapabilitySupport(Capability.ONLY_TIMESTAMPADD_LITERAL, false); TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT timestampadd(sql_tsi_second, g_0.IntKey, g_0.TimestampValue) FROM BQT1.SmallA AS g_0"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); }