private static void setSupports(Object connectorID, BasicSourceCapabilities tgtCaps, Capability cap, boolean supports, Capability... required) { if (!supports) { return; } for (Capability capability : required) { if (!tgtCaps.supportsCapability(capability)) { LogManager.logWarning(LogConstants.CTX_CONNECTOR, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30003, cap, capability, connectorID)); supports = false; } } tgtCaps.setCapabilitySupport(cap, supports); }
@Test public void testSupportsAggregate16() throws Exception { BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_DISTINCT, true); AggregateSymbol aggregate = new AggregateSymbol(NonReserved.MAX, true, new ElementSymbol("x")); //$NON-NLS-1$ //$NON-NLS-2$ helpTestSupportsAggregateFunction(caps, aggregate, true); }
@Test public void testDeleteSubqueryCorrelated() throws Exception { BasicSourceCapabilities bsc = getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); TestOptimizer.helpPlan("delete FROM bqt1.smalla x where intkey = (select intkey from bqt1.smallb where intkey < x.intkey)", //$NON-NLS-1$ RealMetadataFactory.exampleBQTCached(), null, new DefaultCapabilitiesFinder(bsc), new String[] {"DELETE FROM BQT1.SmallA WHERE BQT1.SmallA.IntKey = (SELECT g_0.IntKey FROM BQT1.SmallB AS g_0 WHERE g_0.IntKey < BQT1.SmallA.IntKey)"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testSimpleMergeUnderUnionWithJoin() { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.QUERY_UNION, true); caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ TestOptimizer.helpPlan("select * from (SELECT x.x, x.e2 FROM (select '1' as x, pm1.g1.e2 from pm1.g1, pm1.g2 where pm1.g1.e1 = pm1.g2.e1 group by pm1.g1.e2, pm1.g1.e3 || '1') x union all select e1, 1 from pm1.g2) as y where x = '1'", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, capFinder, new String[] { "SELECT pm1.g2.e1 FROM pm1.g2", "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3 FROM pm1.g1"}, TestOptimizer.SHOULD_SUCCEED); }
@Test public void testSupportsAggregate13() throws Exception { BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, false); AggregateSymbol aggregate = new AggregateSymbol(NonReserved.MAX, false, new ElementSymbol("x")); //$NON-NLS-1$ //$NON-NLS-2$ helpTestSupportsAggregateFunction(caps, aggregate, false); }
private FakeCapabilitiesFinder getFinder(Capability property, boolean supported) { // Set up capabilities FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder(); BasicSourceCapabilities sourceCaps = new BasicSourceCapabilities(); sourceCaps.setCapabilitySupport(property, supported); finder.addCapabilities("pm1", sourceCaps); //$NON-NLS-1$ return finder; }
@Test public void testMatchCriteriaSuccess2() throws Exception { TransformationMetadata metadata = RealMetadataFactory.example1Cached(); Object modelID = metadata.getMetadataStore().getSchema("PM1"); FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.CRITERIA_LIKE, true); caps.setCapabilitySupport(Capability.CRITERIA_LIKE_ESCAPE, true); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ helpTestVisitor("pm1.g1.e1 LIKE 'x' ESCAPE '#'", modelID, metadata, capFinder, true, false); }
@Test public void testSetCriteria7() throws Exception { TransformationMetadata metadata = RealMetadataFactory.example1Cached(); Object modelID = metadata.getMetadataStore().getSchema("PM1"); FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.CRITERIA_IN, true); caps.setCapabilitySupport(Capability.CRITERIA_NOT, true); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ helpTestVisitor("pm1.g1.e1 NOT IN ('x')", modelID, metadata, capFinder, true, false); //$NON-NLS-1$ }
@Test public void testLeftOuterAssocitivtyNullDependent() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true); TestOptimizer.helpPlan("SELECT pm1.g1.e3 from pm1.g1 left outer join pm2.g2 on pm1.g1.e1 = pm2.g2.e1 or pm2.g2.e1 is null left outer join pm2.g3 on pm2.g2.e2 = pm2.g3.e2", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e2 AS c_0 FROM pm2.g3 AS g_0 ORDER BY c_0", "SELECT g_0.e1, g_0.e2 FROM pm2.g2 AS g_0", "SELECT g_0.e1, g_0.e3 FROM pm1.g1 AS g_0"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testLeftOuterAssocitivtyLeftLinear() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true); TestOptimizer.helpPlan("SELECT pm1.g1.e3 from pm1.g1 left outer join pm2.g2 on pm1.g1.e1 = pm2.g2.e1 left outer join pm2.g3 on pm2.g2.e2 = pm2.g3.e2", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0", "SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 LEFT OUTER JOIN pm2.g3 AS g_1 ON g_0.e2 = g_1.e2 ORDER BY c_0"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testLeftOuterAssocitivtyLeftLinearSwap() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true); TestOptimizer.helpPlan("SELECT pm1.g1.e3 from pm1.g1 left outer join pm2.g2 on pm1.g1.e1 = pm2.g2.e1 left outer join pm1.g3 on pm1.g1.e2 = pm1.g3.e2", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 ORDER BY c_0", "SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g3 AS g_1 ON g_0.e2 = g_1.e2 ORDER BY c_0"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testLeftOuterAssocitivtyWithMakedep() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true); TestOptimizer.helpPlan("SELECT pm1.g1.e3 from (pm1.g1 " + "left outer join /*+ MAKEDEP */ pm2.g2 on pm1.g1.e1 = pm2.g2.e1) " + "left outer join pm1.g2 on pm1.g1.e1 = pm1.g2.e1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>) ORDER BY c_0", "SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g2 AS g_1 ON g_0.e1 = g_1.e1 ORDER BY c_0"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
/** * Note the pre/post affect of the having in the source query */ @Test public void testRollupHaving() throws Exception { String sql = "select e1, sum(e2) from pm1.g1 group by rollup(e1) having e1 is not null"; //$NON-NLS-1$ BasicSourceCapabilities caps = getAggregateCapabilities(); caps.setCapabilitySupport(Capability.QUERY_GROUP_BY_ROLLUP, true); helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1, SUM(g_0.e2) FROM pm1.g1 AS g_0 WHERE g_0.e1 IS NOT NULL GROUP BY ROLLUP(g_0.e1) HAVING g_0.e1 IS NOT NULL"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); }
@Test public void testFilterPlanningFullyPushed() throws Exception { String ddl = "create foreign table People_Groups (user_dn string options (nameinsource 'dn'), group_dn string options (nameinsource 'memberOf', \"teiid_rel:partial_filter\" true)) options (nameinsource 'ou=people,dc=metamatrix,dc=com')"; BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.PARTIAL_FILTERS, true); ProcessorPlan plan = TestOptimizer.helpPlan("select user_dn from people_groups where group_dn = 'a'", RealMetadataFactory.fromDDL(ddl, "x", "y"), new String[] {"SELECT g_0.user_dn FROM y.People_Groups AS g_0 WHERE g_0.group_dn = 'a'"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testOuterRestrictionMultiLevel() throws Exception { String ddl = "create foreign table g1 (e1 integer primary key, e2 integer); " + "create foreign table g2 (e1 integer primary key, e2 integer, FOREIGN KEY (e2) REFERENCES g1 (e1));" + "create foreign table g3 (e1 integer primary key, e2 integer, FOREIGN KEY (e2) REFERENCES g2 (e1));"; String sql = "select g1.e2, g2.e2 from g1, g2, g3 where g1.e1 = g2.e2 and g2.e1 = g3.e2"; //$NON-NLS-1$ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.KEY); TestOptimizer.helpPlan(sql, RealMetadataFactory.fromDDL(ddl, "x", "y"), new String[] {"SELECT g_1.e2, g_2.e2 FROM y.g3 AS g_0 LEFT OUTER JOIN (y.g1 AS g_1 LEFT OUTER JOIN y.g2 AS g_2 ON g_1.e1 = g_2.e2) ON g_2.e1 = g_0.e2 WHERE g_2.e2 IS NOT NULL"}, new DefaultCapabilitiesFinder(caps), 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); }
@Test public void testNestedTable() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true); capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$ ProcessorPlan plan = helpPlan("select pm2.g1.e1, x.e1 from pm2.g1, table(select * from pm2.g2 where pm2.g1.e1=pm2.g2.e1) x where pm2.g1.e2 IN (1, 2)", example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm2.g2 AS g_0 WHERE g_0.e1 = pm2.g1.e1", "SELECT g_0.e1 FROM pm2.g1 AS g_0 WHERE g_0.e2 IN (1, 2)" }, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ checkNodeTypes(plan, new int[] {1}, new Class[] {NestedTableJoinStrategy.class}); }
@Test public void testCompareCriteriaOpCapFail3() 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_ORDERED, false); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ helpTestVisitor("pm1.g1.e1 < 'x'", modelID, metadata, capFinder, false, false); }
@Test public void testSubqueryCompareCriteriaOpCapFail2() 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, false); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ helpTestVisitorWithCommand("SELECT e1 FROM pm1.g1 WHERE pm1.g1.e1 <> ANY (SELECT e1 FROM pm1.g2)", modelID, metadata, capFinder, false, false); //$NON-NLS-1$ }
@Test public void testSubqueryCompareCriteriaSearchableFail() throws Exception { TransformationMetadata metadata = RealMetadataFactory.example1(); Schema modelID = metadata.getMetadataStore().getSchema("PM1"); Column e1 = metadata.getElementID("pm1.g1.e1"); e1.setSearchType(SearchType.Like_Only); FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = new BasicSourceCapabilities(); caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ helpTestVisitorWithCommand("SELECT e1 FROM pm1.g1 WHERE pm1.g1.e1 = ANY (SELECT e1 FROM pm1.g2)", modelID, metadata, capFinder, false, false); //$NON-NLS-1$ }