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); }
@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 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$ }
@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$ }
@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 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 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 testManagedMaterializedTransformation() throws Exception { String userSql = "SELECT * FROM ManagedMatView"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); RelationalPlan plan = (RelationalPlan) TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] { "SELECT g_0.e1 FROM MatTable.MatTable AS g_0 WHERE mvstatus('MatView', 'ManagedMatView') = 1" }, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING); Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testManagedMaterializedTransformationInsert() throws Exception { String userSql = "insert into MatTable1 SELECT * FROM ManagedMatView option nocache ManagedMatView"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); RelationalPlan plan = (RelationalPlan) TestOptimizer.helpPlanCommand(command, metadata, new DefaultCapabilitiesFinder(bsc), analysis, new String[] { "SELECT g_0.e1 FROM MatTable.MatTable AS g_0 WHERE mvstatus('MatView', 'ManagedMatView') = 1" }, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING); bsc.setCapabilitySupport(Capability.INSERT_WITH_QUERYEXPRESSION, true); plan = (RelationalPlan) TestOptimizer.helpPlanCommand(command, metadata, new DefaultCapabilitiesFinder(bsc), analysis, new String[] { "INSERT INTO MatTable1 (e1) SELECT g_0.e1 FROM MatTable.MatTable AS g_0 WHERE mvstatus('MatView', 'ManagedMatView') = 1" }, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING); Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testManagedMaterializedTransformationUnion() throws Exception { //make sure view removal is not inhibited String userSql = "SELECT * FROM ManagedMatView union all SELECT * FROM ManagedMatView"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); BasicSourceCapabilities capabilities = getTypicalCapabilities(); capabilities.setCapabilitySupport(Capability.QUERY_UNION, true); TestOptimizer.helpPlanCommand(command, metadata, new DefaultCapabilitiesFinder(capabilities), analysis, new String[] { "SELECT g_1.e1 AS c_0 FROM MatTable.MatTable AS g_1 WHERE mvstatus('MatView', 'ManagedMatView') = 1 UNION ALL SELECT g_0.e1 AS c_0 FROM MatTable.MatTable AS g_0" }, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING); }
@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 testManagedMaterializedTransformationSubquery() throws Exception { String userSql = "SELECT e1, (select min(e1) from ManagedMatView x where x.e1 > MatTable1.e1) FROM MatTable1"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); BasicSourceCapabilities capabilities = getTypicalCapabilities(); capabilities.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); capabilities.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true); capabilities.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR_PROJECTION, true); capabilities.setCapabilitySupport(Capability.QUERY_AGGREGATES_MIN, true); TestOptimizer.helpPlanCommand(command, metadata, new DefaultCapabilitiesFinder(capabilities), analysis, new String[] { "SELECT g_0.e1, (SELECT MIN(g_1.e1) FROM MatTable.MatTable AS g_1 WHERE (mvstatus('MatView', 'ManagedMatView') = 1) AND (g_1.e1 > g_0.e1)) FROM MatTable.MatTable1 AS g_0" }, //$NON-NLS-1$ ComparisonMode.EXACT_COMMAND_STRING); }