Examples with CalcitePlanner herddb.sql.CalcitePlanner used on opensource projects

Search in sources :

Example 1 with CalcitePlanner

use of herddb.sql.CalcitePlanner in project herddb by diennea.

the class SimpleJoinTest method testSimpleJoinNoWhere.

@Test
public void testSimpleJoinNoWhere() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
        execute(manager, "CREATE TABLE tblspace1.table2 (k2 string primary key,n2 int,s2 string)", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('a',1,'A')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('b',2,'B')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('c',3,'A')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('d',4,'A')", Collections.emptyList());
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("t:" + t);
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "c"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", "c"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", "d"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 >= 2" + "   and t2.n2 >= 4", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(1, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", "d"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.*,t2.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(3, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(3, t.getFieldNames().length);
                assertEquals("k2", t.getFieldNames()[0]);
                assertEquals("n2", t.getFieldNames()[1]);
                assertEquals("s2", t.getFieldNames()[2]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.s2 FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(1, t.getFieldNames().length);
                assertEquals("s2", t.getFieldNames()[0]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 4", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 <= t2.n2", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM " + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 <= t2.n2 " + "and t2.n2 <= 3", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM " + " tblspace1.table1 t1 " + " JOIN tblspace1.table2 t2 ON t1.n1 <= t2.n2 " + " and t2.n2 <= 3", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 + 3 = t2.n2", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(1, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.n2, t1.s1, t2.k2 FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(3, t.getFieldNames().length);
                assertEquals("n2", t.getFieldNames()[0]);
                assertEquals("s1", t.getFieldNames()[1]);
                assertEquals("k2", t.getFieldNames()[2]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "A", "k2", "c", "n2", 3))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "A", "k2", "d", "n2", 4))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "B", "k2", "c", "n2", 3))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "B", "k2", "d", "n2", 4))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.*, t1.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k2", t.getFieldNames()[0]);
                assertEquals("n2", t.getFieldNames()[1]);
                assertEquals("s2", t.getFieldNames()[2]);
                assertEquals("k1", t.getFieldNames()[3]);
                assertEquals("n1", t.getFieldNames()[4]);
                assertEquals("s1", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2 " + " ORDER BY n2,n1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            int i = 0;
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A")));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2 " + " ORDER BY n2 desc,n1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            int i = 0;
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A")));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 + 3 <= t2.n2", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(1, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d"))));
        }
        if (manager.getPlanner() instanceof CalcitePlanner) {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2, t1.s1, t2.s2 FROM" + " tblspace1.table1 t1 " + " LEFT JOIN tblspace1.table2 t2 " + " ON t1.s1 = t2.s2" + " ", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(4, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("s2", t.getFieldNames()[3]);
            }
            assertEquals(3, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "c", "s1", "A", "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d", "s1", "A", "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", null, "s1", "B", "s2", null))));
        }
        if (manager.getPlanner() instanceof CalcitePlanner) {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2, t1.s1, t2.s2 FROM" + " tblspace1.table1 t1 " + " RIGHT JOIN tblspace1.table2 t2 " + " ON t1.s1 = t2.s2" + " ", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(4, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("s2", t.getFieldNames()[3]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "c", "s1", "A", "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d", "s1", "A", "s2", "A"))));
        }
    }
}
Also used : DataAccessor(herddb.utils.DataAccessor) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) Assume.assumeThat(org.junit.Assume.assumeThat) Assert.assertTrue(org.junit.Assert.assertTrue) Test(org.junit.Test) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) CoreMatchers.instanceOf(org.hamcrest.CoreMatchers.instanceOf) List(java.util.List) TestUtils.scan(herddb.core.TestUtils.scan) TransactionContext(herddb.model.TransactionContext) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) StatementEvaluationContext(herddb.model.StatementEvaluationContext) TestUtils.execute(herddb.core.TestUtils.execute) MapUtils(herddb.utils.MapUtils) RawString(herddb.utils.RawString) Collections(java.util.Collections) Assert.assertEquals(org.junit.Assert.assertEquals) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) CalcitePlanner(herddb.sql.CalcitePlanner) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) DataAccessor(herddb.utils.DataAccessor) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) CalcitePlanner(herddb.sql.CalcitePlanner) List(java.util.List) RawString(herddb.utils.RawString) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Example 2 with CalcitePlanner

