public static ProcessorPlan helpPlan(String sql, QueryMetadataInterface md, List<String> bindings, CapabilitiesFinder capFinder, String[] expectedAtomic, ComparisonMode mode) throws TeiidComponentException, TeiidProcessingException { Command command = helpGetCommand(sql, md); return helpPlanCommand(command, md, capFinder, null, expectedAtomic, mode); }
public static ProcessorPlan helpPlan(String sql, QueryMetadataInterface md, List<String> bindings, CapabilitiesFinder capFinder, String[] expectedAtomic, boolean shouldSucceed) { Command command; try { command = helpGetCommand(sql, md); } catch (TeiidException err) { throw new TeiidRuntimeException(err); } return helpPlanCommand(command, md, capFinder, null, expectedAtomic, shouldSucceed ? ComparisonMode.CORRECTED_COMMAND_STRING : ComparisonMode.FAILED_PLANNING); }
private void helpTestReResolved(String sql) throws TeiidComponentException, TeiidProcessingException, TeiidException, Exception { TransformationMetadata metadata = RealMetadataFactory.exampleBQTCached(); HardcodedDataManager dataMgr = new HardcodedDataManager(metadata); dataMgr.addData("SELECT g_0.IntKey FROM SmallA AS g_0", Arrays.asList(1)); dataMgr.addData("SELECT g_0.IntKey FROM SmallB AS g_0 WHERE g_0.IntKey > 1", Arrays.asList(2)); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); CommandContext cc = TestProcessor.createCommandContext(); Command command = TestOptimizer.helpGetCommand(sql, metadata); ProcessorPlan pp = TestProcessor.helpGetPlan(command, metadata, new DefaultCapabilitiesFinder(bsc), cc); TestProcessor.helpProcess(pp, cc, dataMgr, new List[] {Arrays.asList(BigDecimal.valueOf(2))}); }
@Test public void testRecursiveView() throws Exception { String ddl = "CREATE view x (y string) as (select * from x)"; QueryMetadataInterface metadata = RealMetadataFactory.fromDDL(ddl, "x", "phy"); getPlan(helpGetCommand("select * from x", metadata), metadata, getGenericFinder(), null, false, null); }
@Test public void testMultiAccessPatternWithCriteria() throws Exception { String sql = "SELECT pm1.g1.* FROM pm4.g1, pm5.g1, pm1.g1 where pm4.g1.e1 = pm1.g1.e1 and pm5.g1.e1 = pm1.g1.e1 and pm5.g1.e2 like '%x' "; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); AnalysisRecord record = new AnalysisRecord(true, true); TestOptimizer.helpPlanCommand(TestOptimizer.helpGetCommand(sql, metadata), metadata, TestOptimizer.getGenericFinder(false), record, new String[] { "SELECT g_0.e2, g_0.e1 FROM pm5.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", //$NON-NLS-1$ "SELECT g_0.e1, g_0.e2, g_0.e3, g_0.e4 FROM pm1.g1 AS g_0", //$NON-NLS-1$ "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ assertTrue(record.getAnnotations().toString().contains("access pattern not satisfied by join")); }
@Test(expected=QueryPlannerException.class) public void testInvalidSource() throws Exception { String sql = "select * from pm1.g1"; //$NON-NLS-1$ QueryMetadataInterface md = RealMetadataFactory.example1Cached(); QueryOptimizer.optimizePlan(helpGetCommand(sql, md), md, null, new DefaultCapabilitiesFinder() { @Override public boolean isValid(String modelName) { return false; } }, null, new CommandContext()); }
@Test public void testMaterializedTransformationNoCache2() throws Exception { String userSql = "SELECT MATVIEW.E1 FROM MATVIEW OPTION NOCACHE"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.x FROM MatSrc.MatSrc AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertTrue("Expected one annotation", annotations.size() == 1); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testMaterializedTransformation() throws Exception { String userSql = "SELECT MATVIEW.E1 FROM MATVIEW"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.e1 FROM MatTable.MatTable AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertTrue("Expected one annotation", annotations.size() == 1); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testMaterializedTransformationNoCache() throws Exception { String userSql = "SELECT MATVIEW.E1 FROM MATVIEW OPTION NOCACHE MatView.MatView"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.x FROM MatSrc.MatSrc AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertTrue("Expected one annotation", annotations.size() == 1); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testNoCacheCascadeSubquery() throws Exception { String userSql = "SELECT (select MATVIEW1.E1 FROM MATVIEW1) from MatSrc option nocache"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT (SELECT g_0.x FROM MatSrc.MatSrc AS g_0 WHERE g_0.x = '1') FROM MatSrc.MatSrc AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertEquals("Expected one annotation",3, annotations.size()); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testNoCacheInTransformation() throws Exception { String userSql = "SELECT VGROUP.E1 FROM VGROUP"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.x FROM MatSrc.MatSrc AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Ignore("we no longer auto detect this case, if we need this logic it will have to be added to the rewriter since it changes select into to an insert") @Test public void testMaterializedTransformationLoading() throws Exception { String userSql = "SELECT MATVIEW.E1 INTO MatTable.MatStage FROM MATVIEW"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.x FROM MatSrc.MatSrc AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertTrue("Expected one annotation", annotations.size() == 1); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testTableNoCacheDoesntCascade() throws Exception { String userSql = "SELECT MATVIEW1.E1 FROM MATVIEW1 option nocache matview.matview1"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.e1 FROM MatTable.MatTable AS g_0 WHERE g_0.e1 = '1'"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testNoCacheCascade() throws Exception { String userSql = "SELECT MATVIEW1.E1 FROM MATVIEW1 option nocache"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.x FROM MatSrc.MatSrc AS g_0 WHERE g_0.x = '1'"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testManagedMaterializedTransformationJoin() throws Exception { //make sure view removal is not inhibited String userSql = "SELECT MatTable1.*,ManagedMatView.* FROM ManagedMatView left outer join MatTable1 on ManagedMatView.e1 = MatTable1.e1"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] { "SELECT g_1.e1, g_0.e1 FROM MatTable.MatTable AS g_0 LEFT OUTER JOIN MatTable.MatTable1 AS g_1 ON g_0.e1 = g_1.e1 WHERE mvstatus('MatView', 'ManagedMatView') = 1" }, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING); }
@Test public void testSubqueryPredicatePushdown() throws Exception { String sql = "SELECT array_agg((select intkey from bqt1.smallb where stringkey = lower(a.stringkey))) from bqt1.smalla a"; //$NON-NLS-1$ TransformationMetadata metadata = RealMetadataFactory.exampleBQTCached(); HardcodedDataManager dataMgr = new HardcodedDataManager(metadata); dataMgr.addData("SELECT g_0.StringKey FROM SmallA AS g_0", Arrays.asList("a")); dataMgr.addData("SELECT g_0.IntKey FROM SmallB AS g_0 WHERE g_0.StringKey = 'a'", Arrays.asList(1)); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); CommandContext cc = TestProcessor.createCommandContext(); ProcessorPlan pp = TestProcessor.helpGetPlan(TestOptimizer.helpGetCommand(sql, metadata), metadata, new DefaultCapabilitiesFinder(bsc), cc); TestProcessor.helpProcess(pp, cc, dataMgr, new List[] {Arrays.asList(new ArrayImpl(1))}); }
@Test public void testSourceWindowFunction() throws Exception { TransformationMetadata metadata = RealMetadataFactory.fromDDL("create foreign table team_target (amount integer, team integer, \"year\" integer); " + "create foreign function lead (arg string) returns string options (\"teiid_rel:aggregate\" true, \"teiid_rel:analytic\" true)", "x", "y"); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); String sql = "SELECT y.LEAD(ALL convert(amount, string)) OVER (PARTITION BY team ORDER BY \"year\") FROM team_target"; TestOptimizer.getPlan(helpGetCommand(sql, metadata), metadata, new DefaultCapabilitiesFinder(bsc), null, false, new CommandContext()); //$NON-NLS-1$ }
@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 testNoFullDepJoin() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.FULL_DEPENDENT_JOIN, true); String sql = "select pm1.g1.e1, pm1.g1.e2, pm2.g1.e2 FROM pm1.g1, pm2.g1 where (pm1.g1.e1 = pm2.g1.e1) option makedep pm1.g1(no join)"; assertEquals("SELECT pm1.g1.e1, pm1.g1.e2, pm2.g1.e2 FROM pm1.g1, pm2.g1 WHERE pm1.g1.e1 = pm2.g1.e1 OPTION MAKEDEP pm1.g1(NO JOIN)", QueryParser.getQueryParser().parseCommand(sql).toString()); //pass a debug analysisrecord to test debug annotations TestOptimizer.helpPlanCommand(TestOptimizer.helpGetCommand(sql, TestOptimizer.example1()), TestOptimizer.example1(), new DefaultCapabilitiesFinder(caps), new AnalysisRecord(true, true), new String[] { "SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm2.g1 AS g_0 ORDER BY c_0", "SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>) ORDER BY c_0" }, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testKeepAliases() throws Exception { String sql = "SELECT /*+ sh KEEP ALIASES bar:'leading(g)' */ e1 from pm1.g1 g order by e1 limit 1"; //$NON-NLS-1$ CommandContext cc = TestProcessor.createCommandContext(); cc.setDQPWorkContext(new DQPWorkContext()); cc.getDQPWorkContext().getSession().setVdb(RealMetadataFactory.example1VDB()); ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand(sql, RealMetadataFactory.example1Cached()), RealMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder(), null, true, cc); TestOptimizer.checkAtomicQueries(new String[] {"SELECT /*+sh KEEP ALIASES bar:'leading(g)' */ g.e1 AS c_0 FROM pm1.g1 AS g ORDER BY c_0"}, plan); List<?>[] expected = new List[] {}; helpProcess(plan, manager(null, "leading(g)"), expected); }