Search in sources :

Example 46 with Command

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

the class TestProcessor method testCase2634.

@Test
public void testCase2634() {
    // $NON-NLS-1$
    String sql = "SELECT x, IntKey FROM (SELECT IntKey, 'a' AS x FROM BQT1.SmallA UNION ALL SELECT IntKey, 'b' AS x FROM BQT1.SmallB) as Z";
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
    caps.setCapabilitySupport(Capability.QUERY_UNION, 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 'a' AS c_0, BQT1.SmallA.IntKey AS c_1 FROM BQT1.SmallA UNION ALL SELECT 'b' AS c_0, BQT1.SmallB.IntKey AS c_1 FROM BQT1.SmallB";
    assertEquals(1, actualQueries.size());
    assertEquals(expectedSql, actualQueries.iterator().next().toString());
    List[] input1 = new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { "a", new Integer(0) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "a", new Integer(1) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "b", new Integer(0) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "b", new Integer(1) }) };
    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(expectedSql, input1);
    List[] expected = new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { "a", new Integer(0) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "a", new Integer(1) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "b", new Integer(0) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "b", new Integer(1) }) };
    helpProcess(plan, dataManager, expected);
}
Also used : FakeCapabilitiesFinder(org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder) BigInteger(java.math.BigInteger) ColumnSet(org.teiid.metadata.ColumnSet) BasicSourceCapabilities(org.teiid.query.optimizer.capabilities.BasicSourceCapabilities) Command(org.teiid.query.sql.lang.Command) QueryMetadataInterface(org.teiid.query.metadata.QueryMetadataInterface) Test(org.junit.Test)

Example 47 with Command

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

the class TestProcessor method testNonJoinCriteriaInFromUsingDependentJoin.

@Test
public void testNonJoinCriteriaInFromUsingDependentJoin() {
    // $NON-NLS-1$
    String sql = "SELECT a.e1, b.e1, b.e2 FROM pm1.g1 a LEFT OUTER JOIN pm2.g1 b ON a.e1=b.e1 AND b.e2 = 0";
    // Create expected results
    List[] expected = new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { "a", null, null }), // $NON-NLS-1$ //$NON-NLS-2$
    Arrays.asList(new Object[] { "b", "b", new Integer(0) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "c", null, null }) };
    // Construct data manager with data
    FakeDataManager dataManager = new FakeDataManager();
    sampleData2a(dataManager);
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm2", caps);
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, RealMetadataFactory.example1Cached(), capFinder);
    // Run query
    helpProcess(plan, dataManager, expected);
}
Also used : BigInteger(java.math.BigInteger) FakeCapabilitiesFinder(org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder) BasicSourceCapabilities(org.teiid.query.optimizer.capabilities.BasicSourceCapabilities) Command(org.teiid.query.sql.lang.Command) Test(org.junit.Test)

Example 48 with Command

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

the class TestProcessor method testCaseInGroupByAndHavingCantPush2.

@Test
public void testCaseInGroupByAndHavingCantPush2() {
    String sql = // $NON-NLS-1$
    "SELECT sum (IntKey), case when IntKey>=5000 then '5000 +' else '0-999' end || 'x' " + // $NON-NLS-1$
    "FROM BQT1.SmallA GROUP BY case when IntKey>=5000 then '5000 +' else '0-999' end " + // $NON-NLS-1$
    "HAVING length(case when IntKey>=5000 then '5000 +' else '0-999' end) > 5";
    // 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);
}
Also used : FakeCapabilitiesFinder(org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder) BigInteger(java.math.BigInteger) ColumnSet(org.teiid.metadata.ColumnSet) BasicSourceCapabilities(org.teiid.query.optimizer.capabilities.BasicSourceCapabilities) Command(org.teiid.query.sql.lang.Command) QueryMetadataInterface(org.teiid.query.metadata.QueryMetadataInterface) Test(org.junit.Test)

Example 49 with Command

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

the class TestProcessor method testDefect15355.

@Test
public void testDefect15355() throws Exception {
    String sql = // $NON-NLS-1$
    "SELECT e1, e1 FROM pm1.g1 " + // $NON-NLS-1$
    "UNION ALL " + // $NON-NLS-1$
    "SELECT e1, (SELECT e1 FROM pm2.g1 WHERE pm2.g1.e2 = pm1.g2.e2) FROM pm1.g2";
    // Create expected results
    List[] expected = new List[] { // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "a", "a" }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "b", "b" }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "c", "c" }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "a", "e" }), // $NON-NLS-1$  //$NON-NLS-2$
    Arrays.asList(new Object[] { "b", "b" }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "b", null }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "b", null }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "d", null }) };
    // Construct data manager with data
    FakeDataManager dataManager = new FakeDataManager();
    sampleData2(dataManager);
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm2", caps);
    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    // Run query
    helpProcess(plan, dataManager, expected);
}
Also used : FakeCapabilitiesFinder(org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder) BasicSourceCapabilities(org.teiid.query.optimizer.capabilities.BasicSourceCapabilities) Command(org.teiid.query.sql.lang.Command) QueryMetadataInterface(org.teiid.query.metadata.QueryMetadataInterface) Test(org.junit.Test)

Example 50 with Command

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

the class TestTextTable method testRowDelimiter.

@Test
public void testRowDelimiter() throws Exception {
    // $NON-NLS-1$
    String sql = "select x.* from texttable('x-1, y\n a -2, \"b\"-3' COLUMNS x string, \"1\" string ROW DELIMITER ',' DELIMITER '-' HEADER) x";
    List<?>[] expected = new List[] { Arrays.asList("y\n a", "2"), Arrays.asList("b", "3") };
    HardcodedDataManager dataManager = new HardcodedDataManager();
    Command cmd = helpParse(sql);
    assertEquals("SELECT x.* FROM TEXTTABLE('x-1, y\\u000A a -2, \"b\"-3' COLUMNS x string, \"1\" string ROW DELIMITER ',' DELIMITER '-' HEADER) AS x", cmd.toString());
    ProcessorPlan plan = helpGetPlan(cmd, RealMetadataFactory.example1Cached());
    helpProcess(plan, dataManager, expected);
}
Also used : Command(org.teiid.query.sql.lang.Command) ArrayList(java.util.ArrayList) List(java.util.List) Test(org.junit.Test)

Aggregations

Command (org.teiid.query.sql.lang.Command)232 Test (org.junit.Test)142 QueryMetadataInterface (org.teiid.query.metadata.QueryMetadataInterface)90 BasicSourceCapabilities (org.teiid.query.optimizer.capabilities.BasicSourceCapabilities)73 FakeCapabilitiesFinder (org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder)66 List (java.util.List)64 CommandContext (org.teiid.query.util.CommandContext)38 ArrayList (java.util.ArrayList)37 BatchedUpdateCommand (org.teiid.query.sql.lang.BatchedUpdateCommand)36 BigInteger (java.math.BigInteger)29 DefaultCapabilitiesFinder (org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder)25 AnalysisRecord (org.teiid.query.analysis.AnalysisRecord)21 GroupSymbol (org.teiid.query.sql.symbol.GroupSymbol)20 ProcessorPlan (org.teiid.query.processor.ProcessorPlan)19 TransformationMetadata (org.teiid.query.metadata.TransformationMetadata)16 LinkedList (java.util.LinkedList)10 QueryParser (org.teiid.query.parser.QueryParser)10 RelationalPlan (org.teiid.query.processor.relational.RelationalPlan)10 QueryCommand (org.teiid.query.sql.lang.QueryCommand)10 StoredProcedure (org.teiid.query.sql.lang.StoredProcedure)10