use of herddb.sql.CalcitePlanner in project herddb by diennea.

the class DropTableSQLTest method dropTableWithTransaction.

@Test
public void dropTableWithTransaction() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        long tx = ((TransactionResult) execute(manager, "EXECUTE begintransaction 'tblspace1'", Collections.emptyList())).getTransactionId();
        execute(manager, "CREATE TABLE tblspace1.tsql (k1 string primary key,n1 int,s1 string)", Collections.emptyList(), new TransactionContext(tx));
        execute(manager, "INSERT INTO tblspace1.tsql (k1) values('a')", Collections.emptyList(), new TransactionContext(tx));
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql ", Collections.emptyList())) {
            fail();
        } catch (TableDoesNotExistException ok) {
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.systables where table_name='tsql'", Collections.emptyList())) {
            List<DataAccessor> all = scan.consume();
            assertEquals(0, all.size());
        }
        execute(manager, "EXECUTE committransaction 'tblspace1'," + tx, Collections.emptyList());
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql ", Collections.emptyList())) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.systables where table_name='tsql'", Collections.emptyList())) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
        }
        long tx2 = ((TransactionResult) execute(manager, "EXECUTE begintransaction 'tblspace1'", Collections.emptyList())).getTransactionId();
        execute(manager, "DROP TABLE tblspace1.tsql", Collections.emptyList(), new TransactionContext(tx2));
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql ", Collections.emptyList())) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.systables where table_name='tsql'", Collections.emptyList())) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
        }
        execute(manager, "EXECUTE committransaction 'tblspace1'," + tx2, Collections.emptyList());
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.systables where table_name='tsql'", Collections.emptyList())) {
            List<DataAccessor> all = scan.consume();
            assertEquals(0, all.size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql ", Collections.emptyList())) {
            fail();
        } catch (TableDoesNotExistException ok) {
            assertTrue(manager.getPlanner() instanceof SQLPlanner);
        } catch (StatementExecutionException ok) {
            assertEquals("From line 1, column 15 to line 1, column 28: Object 'TSQL' not found within 'tblspace1'", ok.getMessage());
            assertTrue(manager.getPlanner() instanceof CalcitePlanner);
        }
    }
}
Also used : TransactionResult(herddb.model.TransactionResult) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) DataAccessor(herddb.utils.DataAccessor) StatementExecutionException(herddb.model.StatementExecutionException) TableDoesNotExistException(herddb.model.TableDoesNotExistException) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DataScanner(herddb.model.DataScanner) TransactionContext(herddb.model.TransactionContext) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) CalcitePlanner(herddb.sql.CalcitePlanner) SQLPlanner(herddb.sql.SQLPlanner) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Example 3 with CalcitePlanner

use of herddb.sql.CalcitePlanner in project herddb by diennea.

the class RawSQLTest method limitsTest.

