@Test
  public void testDDLMetadata1() throws Exception {
    String ddl =
        "CREATE foreign FUNCTION sourceFunc(msg varchar) RETURNS varchar options (nameinsource 'a.sourcefunc') "
            + "CREATE foreign FUNCTION b.sourceFunc(msg varchar) RETURNS varchar "
            + "CREATE foreign table X (Y varchar);";

    QueryMetadataInterface metadata = RealMetadataFactory.fromDDL(ddl, "x", "phy");

    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    capFinder.addCapabilities("phy", caps); // $NON-NLS-1$

    ProcessorPlan plan =
        helpPlan(
            "SELECT sourceFunc(g_0.Y), phy.b.sourceFunc(g_0.Y) FROM phy.X AS g_0",
            metadata,
            null,
            capFinder,
            new String[] {"SELECT sourceFunc(g_0.Y), phy.b.sourceFunc(g_0.Y) FROM phy.X AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    // ensure that the source query contains the function schemas
    HardcodedDataManager dm = new HardcodedDataManager(metadata);
    dm.addData("SELECT a.sourcefunc(g_0.Y), b.sourceFunc(g_0.Y) FROM X AS g_0", new List[0]);
    TestProcessor.helpProcess(plan, dm, new List[0]);
  }
Example #2
0
  @Test
  public void testPartialProjection() throws TeiidComponentException, TeiidProcessingException {
    String sql =
        "SELECT user() AS a, "
            + " AVG(e2) OVER ( ) AS b,"
            + " MAX(e2) OVER ( ) AS b"
            + " FROM pm1.g1";

    HardcodedDataManager dataMgr = new HardcodedDataManager();
    dataMgr.addData(
        "SELECT ROUND(convert((g_0.L_DISCOUNT - AVG(g_0.L_DISCOUNT) OVER ()), FLOAT), 0) FROM TPCR_Oracle_9i.LINEITEM AS g_0",
        Arrays.asList(2.0f),
        Arrays.asList(2.0f));

    BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
    bsc.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    bsc.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true);
    bsc.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true);

    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            sql,
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(bsc),
            new String[] {"SELECT AVG(g_0.e2) OVER (), g_0.e2 FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(
        plan,
        new int[] {1, 1, 1},
        new Class<?>[] {AccessNode.class, WindowFunctionProjectNode.class, ProjectNode.class});

    List<?>[] expected =
        new List<?>[] {
          Arrays.asList(null, BigDecimal.valueOf(1.5), 2),
          Arrays.asList(null, BigDecimal.valueOf(1.5), 2)
        };

    dataMgr.addData(
        "SELECT AVG(g_0.e2) OVER (), g_0.e2 FROM pm1.g1 AS g_0", //$NON-NLS-1$
        Arrays.asList(1.5, 2),
        Arrays.asList(1.5, 1));

    helpProcess(plan, dataMgr, expected);

    // should completely eliminate the window function node
    plan =
        TestOptimizer.helpPlan(
            "SELECT uuid() AS a, AVG(e2) OVER ( ) AS b FROM pm1.g1",
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(bsc),
            new String[] {"SELECT AVG(g_0.e2) OVER () FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(plan, new int[] {1, 1}, new Class<?>[] {AccessNode.class, ProjectNode.class});
  }
  @Test
  public void testConcat2() throws Exception {
    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();

    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, true);
    capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$

    String sql = "select concat2(x.e1, x.e1) from pm1.g1 as x"; // $NON-NLS-1$

    helpPlan(
        sql,
        metadata,
        null,
        capFinder,
        new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"},
        ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    // cannot pushdown
    caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, false);

    ProcessorPlan plan =
        helpPlan(
            sql,
            metadata,
            null,
            capFinder,
            new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(
        "SELECT g_0.e1 FROM pm1.g1 AS g_0",
        new List[] {Arrays.asList("a"), Arrays.asList((String) null)});
    TestProcessor.helpProcess(
        plan, dataManager, new List[] {Arrays.asList("aa"), Arrays.asList((String) null)});

    caps.setFunctionSupport(SourceSystemFunctions.CONCAT, true);
    caps.setFunctionSupport(SourceSystemFunctions.IFNULL, true);
    caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);

    // will get replaced in the LanguageBridgeFactory
    helpPlan(
        sql,
        metadata,
        null,
        capFinder,
        new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"},
        ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
  }
Example #4
0
  /**
   * Note that we've optimized the ordering to be performed prior to the windowing. If we change the
   * windowing logic to not preserve the incoming row ordering, then this optimization will need to
   * change
   *
   * @throws Exception
   */
  @Test
  public void testCountDuplicates() throws Exception {
    String sql = "select e1, count(e1) over (order by e1) as c from pm1.g1 order by e1";

    List<?>[] expected =
        new List[] {
          Arrays.asList("a", 2), Arrays.asList("a", 2), Arrays.asList("b", 3),
        };

    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(
        "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0",
        new List[] {Arrays.asList("a"), Arrays.asList("a"), Arrays.asList("b")});
    ProcessorPlan plan =
        helpGetPlan(sql, RealMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder());

    helpProcess(plan, dataManager, expected);
  }
  @Test
  public void testMustPushdownOverMultipleSources() throws Exception {
    QueryMetadataInterface metadata =
        RealMetadataFactory.createTransformationMetadata(
            RealMetadataFactory.example1Cached().getMetadataStore(),
            "example1",
            new FunctionTree("foo", new FakeFunctionMetadataSource()));

    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setFunctionSupport("misc.namespace.func", true);
    capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$
    capFinder.addCapabilities("pm2", caps); // $NON-NLS-1$

    String sql = "select func(x.e1) from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2"; // $NON-NLS-1$

    ProcessorPlan plan =
        helpPlan(
            sql,
            metadata,
            null,
            capFinder,
            new String[] {
              "SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0",
              "SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0"
            },
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(
        "SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0",
        new List[] {Arrays.asList(1, "a")});
    dataManager.addData(
        "SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0",
        new List[] {Arrays.asList(1), Arrays.asList(2)});

    TestProcessor.helpProcess(plan, dataManager, new List[] {Arrays.asList("a")});
  }