Examples with Command org.teiid.query.sql.lang.Command used on opensource projects

Example 51 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestTextTable method testTextTableSelector1.

@Test
public void testTextTableSelector1() throws Exception {
    String sql = "select x.* from texttable('cc,bb' SELECTOR 'c' COLUMNS x string) x";
    Command c = QueryParser.getQueryParser().parseCommand(sql);
    assertEquals("SELECT x.* FROM TEXTTABLE('cc,bb' SELECTOR 'c' COLUMNS x string) AS x", c.toString());
    assertEquals("SELECT x.* FROM TEXTTABLE('cc,bb' SELECTOR 'c' COLUMNS x string) AS x", c.clone().toString());
    List<?>[] expected = new List<?>[] {};
    process(sql, expected);
}

Example 52 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestTextTable method testTextAggNoQuote.

@Test
public void testTextAggNoQuote() throws Exception {
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
    String sql = "select convert(to_chars(textagg(col, col1 no quote), 'UTF-8'), string) as x from (select '1' as col, 2 as col1 union all select 'abc', 3) as v";
    Command c = TestProcessor.helpParse(sql);
    assertEquals("SELECT convert(to_chars(TEXTAGG(FOR col, col1 NO QUOTE), 'UTF-8'), string) AS x FROM (SELECT '1' AS col, 2 AS col1 UNION ALL SELECT 'abc', 3) AS v", c.toString());
    ProcessorPlan plan = helpPlan(// $NON-NLS-1$
    sql, // $NON-NLS-1$
    metadata, // $NON-NLS-1$
    null, // $NON-NLS-1$
    capFinder, new String[] {}, // $NON-NLS-1$
    ComparisonMode.EXACT_COMMAND_STRING);
    String nl = System.getProperty("line.separator");
    ArrayList<Object> list = new ArrayList<Object>();
    list.add("1,2" + nl + "abc,3" + nl);
    List<?>[] expected = new List<?>[] { list };
    helpProcess(plan, new HardcodedDataManager(), expected);
}

Example 53 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestVirtualDepJoin method helpTestVirtualDepJoinSourceSelection.

public void helpTestVirtualDepJoinSourceSelection(boolean setPushdown) throws Exception {
    // Create query
    // $NON-NLS-1$
    String sql = "select c.id as CustomerID, First, Last, a.account_id as AccountID, transaction_id as TransactionID, txn_type AS TxnCode, Amount, source from (CustomerMaster.Customers c join CustomerMaster.Locations l on c.id=l.id) join Accounts.Accounts a on c.id=a.customer_id and l.location=a.source where c.first='Miles' order by accountid option makenotdep c, l";
    // Create expected results
    List[] expected = new List[] { // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20"), "EU" }), // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00"), "EU" }), // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00"), "US" }), // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00"), "US" }), // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00"), "US" }), // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00"), "US" }), // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00"), "US" }) };
    // Construct data manager with data
    QueryMetadataInterface metadata = exampleVirtualDepJoin();
    FakeDataManager dataManager = new FakeDataManager();
    sampleDataVirtualDepJoin(dataManager, metadata);
    // Plan query
    CommandContext context = TestProcessor.createCommandContext();
    Command command = TestProcessor.helpParse(sql);
    FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.QUERY_ORDERBY, false);
    caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false);
    caps.setCapabilitySupport(Capability.CRITERIA_ISNULL, false);
    caps.setCapabilitySupport(Capability.CRITERIA_IN, setPushdown);
    // $NON-NLS-1$
    finder.addCapabilities("US", caps);
    // $NON-NLS-1$
    finder.addCapabilities("Europe", caps);
    // $NON-NLS-1$
    finder.addCapabilities("CustomerMaster", caps);
    ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context);
    // Check plan contents
    int selectCount = !setPushdown ? 3 : 0;
    int accessCount = setPushdown ? 1 : 4;
    int depAccessCount = 4 - accessCount;
    TestOptimizer.checkNodeTypes(plan, new int[] { // Access
    accessCount, // DependentAccess
    depAccessCount, // DependentSelect
    0, // DependentProject
    0, // DupRemove
    0, // Grouping
    0, // NestedLoopJoinStrategy
    0, // MergeJoinStrategy
    2, // Null
    0, // PlanExecution
    0, // Project
    3, // Select
    selectCount, // Sort
    1, // UnionAll
    1 });
    // Run query
    TestProcessor.helpProcess(plan, context, dataManager, expected);
}

Example 54 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestVirtualDepJoin method helpTestMultipleBatches.

