@Test public void testSubqueryRewriteToJoinWithAggregate() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (select max(e2) FROM pm1.g1 where pm3.g1.e1 = e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS expr1, e1 FROM pm1.g1 GROUP BY e1) AS X__1 WHERE (pm3.g1.e2 < X__1.expr1) AND (pm3.g1.e1 = X__1.e1)", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryRewriteToJoinWithGroupingExpression() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select distinct e1 from pm3.g1 where exists (select 1 FROM pm1.g1 group by e4 || 'x' HAVING min(e3) || (e4 || 'x') = pm3.g1.e3)", "SELECT DISTINCT e1 FROM pm3.g1, (SELECT MIN(e3) AS expr1, concat(convert(e4, string), 'x') AS expr2, concat(convert(MIN(e3), string), concat(convert(e4, string), 'x')) AS expr FROM pm1.g1 GROUP BY concat(convert(e4, string), 'x')) AS X__1 WHERE convert(pm3.g1.e3, string) = X__1.expr", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryExpressionJoin() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (Select max(e2) from pm2.g2 where e1 = pm3.g1.e1 having convert(min(e2), string) > pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS expr1, e1, MIN(e2) AS expr3 FROM pm2.g2 GROUP BY e1) AS X__1 WHERE (convert(X__1.expr3, string) > pm3.g1.e1) AND (pm3.g1.e2 < X__1.expr1) AND (pm3.g1.e1 = X__1.e1)", RealMetadataFactory.example4(), cc); }
/** * Agg does not depend on cardinality */ @Test public void testSubqueryRewriteToJoinGroupBy() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select max(e1) from pm1.g1 as x where exists (select pm1.g1.e1 FROM pm1.g1 where e1 = x.e1) group by e2", "SELECT MAX(e1) FROM pm1.g1 AS x, (SELECT e1 FROM pm1.g1) AS X__1 WHERE x.e1 = X__1.e1 GROUP BY e2", RealMetadataFactory.example1Cached(), cc); }
@Test public void testSubqueryDoNotRewriteToJoin() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where not exists (select pm1.g1.e1 FROM pm1.g1 where e1 = pm3.g1.e1)", "SELECT e1 FROM pm3.g1 WHERE NOT EXISTS (SELECT pm1.g1.e1 FROM pm1.g1 WHERE e1 = pm3.g1.e1 LIMIT 1)", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryRewriteToJoinDistinct() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select distinct e1 from pm1.g1 as x where exists (select pm1.g1.e1 FROM pm1.g1 where e1 = x.e1)", "SELECT DISTINCT e1 FROM pm1.g1 AS x, (SELECT e1 FROM pm1.g1) AS X__1 WHERE x.e1 = X__1.e1", RealMetadataFactory.example1Cached(), cc); }
@Test public void testSortCollationInhibitsPush() throws TeiidException { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setSourceProperty(Capability.COLLATION_LOCALE, "foo"); // Create query String sql = "select e1, e2 from pm1.g1 order by e2"; //$NON-NLS-1$ CommandContext cc = new CommandContext(); cc.setOptions(new Options().requireTeiidCollation(true)); ProcessorPlan plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example1Cached(), new DefaultCapabilitiesFinder(caps), cc); //$NON-NLS-1$ List[] expected = new List[] { Arrays.asList("a", 0), Arrays.asList("a", 1) }; HardcodedDataManager manager = new HardcodedDataManager(); manager.addData("SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_1", new List[] {Arrays.asList("a", 0), Arrays.asList("a", 1)}); helpProcess(plan, manager, expected); sql = "select e1, e2 from pm1.g1 order by e1"; //$NON-NLS-1$ plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example1Cached(), new DefaultCapabilitiesFinder(caps), cc); //$NON-NLS-1$ expected = new List[] { Arrays.asList("a", 0), Arrays.asList("b", 1) }; manager = new HardcodedDataManager(); manager.addData("SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0", new List[] {Arrays.asList("b", 1), Arrays.asList("a", 0)}); helpProcess(plan, manager, expected); }
@Test public void testSubqueryDoNotRewriteToJoin2() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where e2 < some (select pm1.g1.e2 FROM pm1.g1)", "SELECT e1 FROM pm3.g1 WHERE e2 < (SELECT MAX(X.e2) FROM (SELECT pm1.g1.e2 FROM pm1.g1) AS X)", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryRewriteToJoin() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where exists (select pm1.g1.e1 FROM pm1.g1 where e1 = pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT e1 FROM pm1.g1) AS X__1 WHERE pm3.g1.e1 = X__1.e1", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryRewriteToJoinWithAggregate2() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (select max(e2) FROM pm1.g1 WHERE pm3.g1.e1 = e1 HAVING min(e3) < pm3.g1.e3)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS expr1, e1, MIN(e3) AS expr3 FROM pm1.g1 GROUP BY e1) AS X__1 WHERE (X__1.expr3 < pm3.g1.e3) AND (pm3.g1.e2 < X__1.expr1) AND (pm3.g1.e1 = X__1.e1)", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryRewriteToJoinDistinct1() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm1.g1 as x where exists (select pm1.g1.e1 FROM pm1.g1 where e1 = x.e1 and e2 < x.e2)", "SELECT e1 FROM pm1.g1 AS x WHERE EXISTS (SELECT pm1.g1.e1 FROM pm1.g1 WHERE (e1 = x.e1) AND (e2 < x.e2) LIMIT 1)", RealMetadataFactory.example1Cached(), cc); }
@Test public void testDontRewriteToJoinWithOtherCriteria() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in /*+ NO_UNNEST */ (select pm1.g1.e1 FROM pm1.g1 where e2 < pm3.g1.e2)", "SELECT e1 FROM pm3.g1 WHERE pm3.g1.e1 IN /*+ NO_UNNEST */ (SELECT pm1.g1.e1 FROM pm1.g1 WHERE e2 < pm3.g1.e2)", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryRewriteToJoinExistsNoKey() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm1.g1 x where exists (select 1 FROM pm1.g2 where pm1.g2.e1 = x.e1)", "SELECT e1 FROM pm1.g1 AS x, (SELECT DISTINCT pm1.g2.e1 FROM pm1.g2) AS X__1 WHERE x.e1 = X__1.e1", RealMetadataFactory.example4(), cc); }
@Test public void testSubqueryDoNotRewriteToJoin3() throws Exception { CommandContext cc = new CommandContext(); cc.setOptions(new Options().subqueryUnnestDefault(true)); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where e2 < some (select pm1.g1.e2 FROM pm1.g1 where pm3.g1.e3 <> e3)", "SELECT e1 FROM pm3.g1 WHERE e2 < SOME (SELECT MAX(pm1.g1.e2) FROM pm1.g1 WHERE e3 <> pm3.g1.e3)", RealMetadataFactory.example4(), cc); //should rewrite as we have an equi join predicate TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where e2 < some (select pm1.g1.e2 FROM pm1.g1 where pm3.g1.e3 = e3)", "SELECT e1 FROM pm3.g1, (SELECT MAX(pm1.g1.e2) AS expr1, e3 FROM pm1.g1 GROUP BY e3) AS X__1 WHERE (e2 < X__1.expr1) AND (pm3.g1.e3 = X__1.e3)", RealMetadataFactory.example4(), cc); }
@Test() public void testAggregateOrderByPushdown() throws Exception { String sql = "SELECT string_agg(e1, ' ' order by e1) FROM pm1.g1"; //$NON-NLS-1$ TransformationMetadata metadata = RealMetadataFactory.example1Cached(); HardcodedDataManager hdm = new HardcodedDataManager(metadata); hdm.addData("SELECT STRING_AGG(g_0.e1, ' ' ORDER BY g_0.e1) FROM g1 AS g_0", Arrays.asList('a')); BasicSourceCapabilities bsc = TestAggregatePushdown.getAggregateCapabilities(); bsc.setCapabilitySupport(Capability.QUERY_AGGREGATES_STRING, true); ProcessorPlan plan = TestProcessor.helpGetPlan(sql, metadata, new DefaultCapabilitiesFinder(bsc)); TestProcessor.helpProcess(plan, TestProcessor.createCommandContext(), hdm, new List[] {Arrays.asList('a')}); bsc.setSourceProperty(Capability.COLLATION_LOCALE, "foo"); CommandContext cc = new CommandContext(); cc.setOptions(new Options().requireTeiidCollation(true)); CommandContext.pushThreadLocalContext(cc); try { plan = TestProcessor.helpGetPlan(helpParse(sql), metadata, new DefaultCapabilitiesFinder(bsc), cc); TestOptimizer.checkAtomicQueries(new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"}, plan); } finally { CommandContext.popThreadLocalContext(); } }
/** * Join processing is insensitive to the null ordering */ @Test public void testNullOrderingJoin() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.UNKNOWN); DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps); //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); CommandContext cc = new CommandContext(); cc.setOptions(new Options().pushdownDefaultNullOrder(true)); ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand("select pm1.g1.e1, pm2.g1.e3 from /*+ makedep */ pm1.g1, pm2.g1 where pm1.g1.e2 = pm2.g1.e2", metadata), metadata, capFinder, null, true, cc); HardcodedDataManager dataManager = new HardcodedDataManager(metadata, cc, caps); dataManager.addData("SELECT g_0.e2 AS c_0, g_0.e3 AS c_1 FROM g1 AS g_0 ORDER BY c_0", new List<?>[] {}); TestProcessor.helpProcess(plan, dataManager, new List<?>[] {}); }
/** * The engine will remove the null ordering if it's not needed * @throws Exception */ @Test public void testNullOrdering3() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.HIGH); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); CommandContext cc = new CommandContext(); cc.setOptions(new Options().pushdownDefaultNullOrder(true)); ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand("select e1 from pm1.g1 order by e1 desc, e2 asc NULLS LAST", metadata), metadata, capFinder, null, true, cc); TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); HardcodedDataManager dataManager = new HardcodedDataManager(metadata, cc, caps); dataManager.addData("SELECT g_0.e1 AS c_0 FROM g1 AS g_0 ORDER BY c_0 DESC, g_0.e2", new List<?>[] {}); TestProcessor.helpProcess(plan, dataManager, new List<?>[] {}); }
@Test public void testNullOrdering2() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.FIRST); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); CommandContext cc = new CommandContext(); cc.setOptions(new Options().pushdownDefaultNullOrder(true)); ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand("select e1 from pm1.g1 order by e1 desc, e2 asc NULLS LAST", metadata), metadata, capFinder, null, true, cc); TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); HardcodedDataManager dataManager = new HardcodedDataManager(metadata, cc, caps); dataManager.addData("SELECT g_0.e1 AS c_0 FROM g1 AS g_0 ORDER BY c_0 DESC NULLS LAST, g_0.e2 NULLS LAST", new List<?>[] {}); TestProcessor.helpProcess(plan, dataManager, new List<?>[] {}); }
context.setOptions(options); ProcessorPlan plan = QueryOptimizer.optimizePlan(command, wrapper, idGenerator, finder, analysis, context);
private void helpTestNullOrdering(String sql, String result) throws TeiidComponentException, TeiidProcessingException { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_STRING, true); caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true); caps.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true); caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true); caps.setCapabilitySupport(Capability.ROW_LIMIT, true); caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.UNKNOWN); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); CommandContext cc = new CommandContext(); cc.setOptions(new Options().pushdownDefaultNullOrder(true)); ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand(sql, metadata), metadata, capFinder, null, true, cc); TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); HardcodedDataManager dataManager = new HardcodedDataManager(metadata, cc, caps); dataManager.addData(result, new List<?>[] {}); TestProcessor.helpProcess(plan, dataManager, new List<?>[] {}); }