@Test
public void limitsTest() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.tsql (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey2", Integer.valueOf(2))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey3", Integer.valueOf(3))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1) values(?)", Arrays.asList("mykey4")).getUpdateCount());
        // scan performed at "scan time"
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql ORDER BY k1 LIMIT 1", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(1, result.size());
            assertEquals(RawString.of("mykey"), result.get(0).get("k1"));
        }
        if (manager.getPlanner() instanceof SQLPlanner) {
            try (DataScanner scan1 = scan(manager, "SELECT TOP 1 * FROM tblspace1.tsql ORDER BY k1", Collections.emptyList())) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(1, result.size());
                assertEquals(RawString.of("mykey"), result.get(0).get("k1"));
            }
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql ORDER BY k1 LIMIT 1,1", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(1, result.size());
            assertEquals(RawString.of("mykey2"), result.get(0).get("k1"));
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql ORDER BY k1 LIMIT 1,2", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(2, result.size());
            assertEquals(RawString.of("mykey2"), result.get(0).get("k1"));
            assertEquals(RawString.of("mykey3"), result.get(1).get("k1"));
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql ORDER BY k1 LIMIT 10", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(4, result.size());
            assertEquals(RawString.of("mykey"), result.get(0).get("k1"));
            assertEquals(RawString.of("mykey2"), result.get(1).get("k1"));
            assertEquals(RawString.of("mykey3"), result.get(2).get("k1"));
            assertEquals(RawString.of("mykey4"), result.get(3).get("k1"));
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql ORDER BY k1 LIMIT 10,10", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(0, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql ORDER BY k1 LIMIT 4,10", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(0, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql ORDER BY k1 LIMIT 3,10", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(1, result.size());
            assertEquals(RawString.of("mykey4"), result.get(0).get("k1"));
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql LIMIT 3", Collections.emptyList(), 2, TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(2, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql LIMIT ?", Arrays.asList(3), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(3, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql LIMIT 1,2", Collections.emptyList(), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(2, result.size());
        }
        if (manager.getPlanner() instanceof CalcitePlanner) {
            try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql LIMIT ?,?", Arrays.asList(1, 2), TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(2, result.size());
            }
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql " + "WHERE k1 <> ? LIMIT ?", Arrays.asList("aaa", 3), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(3, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql " + "WHERE k1 <> ? LIMIT 2,?", Arrays.asList("aaa", 3), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(2, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql " + "WHERE k1 <> ? ORDER BY k1 LIMIT ?", Arrays.asList("aaa", 3), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(3, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT k1, count(*) FROM tblspace1.tsql " + "WHERE k1 <> ? GROUP BY k1 ORDER BY k1 LIMIT ?", Arrays.asList("aaa", 3), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(3, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql LIMIT ?", Arrays.asList(0), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(4, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql LIMIT ?", Collections.emptyList(), TransactionContext.NO_TRANSACTION)) {
            fail();
        } catch (MissingJDBCParameterException err) {
            assertEquals(1, err.getIndex());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql " + "ORDER BY k1 LIMIT 3", Collections.emptyList(), 5, TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(3, result.size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql " + "ORDER BY k1 ", Collections.emptyList(), 2, TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(2, result.size());
        }
    }
}
Also used : CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DataScanner(herddb.model.DataScanner) MissingJDBCParameterException(herddb.model.MissingJDBCParameterException) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) DataAccessor(herddb.utils.DataAccessor) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) CalcitePlanner(herddb.sql.CalcitePlanner) RawString(herddb.utils.RawString) SQLPlanner(herddb.sql.SQLPlanner) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Example 4 with CalcitePlanner

use of herddb.sql.CalcitePlanner in project herddb by diennea.

the class SimpleOperatorsTest method simpleArithmeticOperationsTest.