private void helpTestMultipleBatches(boolean unique) throws Exception, TeiidComponentException, TeiidException, SQLException {
    // Create query
    // $NON-NLS-1$
    String sql = "SELECT * from Master.Transactions where last = 'Davis' order by CustomerID, TransactionID";
    List<List<Object>> expected = new LinkedList<List<Object>>();
    // Create expected results
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00") }));
    if (!unique) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00") }));
    }
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00") }));
    if (!unique) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00") }));
    }
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00") }));
    if (!unique) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00") }));
    }
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00") }));
    if (!unique) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00") }));
    }
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00") }));
    if (!unique) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00") }));
    }
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20") }));
    if (!unique) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20") }));
    }
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00") }));
    if (!unique) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00") }));
    }
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(16000), new Long(207), new Integer(3), new BigDecimal("12.34") }));
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(16000), new Long(299), new Integer(3), new BigDecimal("950.34") }));
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(550), new Long(1004), new Integer(3), new BigDecimal("542.20") }));
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(550), new Long(1005), new Integer(1), new BigDecimal("99.99") }));
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(300), "CloneB", "Davis", new Long(620), new Long(1006), new Integer(1), new BigDecimal("10000.00") }));
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(300), "CloneB", "Davis", new Long(620), new Long(1007), new Integer(2), new BigDecimal("0.75") }));
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    expected.add(Arrays.asList(new Object[] { new Long(300), "CloneB", "Davis", new Long(630), new Long(1008), new Integer(2), new BigDecimal("62.00") }));
    // Construct data manager with data
    QueryMetadataInterface metadata = exampleVirtualDepJoin();
    FakeDataManager dataManager = new FakeDataManager();
    sampleDataVirtualDepJoin(dataManager, metadata);
    overrideVirtualDepJoinData(dataManager, metadata, unique);
    // Plan query
    CommandContext context = TestProcessor.createCommandContext();
    Command command = TestProcessor.helpParse(sql);
    FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setSourceProperty(Capability.MAX_IN_CRITERIA_SIZE, new Integer(1));
    // $NON-NLS-1$
    finder.addCapabilities("US", caps);
    // $NON-NLS-1$
    finder.addCapabilities("Europe", caps);
    // $NON-NLS-1$
    finder.addCapabilities("CustomerMaster", caps);
    ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context);
    // Run query
    BufferManager bufferMgr = createCustomBufferMgr(2);
    QueryProcessor processor = new QueryProcessor(plan, context, bufferMgr, dataManager);
    processor.setNonBlocking(true);
    BatchCollector collector = processor.createBatchCollector();
    TupleBuffer id = collector.collectTuples();
    TestProcessor.examineResults(expected.toArray(new List[expected.size()]), bufferMgr, id);
}

Example 55 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestVirtualDepJoin method testVirtualDepJoinOverAggregates2.

/**
 *  same as testVirtualDepJoinOverAggregate, but the makenotdep hint prevents the
 *  dependent join from happening
 */
@Test
public void testVirtualDepJoinOverAggregates2() throws Exception {
    // Create query
    // $NON-NLS-1$
    String sql = "select first, last, sum(amount) from Europe.CustAccts e makenotdep join CustomerMaster.Customers c on c.id=e.id where c.first='Miles' group by c.id, first, last";
    // Create expected results
    List[] expected = new List[] { // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    Arrays.asList(new Object[] { "Miles", "Davis", new BigDecimal("1007.20") }) };
    // Construct data manager with data
    QueryMetadataInterface metadata = exampleVirtualDepJoin();
    FakeDataManager dataManager = new FakeDataManager();
    sampleDataVirtualDepJoin(dataManager, metadata);
    // Plan query
    CommandContext context = TestProcessor.createCommandContext();
    Command command = TestProcessor.helpParse(sql);
    FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false);
    // $NON-NLS-1$
    finder.addCapabilities("Europe", caps);
    // $NON-NLS-1$
    finder.addCapabilities("CustomerMaster", caps);
    ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context);
    TestOptimizer.checkNodeTypes(plan, new int[] { // Access
    2, // DependentAccess
    0, // DependentSelect
    0, // DependentProject
    0, // DupRemove
    0, // Grouping
    2, // NestedLoopJoinStrategy
    0, // MergeJoinStrategy
    1, // Null
    0, // PlanExecution
    0, // Project
    1, // Select
    0, // Sort
    0, // UnionAll
    0 });
    TestOptimizer.checkDependentJoinCount(plan, 0);
    // Run query
    TestProcessor.helpProcess(plan, context, dataManager, expected);
}