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

Example 41 with Command

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

the class TestProcessor method testCaseInGroupByAndHavingCantPush3.

@Test
public void testCaseInGroupByAndHavingCantPush3() {
    String sql = // $NON-NLS-1$
    "SELECT s, c FROM (" + // $NON-NLS-1$
    "SELECT sum (IntKey) s, case when IntKey>=5000 then '5000 +' else '0-999' end || 'x' c " + // $NON-NLS-1$
    "FROM BQT1.SmallA GROUP BY case when IntKey>=5000 then '5000 +' else '0-999' end " + // $NON-NLS-1$
    ") AS x WHERE length(c) > 5 AND s = 20002";
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    // Can't push GROUP BY
    caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, false);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true);
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT1", caps);
    QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    Set actualQueries = TestOptimizer.getAtomicQueries(plan);
    // $NON-NLS-1$
    String expectedSql = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA";
    assertEquals(1, actualQueries.size());
    assertEquals(expectedSql, actualQueries.iterator().next().toString());
    List[] input1 = new List[] { Arrays.asList(new Object[] { new Integer(2) }), Arrays.asList(new Object[] { new Integer(4) }), Arrays.asList(new Object[] { new Integer(10000) }), Arrays.asList(new Object[] { new Integer(10002) }) };
    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(expectedSql, input1);
    List[] expected = new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { new Long(20002), "5000 +x" }) };
    helpProcess(plan, dataManager, expected);
}

Example 42 with Command

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

the class TestProcessor method testCorrelatedSubqueryCase3667.

/**
 * There is a bug when the second query in a UNION ALL has a correlated subquery, and both
 * the outer and inner query are selecting from the same virtual group, and aliasing them
 * differently to distinguish between them.  The generated atomic query has screwed up
 * aliasing.
 */
@Test
public void testCorrelatedSubqueryCase3667() {
    HardcodedDataManager dataManager = new HardcodedDataManager();
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true);
    caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
    caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
    caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
    caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
    caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
    caps.setCapabilitySupport(Capability.QUERY_UNION, true);
    caps.setCapabilitySupport(Capability.QUERY_SET_ORDER_BY, true);
    caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm2", caps);
    // Plan query
    String sql = // $NON-NLS-1$
    "Select e1, e2, e4 from pm2.g1 where 1=2 " + // $NON-NLS-1$
    "UNION ALL Select e1, e2, e4 from vm1.g1 outg1 where outg1.e2 in (select ing1.e2 FROM vm1.g1 ing1 WHERE outg1.e4 = ing1.e4)";
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, RealMetadataFactory.example1Cached(), capFinder);
    // Run query
    List[] expected = new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { "aString", new Integer(22), new Double(22.0) }) };
    // $NON-NLS-1$
    dataManager.addData(// $NON-NLS-1$
    "SELECT g_0.e1, g_0.e2, g_0.e4 FROM pm1.g1 AS g_0 WHERE g_0.e2 IN (SELECT g_1.e2 FROM pm1.g1 AS g_1 WHERE g_1.e4 = g_0.e4)", expected);
    helpProcess(plan, dataManager, expected);
}

Example 43 with Command

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

the class TestProcessor method testDefect15355e.

@Test
public void testDefect15355e() throws Exception {
    // $NON-NLS-1$
    String sql = "SELECT BigIntegerValue, StringKey FROM VQT.Defect15355 WHERE StringKey = '0'";
    // Create expected results
    List[] expected = new List[] { // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { new BigInteger("0"), "0" }) };
    // Construct data manager with data
    FakeDataManager dataManager = new FakeDataManager();
    sampleDataBQT2a(dataManager);
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT1", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT2", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT3", caps);
    QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    // Run query
    helpProcess(plan, dataManager, expected);
}

Example 44 with Command

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

the class TestProcessor method testDefect15355f.

@Test
public void testDefect15355f() throws Exception {
    // $NON-NLS-1$
    String sql = "SELECT BigIntegerValue FROM VQT.Defect15355 WHERE StringKey = '0'";
    // Create expected results
    List[] expected = new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { new BigInteger("0") }) };
    // Construct data manager with data
    FakeDataManager dataManager = new FakeDataManager();
    sampleDataBQT2a(dataManager);
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT1", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT2", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT3", caps);
    QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    // Run query
    helpProcess(plan, dataManager, expected);
}

Example 45 with Command

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

the class TestProcessor method testDefect15355b.

@Test
public void testDefect15355b() throws Exception {
    String sql = // $NON-NLS-1$
    "SELECT StringKey, BigIntegerValue FROM BQT1.SmallA " + // $NON-NLS-1$
    "UNION ALL " + // $NON-NLS-1$
    "SELECT StringKey, (SELECT BigIntegerValue FROM BQT3.SmallA WHERE BQT3.SmallA.BigIntegerValue = BQT2.SmallA.StringNum) FROM BQT2.SmallA";
    // Create expected results
    List[] expected = new List[] { // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "0", new BigInteger("0") }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "1", new BigInteger("1") }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "2", new BigInteger("2") }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "0", new BigInteger("0") }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "1", new BigInteger("1") }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "2", new BigInteger("2") }) };
    // Construct data manager with data
    FakeDataManager dataManager = new FakeDataManager();
    sampleDataBQT2a(dataManager);
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT1", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT2", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("BQT3", caps);
    QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    // Run query
    helpProcess(plan, dataManager, expected);
}