@Test
public void simpleArithmeticOperationsTest() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.tsql (k1 string primary key, n1 int, l1 long, t1 timestamp, nu string, b1 bool, d1 double)", Collections.emptyList());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,l1,t1,nu,b1,d1) values(?,?,?,?,?,?,?)", Arrays.asList("mykey", Integer.valueOf(1), Long.valueOf(2), new java.sql.Timestamp(System.currentTimeMillis()), null, Boolean.valueOf(true), Double.valueOf(1.5))).getUpdateCount());
        // Simple constants
        try (DataScanner scan1 = scan(manager, "SELECT 0.5 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(0.5, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT 1 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(1L, ((Number) scan1.consume().get(0).get(0)).longValue());
        }
        try (DataScanner scan1 = scan(manager, "SELECT 'asd' FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals("asd", scan1.consume().get(0).get(0).toString());
        }
        try (DataScanner scan1 = scan(manager, "SELECT true FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(true, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT false FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(false, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT CURRENT_TIMESTAMP FROM tblspace1.tsql", Collections.emptyList())) {
            long instant = ((java.sql.Timestamp) scan1.consume().get(0).get(0)).getTime();
            assertTrue(Math.abs(System.currentTimeMillis() - instant) < 200);
        }
        // Simple column access
        try (DataScanner scan1 = scan(manager, "SELECT k1 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals("mykey", scan1.consume().get(0).get(0).toString());
        }
        try (DataScanner scan1 = scan(manager, "SELECT n1 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(1, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT l1 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(2L, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT t1 FROM tblspace1.tsql", Collections.emptyList())) {
            final Timestamp currentTs = (java.sql.Timestamp) scan1.consume().get(0).get(0);
            System.out.println("currentTs:" + currentTs);
            long instant = currentTs.getTime();
            long delta = System.currentTimeMillis() - instant;
            System.out.println("delta:" + delta);
            assertTrue("too slow ? " + delta, delta < 60000);
        }
        try (DataScanner scan1 = scan(manager, "SELECT nu FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(null, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT b1 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(true, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT d1 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(1.5, scan1.consume().get(0).get(0));
        }
        // Simple expressions
        try (DataScanner scan1 = scan(manager, "SELECT 4+3+2 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(9.0, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT 7-3-2 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(2.0, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT 1/2/2 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(0.25, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT 4*3*2 FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(24.0, scan1.consume().get(0).get(0));
        }
        // Functions
        try (DataScanner scan1 = scan(manager, "SELECT lower('CiAo') FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals("ciao", scan1.consume().get(0).get(0).toString());
        }
        try (DataScanner scan1 = scan(manager, "SELECT upper('CiAo') FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals("CIAO", scan1.consume().get(0).get(0).toString());
        }
        try (DataScanner scan1 = scan(manager, "SELECT abs(-123) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(123L, ((Number) scan1.consume().get(0).get(0)).longValue());
        }
        try (DataScanner scan1 = scan(manager, "SELECT abs(123) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(123L, ((Number) scan1.consume().get(0).get(0)).longValue());
        }
        try (DataScanner scan1 = scan(manager, "SELECT abs(-123.5) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(123.5, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT abs(123.5) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(123.5, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT round(98765.98765) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(98766.0, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT round(98765.98765, 2) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(98765.99, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT round(98765.98765, -2) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(98800.0, scan1.consume().get(0).get(0));
        }
        // Warning: jSQLParser doesn't handle this kind of expressions in select clause
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1<2", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 2<1", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1<1", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 2>1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1>2", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1>1", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1<=2", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 2<=1", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1<=1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 2>=1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1>=2", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1>=1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 2=1", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1=1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1<>2", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        // Logic expressions
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE (1>2) or (1>0)", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE (1>2) or not (1>0)", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE (1>2) and (1>0)", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE not (1>2) and (1>0)", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        // Warning: Parser doesn't handle this kind of expressions in select clause
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE null is null", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE null is not null", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1 is null", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1 is not null", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        // Case expressions
        try (DataScanner scan1 = scan(manager, "SELECT CASE " + " WHEN k1='mykey' THEN 1 " + " WHEN k1='mykeys' THEN 2 " + " ELSE 3 " + "END as mycase " + "FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(1L, ((Number) scan1.consume().get(0).get(0)).longValue());
        }
        try (DataScanner scan1 = scan(manager, "SELECT CASE " + " WHEN k1='mykeys' THEN 1 " + " WHEN k1='mykey' THEN 2 " + " ELSE 3 " + "END as mycase " + "FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(2L, ((Number) scan1.consume().get(0).get(0)).longValue());
        }
        try (DataScanner scan1 = scan(manager, "SELECT CASE " + " WHEN k1='mykeys' THEN 1 " + " WHEN k1='mykeyb' THEN 2 " + " ELSE 3 " + "END as mycase " + "FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(3L, ((Number) scan1.consume().get(0).get(0)).longValue());
        }
        // Warning: Parser doesn't handle this kind of expressions in select clause
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'AbBbCc' LIKE '_b____'", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'AbBbCc' LIKE '_B____'", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'AbBbCc' LIKE '_b%'", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'AbBbCc' LIKE '_d%'", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'AbBbCc' LIKE 'AbBbCc'", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'AbBbCc' LIKE '%AbBbCc%'", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        // Warning: jsqlParser doesn't handle this kind of expressions in select clause
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE '1' in (1,2,3)", Collections.emptyList())) {
            if (manager.getPlanner() instanceof CalcitePlanner) {
                assertEquals(0, scan1.consume().size());
            } else {
                assertEquals(1, scan1.consume().size());
            }
        }
        if ((manager.getPlanner() instanceof SQLPlanner)) {
            try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE '1' in ('1',2,3)", Collections.emptyList())) {
                assertEquals(1, scan1.consume().size());
            }
            try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'b' in ('1',2,3)", Collections.emptyList())) {
                assertEquals(0, scan1.consume().size());
            }
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 'b' in (1)", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        // Warning: Parser doesn't handle this kind of expressions in select clause
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 3 BETWEEN 1 AND 5", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 1 BETWEEN 1 AND 5", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 5 BETWEEN 1 AND 5", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 0 BETWEEN 1 AND 5", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE 6 BETWEEN 1 AND 5", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT ((4+(3+2)-1)*2) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(16.0, scan1.consume().get(0).get(0));
        }
        try (DataScanner scan1 = scan(manager, "SELECT ((3/2)*3+(1/2)) FROM tblspace1.tsql", Collections.emptyList())) {
            assertEquals(5.0, scan1.consume().get(0).get(0));
        }
    }
}
Also used : CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DataScanner(herddb.model.DataScanner) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) CalcitePlanner(herddb.sql.CalcitePlanner) SQLPlanner(herddb.sql.SQLPlanner) Timestamp(java.sql.Timestamp) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Aggregations

MemoryCommitLogManager (herddb.mem.MemoryCommitLogManager)4 MemoryDataStorageManager (herddb.mem.MemoryDataStorageManager)4 MemoryMetadataStorageManager (herddb.mem.MemoryMetadataStorageManager)4 CreateTableSpaceStatement (herddb.model.commands.CreateTableSpaceStatement)4 CalcitePlanner (herddb.sql.CalcitePlanner)4 Test (org.junit.Test)4 DataScanner (herddb.model.DataScanner)3 SQLPlanner (herddb.sql.SQLPlanner)3 DataAccessor (herddb.utils.DataAccessor)3 TransactionContext (herddb.model.TransactionContext)2 RawString (herddb.utils.RawString)2 TestUtils.execute (herddb.core.TestUtils.execute)1 TestUtils.scan (herddb.core.TestUtils.scan)1 MissingJDBCParameterException (herddb.model.MissingJDBCParameterException)1 StatementEvaluationContext (herddb.model.StatementEvaluationContext)1 StatementExecutionException (herddb.model.StatementExecutionException)1 TableDoesNotExistException (herddb.model.TableDoesNotExistException)1 TransactionResult (herddb.model.TransactionResult)1 MapUtils (herddb.utils.MapUtils)1 Timestamp (java.sql.Timestamp)1
About Me
UseOf

Java Tutorials :

Simple Java RabbitMQ Example with Spring
Simple Springboot JPA Eclipeslink Example
Simple SpringBoot JPA Hibernate Example
Jackson JSON @JsonIgnore and @JsonIgnoreProperties Examples
Java Infinite recursion (StackOverflowError) Jackson solutions
Simple Java Jackson Serialize Objects to JSON and convert them back To Object
ElasticSearch 5 - Upload files using Java API in binary field Example
Java JAXB marshall unmarshall Examples from String and Stream
Java 8 forEach List and Map examples
ElasticSearch 5 Java API SearchRequestBuilder examples
Java ElasticSearch 5 examples with node index and mapping - running local
Convert String to int or Integer Java 8
Java 9 in action - JShell - Ubuntu
Java - removing invalid characters from a file name
Hello world!? or Hello Tutorial