@Test public void testAnonBlockIn() throws Exception { String preparedSql = "begin insert into pm1.g1 (e1, e2) select ?, ?; select rowcount; end;"; //$NON-NLS-1$ List<?>[] expected = new List<?>[] { Arrays.asList(1), }; List<?> values = Arrays.asList("a", "1"); //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); HardcodedDataManager dataManager = new HardcodedDataManager(metadata); dataManager.addData("INSERT INTO g1 (e1, e2) VALUES ('a', 1)", new List<?>[] {Arrays.asList(1)}); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); helpTestProcessing(preparedSql, values, expected, dataManager, new DefaultCapabilitiesFinder(caps), metadata, null, false, false, false, RealMetadataFactory.example1VDB()); }
@Test public void testSingleOrPredicate() throws Exception { String sql = "SELECT alias3.a1 FROM (select e2 as a from pm1.g1) as alias2 INNER JOIN (SELECT t2.a AS a1, t1.a " + "FROM (SELECT 1 AS a) AS t1 INNER JOIN (select e2 as a from pm1.g1) as t2 ON t1.a = t2.a) " + "AS alias3 ON ((alias3.a = alias2.a) OR (alias3.a > alias2.a))"; BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); TestOptimizer.helpPlan(sql, //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT 1 FROM pm1.g1 AS g_0 WHERE (g_0.e2 = 1) OR (g_0.e2 < 1)", "SELECT g_0.e2 FROM pm1.g1 AS g_0 WHERE g_0.e2 = 1"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
public static CapabilitiesFinder getGenericFinder(boolean supportsJoins) { final BasicSourceCapabilities caps = getTypicalCapabilities(); if (!supportsJoins) { caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, false); caps.setCapabilitySupport(Capability.QUERY_ORDERBY, false); } return new DefaultCapabilitiesFinder(caps); }
@Test public void testNestedLimit() throws Exception { String sql = "SELECT count(*) FROM (select intkey, stringkey as x from BQT1.SmallA ORDER BY x limit 10) x where intkey = 1"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, new DefaultCapabilitiesFinder(TestOptimizer.getTypicalCapabilities())); List[] expected = new List[] { Arrays.asList(1), }; HardcodedDataManager manager = new HardcodedDataManager(metadata); manager.addData("SELECT g_0.IntKey AS c_0 FROM SmallA AS g_0 ORDER BY g_0.StringKey", new List[] {Arrays.asList(1)}); helpProcess(plan, manager, expected); }
@Test public void testSubqueryOrderByRelated() throws Exception { String sql = "SELECT pm1.g1.*, (select count(*) from pm1.g2 where e1 = pm1.g1.e1) FROM pm1.g1 ORDER BY (select count(*) from pm1.g2 where e1 = pm1.g1.e1)"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, new DefaultCapabilitiesFinder(bsc), createCommandContext()); List[] expected = new List[] { Arrays.asList("a", 1, true, 1.0, 1) }; HardcodedDataManager manager = new HardcodedDataManager(metadata); manager.addData("SELECT g_0.e1, g_0.e2, g_0.e3, g_0.e4 FROM g1 AS g_0", new List[] {Arrays.asList("a", 1, true, 1.0)}); manager.addData("SELECT 1 FROM g2 AS g_0 WHERE g_0.e1 = 'a'", new List[] {Arrays.asList(1)}); helpProcess(plan, manager, expected); }
/** * Detect if a subquery should prevent pushdown */ @Test public void testDeleteSubquery() throws Exception { BasicSourceCapabilities bsc = getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); TestOptimizer.helpPlan("delete FROM bqt1.smalla where intkey in (select cast(stringkey as integer) from bqt1.smallb)", //$NON-NLS-1$ RealMetadataFactory.exampleBQTCached(), null, new DefaultCapabilitiesFinder(bsc), null, false); //$NON-NLS-1$ }
@Test public void testDistinctConstant3() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.ROW_LIMIT, true); TestOptimizer.helpPlan("SELECT DISTINCT c1, null as c2, null as c3 FROM(SELECT c1, c2 FROM (" + "SELECT 'const_col_1' as c1, e1 as c2 FROM pm1.g1 UNION ALL " + "SELECT 'const_col_2' as c1, e1 as c2 FROM pm2.g2 ) as v ) as v1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT 'const_col_2' AS c_0 FROM pm2.g2 AS g_0 LIMIT 1", "SELECT 'const_col_1' AS c_0 FROM pm1.g1 AS g_0 LIMIT 1"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testLeftOuterAssocitivtyRightLinearSwap() 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 left outer join pm1.g3 on pm2.g2.e2 = pm1.g3.e2) on pm1.g1.e1 = pm1.g3.e1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e2 AS c_0 FROM pm2.g2 AS g_0 ORDER BY c_0", "SELECT g_1.e2 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.e1 = g_1.e1 ORDER BY c_0"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testNestedWith1() throws Exception { CommandContext cc = TestProcessor.createCommandContext(); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true); String sql = "WITH cte1 as (SELECT 1 as a), cte3 as /*+ no_inline */ (with cte3_1 as /*+ no_inline */ (select cte1.a from cte1 join pm1.g1 t1 on cte1.a=t1.e2) select * from cte3_1) SELECT * FROM cte3"; ProcessorPlan plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example1Cached(), new DefaultCapabilitiesFinder(bsc), cc); HardcodedDataManager hdm = new HardcodedDataManager(RealMetadataFactory.example1Cached()); hdm.addData("WITH cte3_1 (a) AS (SELECT 1 FROM g1 AS g_0 WHERE g_0.e2 = 1), cte3 (a) AS (SELECT g_0.a FROM cte3_1 AS g_0) SELECT g_0.a FROM cte3 AS g_0", Arrays.asList(1)); TestProcessor.helpProcess(plan, hdm, new List<?>[] {Arrays.asList(1)}); }
@Test public void testFullOuterJoinPredicatePlacement() throws TeiidComponentException, TeiidProcessingException { String sql = "select b1.intkey, b2.intkey from (select * from bqt1.smalla where bqt1.smalla.stringkey = 'a') b1 full outer join (select * from bqt1.smallb where bqt1.smallb.stringkey = 'b') b2 on (b1.intkey = b2.intkey)"; //$NON-NLS-1$ BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER_FULL, true); bsc.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, false); TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.IntKey FROM BQT1.SmallA AS g_0 WHERE g_0.StringKey = 'a'", "SELECT g_0.IntKey FROM BQT1.SmallB AS g_0 WHERE g_0.StringKey = 'b'"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testCantPushGroupBy() throws Exception { String sql = "select e3, e2 from test.group group by e3, e2"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_GROUP_BY, true); capFinder.addCapabilities("test", caps); //$NON-NLS-1$ TestOptimizer.helpPlan(sql, TestValidator.exampleMetadata(), new String[] {"SELECT g_0.e3, g_0.e2 FROM test.\"group\" AS g_0"}, capFinder, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testViewPlanning() throws Exception { CommandContext cc = TestProcessor.createCommandContext(); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, true); TransformationMetadata metadata = RealMetadataFactory.fromDDL("create view v1 as WITH mycte as (SELECT 1 as col1) SELECT col1 FROM mycte;", "x", "y"); String sql = "WITH mycte as (SELECT * FROM y.v1) SELECT * from mycte;"; ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, new DefaultCapabilitiesFinder(bsc), cc); HardcodedDataManager hdm = new HardcodedDataManager(metadata); TestProcessor.helpProcess(plan, hdm, new List<?>[] {Arrays.asList(1)}); }
@Test public void testCrossJoinWithRestriction() throws Exception { String sql = "select pm1.g1.e2, pm1.g2.e2 from pm1.g1, pm1.g2"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.THETA); 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 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 testSetClauseUpdateCompensation() throws Exception { String sql = "update pm1.g1 set e4 = (select e4 from pm1.g2 where pm1.g2.e2 = pm1.g1.e2 limit 1) where e1 = 'a'"; //$NON-NLS-1$ FakeDataManager dataManager = new FakeDataManager(); sampleData1(dataManager); BasicSourceCapabilities caps = getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, false); ProcessorPlan plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example4(), new DefaultCapabilitiesFinder(caps), createCommandContext()); List[] expected = new List[] { Arrays.asList(3), }; helpProcess(plan, dataManager, expected); }
@Test(expected=TeiidProcessingException.class) public void testNtileException() throws Exception { BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); String sql = "select intkey, ntile(intkey) over (order by intkey) l from bqt1.smalla order by l"; QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); ProcessorPlan plan = TestOptimizer.getPlan(helpGetCommand(sql, metadata), metadata, new DefaultCapabilitiesFinder(bsc), null, true, new CommandContext()); //$NON-NLS-1$ HardcodedDataManager dataMgr = new HardcodedDataManager(); dataMgr.addData("SELECT g_0.IntKey FROM BQT1.SmallA AS g_0", Arrays.asList(-1)); helpProcess(plan, createCommandContext(), dataMgr, null); }
@Test public void testCase3778() throws Exception { QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); BasicSourceCapabilities caps = getTypicalCapabilities(); FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true); caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ ProcessorPlan plan = helpPlan( "select a.e1, b.e1 from vm2.g1 a, vm2.g1 b where a.e1 = b.e1 and a.e2 in /*+ no_unnest */ (select e2 from vm1.g1)", //$NON-NLS-1$ metadata, null, capFinder, new String[] {"SELECT g_0.e1, g_2.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1, pm1.g1 AS g_2, pm1.g2 AS g_3 WHERE (g_2.e2 = g_3.e2) AND (g_0.e2 = g_1.e2) AND (g_0.e1 = g_2.e1) AND (g_0.e2 IN /*+ NO_UNNEST */ (SELECT g_4.e2 FROM pm1.g1 AS g_4))"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testCopyCriteriaWithFunction3() throws TeiidComponentException, TeiidProcessingException { String sql = "select bqt1.smalla.intkey, bqt1.smallb.intkey from bqt1.smalla, bqt1.smallb where bqt1.smalla.stringkey = bqt1.smallb.intkey and bqt1.smallb.intkey = 1"; //$NON-NLS-1$ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); // Plan query ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.IntKey, g_1.IntKey FROM BQT1.SmallA AS g_0, BQT1.SmallB AS g_1 WHERE (g_0.StringKey = '1') AND (g_1.IntKey = 1)"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.THETA); TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.IntKey FROM BQT1.SmallA AS g_0 WHERE g_0.StringKey = '1'", "SELECT g_0.IntKey FROM BQT1.SmallB AS g_0 WHERE g_0.IntKey = 1"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testMustPushdownOverMultipleSourcesWithoutSupport() throws Exception { QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource())); FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); 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$ helpPlan(sql, metadata, null, capFinder, new String[] {}, ComparisonMode.FAILED_PLANNING); //$NON-NLS-1$ }