static public PreparedStatementRequest helpGetProcessorPlan(String preparedSql, List<?> values, SessionAwareCache<PreparedPlan> prepPlanCache, int conn) throws TeiidComponentException, TeiidProcessingException { return helpGetProcessorPlan(preparedSql, values, new DefaultCapabilitiesFinder(), RealMetadataFactory .example1Cached(), prepPlanCache, conn, false, false, RealMetadataFactory.example1VDB()); }
@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 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 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 testSupportsSelfJoin4() throws Exception { // Set up metadata TransformationMetadata metadata = RealMetadataFactory.example1Cached(); Schema modelID = metadata.getMetadataStore().getSchema("PM1"); // Test capabilities util boolean actual = CapabilitiesUtil.supportsSelfJoins(modelID, metadata, new DefaultCapabilitiesFinder()); assertEquals("Got wrong answer for supports", false, actual); //$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 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 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); }
private void execute(String sql, List<?>... expectedResults) throws Exception { CommandContext cc = TestProcessor.createCommandContext(); cc.setTempTableStore(tempStore); cc.setGlobalTableStore(globalStore); cc.setMetadata(metadata); CapabilitiesFinder finder = new DefaultCapabilitiesFinder(); previousPlan = TestProcessor.helpGetPlan(TestProcessor.helpParse(sql), metadata, finder, cc); cc.setQueryProcessorFactory(new QueryProcessorFactoryImpl(BufferManagerFactory.getStandaloneBufferManager(), dataManager, finder, null, metadata)); TestProcessor.doProcess(previousPlan, dataManager, expectedResults, cc); }
@Test public void testDDLProcTransactionNonTransactionalJoin() throws Exception { String ddl = "create foreign procedure proc () returns table(col string);" + "create virtual procedure virt() as begin select * from proc, proc as x; end"; //$NON-NLS-1$ TransformationMetadata metadata = RealMetadataFactory.fromDDL(ddl, "x", "y"); String userQuery = "EXEC virt()"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(userQuery, metadata); assertTrue(plan.requiresTransaction(false)); BasicSourceCapabilities bsc = new BasicSourceCapabilities(); bsc.setSourceProperty(Capability.TRANSACTION_SUPPORT, TransactionSupport.NONE); plan = getProcedurePlan(userQuery, metadata, new DefaultCapabilitiesFinder(bsc)); assertFalse(plan.requiresTransaction(false)); }