@Test public void testXmlParseBlobWithEncoding() throws Exception { String sql = "select xmlparse(document cast(? as blob)) x"; //$NON-NLS-1$ List[] expected = new List[] { Arrays.asList(ObjectConverterUtil.convertToString(new InputStreamReader(new FileInputStream(UnitTestUtil.getTestDataFile("encoding.xml")), Charset.forName("ISO-8859-1")))), }; processPreparedStatement(sql, expected, dataManager, new DefaultCapabilitiesFinder(), RealMetadataFactory.example1Cached(), Arrays.asList(blobFromFile("encoding.xml"))); }
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 testXmlParseBlob() throws Exception { String sql = "select xmlparse(document cast(? as blob)) x"; //$NON-NLS-1$ List[] expected = new List[] { Arrays.asList(ObjectConverterUtil.convertToString(new FileInputStream(UnitTestUtil.getTestDataFile("udf.xmi")))), }; processPreparedStatement(sql, expected, dataManager, new DefaultCapabilitiesFinder(), RealMetadataFactory.example1Cached(), Arrays.asList(blobFromFile("udf.xmi"))); }
@Test public void testSimpleCrossJoin1() throws Exception { helpPlan("select pm1.g1.e1 FROM pm1.g1, pm1.g2", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", //$NON-NLS-1$ "SELECT pm1.g2.e1 FROM pm1.g2" }, new DefaultCapabilitiesFinder(), ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ }
@Test public void testProcedureMask() throws Exception { HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("EXEC pm1.sp1()", new List<?>[] {Arrays.asList("a", 1), Arrays.asList("b", 2)}); ProcessorPlan plan = helpGetPlan(helpParse("exec pm1.sp1()"), RealMetadataFactory.example1Cached(), new DefaultCapabilitiesFinder(), context); List<?>[] expectedResults = new List<?>[] {Arrays.asList("a", 1), Arrays.asList(null, 2)}; helpProcess(plan, context, dataManager, expectedResults); }
@Test public void testTableAliasMask() throws Exception { HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1", new List<?>[] {Arrays.asList("a", 1), Arrays.asList("b", 2)}); ProcessorPlan plan = helpGetPlan(helpParse("select g2.e2 from pm1.g1 as g2"), RealMetadataFactory.example1Cached(), new DefaultCapabilitiesFinder(), context); List<?>[] expectedResults = new List<?>[] {Collections.singletonList(null), Arrays.asList(2)}; helpProcess(plan, context, dataManager, expectedResults); }
@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 testProcedureRelationalFilter() throws Exception { HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("EXEC pm1.sp1()", new List<?>[] {Arrays.asList("a", 1), Arrays.asList("b", 2)}); ProcessorPlan plan = helpGetPlan(helpParse("select * from pm1.sp1"), RealMetadataFactory.example1Cached(), new DefaultCapabilitiesFinder(), context); List<?>[] expectedResults = new List<?>[] {Arrays.asList("a", 1)}; helpProcess(plan, context, dataManager, expectedResults); }
@Test public void testInsertWithSimpleSelect() throws Exception { String preparedSql = "insert into pm1.g1 (e1, e2) select ?, ?"; //$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 testIntersect() throws Exception { String sql = "select e1, e2 from pm1.g2 intersect select e1, 1 from pm1.g2"; //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(), new String[] {"SELECT pm1.g2.e1 FROM pm1.g2", "SELECT pm1.g2.e1, pm1.g2.e2 FROM pm1.g2"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ List<?>[] expected = new List[] { Arrays.asList(new Object[] {null, 1}), Arrays.asList(new Object[] {"c", 1}), //$NON-NLS-1$ }; FakeDataManager manager = new FakeDataManager(); TestProcessor.sampleData1(manager); TestProcessor.helpProcess(plan, manager, expected); }
@Test public void testSelectFilter() throws Exception { HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1", new List<?>[] {Arrays.asList("a", 1), Arrays.asList("b", 2)}); ProcessorPlan plan = helpGetPlan(helpParse("select e2 from pm1.g1"), RealMetadataFactory.example1Cached(), new DefaultCapabilitiesFinder(), context); List<?>[] expectedResults = new List<?>[0]; helpProcess(plan, context, dataManager, expectedResults); }
@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$ }
/** * Not allowed to optimize via rewrite as the key is on e1, but the predicate is on e2 */ @Test public void testSelectSubqueryKeyJoin1() throws Exception { String sql = "SELECT g2.e1, g2.e2, /*+ MJ */ (select e1 from g1 where e2 = g2.e2) FROM g2"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.fromDDL("create foreign table g1 (e1 integer primary key, e2 integer);" + " create foreign table g2 (e1 integer, e2 integer, foreign key (e1) references g1);", "x", "y"); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); TestOptimizer.helpPlan(sql, metadata, new String[] {"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM y.g2 AS g_0 ORDER BY c_1"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); }
@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$ }
/** * We're allowed to use a join instead as there will only be 1 row from the subquery for every outer row */ @Test public void testSelectSubqueryKeyJoin() throws Exception { String sql = "SELECT g2.e1, g2.e2, /*+ MJ */ (select e2 from g1 where e1 = g2.e1) FROM g2"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.fromDDL("create foreign table g1 (e1 integer primary key, e2 integer);" + " create foreign table g2 (e1 integer, e2 integer, foreign key (e1) references g1);", "x", "y"); BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); ProcessorPlan pp = TestOptimizer.helpPlan(sql, metadata, new String[] {"SELECT g_0.e1, g_0.e2, g_1.e2 FROM y.g2 AS g_0 LEFT OUTER JOIN y.g1 AS g_1 ON g_0.e1 = g_1.e1"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); TestOptimizer.checkNodeTypes(pp, TestOptimizer.FULL_PUSHDOWN); }
@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$ }