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

Example 6 with SQLPlanner

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

the class RawSQLTest method insertFromSelect.

@Test
public void insertFromSelect() 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,t1 timestamp)", Collections.emptyList());
        execute(manager, "CREATE TABLE tblspace1.tsql2 (k2 string primary key,n2 int,s2 string,t2 timestamp)", Collections.emptyList());
        java.sql.Timestamp tt1 = new java.sql.Timestamp(System.currentTimeMillis());
        java.sql.Timestamp tt2 = new java.sql.Timestamp(System.currentTimeMillis() + 60000);
        java.sql.Timestamp tt3 = new java.sql.Timestamp(System.currentTimeMillis() + 120000);
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,t1) values(?,?,?)", Arrays.asList("mykey", Integer.valueOf(1234), tt1)).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,t1) values(?,?,?)", Arrays.asList("mykey2", Integer.valueOf(1235), tt2)).getUpdateCount());
        assertEquals(2, executeUpdate(manager, "INSERT INTO tblspace1.tsql2(k2,t2,n2)" + "(select k1,t1,n1 from tblspace1.tsql)", Collections.emptyList()).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT k2,n2,t2 FROM tblspace1.tsql2 ORDER BY n2 desc", Collections.emptyList())) {
            List<DataAccessor> res = scan.consume();
            assertEquals(RawString.of("mykey2"), res.get(0).get("k2"));
            assertEquals(RawString.of("mykey"), res.get(1).get("k2"));
            assertEquals(Integer.valueOf(1235), res.get(0).get("n2"));
            assertEquals(Integer.valueOf(1234), res.get(1).get("n2"));
            assertEquals(tt2, res.get(0).get("t2"));
            assertEquals(tt1, res.get(1).get("t2"));
        }
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,t1) values(?,?,?)", Arrays.asList("mykey3", Integer.valueOf(1236), tt1)).getUpdateCount());
        DMLStatementExecutionResult executeUpdateInTransaction = executeUpdate(manager, "INSERT INTO tblspace1.tsql2(k2,t2,n2)" + "(select k1,t1,n1 from tblspace1.tsql where n1=?)", Arrays.asList(1236), TransactionContext.AUTOTRANSACTION_TRANSACTION);
        assertEquals(1, executeUpdateInTransaction.getUpdateCount());
        assertTrue(executeUpdateInTransaction.transactionId > 0);
        try (DataScanner scan = scan(manager, "SELECT k2,n2,t2 FROM tblspace1.tsql2 ORDER BY n2 desc", Collections.emptyList(), new TransactionContext(executeUpdateInTransaction.transactionId))) {
            assertEquals(3, scan.consume().size());
        }
        manager.executeStatement(new RollbackTransactionStatement("tblspace1", executeUpdateInTransaction.transactionId), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        try (DataScanner scan = scan(manager, "SELECT k2,n2,t2 FROM tblspace1.tsql2 ORDER BY n2 desc", Collections.emptyList())) {
            assertEquals(2, scan.consume().size());
        }
        DMLStatementExecutionResult executeUpdateInTransaction2 = executeUpdate(manager, "INSERT INTO tblspace1.tsql2(k2,t2,n2)" + "(select k1,t1,n1 from tblspace1.tsql where n1=?)", Arrays.asList(1236), TransactionContext.AUTOTRANSACTION_TRANSACTION);
        assertEquals(1, executeUpdateInTransaction2.getUpdateCount());
        assertTrue(executeUpdateInTransaction2.transactionId > 0);
        manager.executeStatement(new CommitTransactionStatement("tblspace1", executeUpdateInTransaction2.transactionId), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        try (DataScanner scan = scan(manager, "SELECT k2,n2,t2 FROM tblspace1.tsql2 ORDER BY n2 desc", Collections.emptyList())) {
            assertEquals(3, scan.consume().size());
        }
        if (manager.getPlanner() instanceof SQLPlanner) {
            DMLStatementExecutionResult executeUpdateWithParameters = executeUpdate(manager, "INSERT INTO tblspace1.tsql2(k2,t2,n2)" + "(select ?,?,n1 from tblspace1.tsql where n1=?)", Arrays.asList("mykey5", tt3, 1236), TransactionContext.NO_TRANSACTION);
            assertEquals(1, executeUpdateWithParameters.getUpdateCount());
            assertTrue(executeUpdateWithParameters.transactionId == 0);
            try (DataScanner scan = scan(manager, "SELECT k2,n2,t2 " + "FROM tblspace1.tsql2 " + "WHERE t2 = ?", Arrays.asList(tt3))) {
                List<DataAccessor> all = scan.consume();
                assertEquals(1, all.size());
                assertEquals(Integer.valueOf(1236), all.get(0).get("n2"));
                assertEquals(tt3, all.get(0).get("t2"));
                assertEquals(RawString.of("mykey5"), all.get(0).get("k2"));
            }
        }
    }
}

Example 7 with SQLPlanner

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

the class RawSQLTest method selectWithParameters.

@Test
public void selectWithParameters() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        assumeTrue(manager.getPlanner() instanceof SQLPlanner);
        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(1234))).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT ? as foo, k1, n1 FROM tblspace1.tsql", Arrays.asList("test"))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
            assertEquals(RawString.of("test"), all.get(0).get("foo"));
            assertEquals(RawString.of("mykey"), all.get(0).get("k1"));
            assertEquals(Integer.valueOf(1234), all.get(0).get("n1"));
        }
        Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
        try (DataScanner scan = scan(manager, "SELECT ? as foo, ? as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
            assertEquals(Long.valueOf(1), all.get(0).get("foo"));
            assertEquals(timestamp, all.get(0).get("bar"));
        }
        try (DataScanner scan = scan(manager, "SELECT MAX(?) as foo, MIN(?) as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
            assertEquals(Long.valueOf(1), all.get(0).get("foo"));
            assertEquals(timestamp, all.get(0).get("bar"));
        }
        executeUpdate(manager, "DELETE FROM tblspace1.tsql", Collections.emptyList());
        try (DataScanner scan = scan(manager, "SELECT ? as foo, ? as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(0, all.size());
        }
        try (DataScanner scan = scan(manager, "SELECT MAX(?) as foo, MIN(?) as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
            assertNull(all.get(0).get("foo"));
            assertNull(all.get(0).get("bar"));
        }
    }
}

Example 8 with SQLPlanner

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

the class RawSQLTest method simpleCountTest.

@Test
public void simpleCountTest() 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());
        {
            try (DataScanner scan1 = scan(manager, "SELECT COUNT(*) as cc FROM tblspace1.tsql", Collections.emptyList())) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(1, result.size());
                assertEquals(Long.valueOf(4), result.get(0).get(0));
                assertEquals(Long.valueOf(4), result.get(0).get("cc"));
            }
        }
        if (manager.getPlanner() instanceof SQLPlanner) {
            try (DataScanner scan1 = scan(manager, "SELECT COUNT(*)  FROM tblspace1.tsql", Collections.emptyList())) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(1, result.size());
                assertEquals(Long.valueOf(4), result.get(0).get(0));
                assertEquals(Long.valueOf(4), result.get(0).get("count(*)"));
            }
        }
        {
            try (DataScanner scan1 = scan(manager, "SELECT COUNT(*) as cc FROM tblspace1.tsql WHERE k1='mykey3'", Collections.emptyList())) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(1, result.size());
                assertEquals(Long.valueOf(1), result.get(0).get(0));
                assertEquals(Long.valueOf(1), result.get(0).get("cc"));
            }
        }
        {
            try (DataScanner scan1 = scan(manager, "SELECT COUNT(*),k1 FROM tblspace1.tsql", Collections.emptyList())) {
                List<DataAccessor> result = scan1.consume();
                Assert.fail();
            } catch (StatementExecutionException error) {
                assertTrue("field k1 MUST appear in GROUP BY clause".equals(error.getMessage()) || error.getMessage().equals("From line 1, column 17 to line 1, column 18: Expression 'K1' is not being grouped"));
            }
        }
        {
            try (DataScanner scan1 = scan(manager, "SELECT COUNT(*) as cc,k1 FROM tblspace1.tsql GROUP BY k1", Collections.emptyList())) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(4, result.size());
                for (DataAccessor t : result) {
                    assertEquals(Long.valueOf(1), t.get("cc"));
                    switch(t.get("k1") + "") {
                        case "mykey":
                        case "mykey2":
                        case "mykey3":
                        case "mykey4":
                            break;
                        default:
                            fail();
                    }
                }
            }
        }
    }
}

Example 9 with SQLPlanner

use of herddb.sql.SQLPlanner 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));
        }
    }
}

Example 10 with SQLPlanner

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

the class MysqlCompatilityTest method test2.

@Test
public void test2() throws Exception {
    try (Server server = new Server(new ServerConfiguration(folder.newFolder().toPath()))) {
        server.start();
        server.waitForStandaloneBoot();
        assumeTrue(server.getManager().getPlanner() instanceof SQLPlanner);
        try (HDBClient client = new HDBClient(new ClientConfiguration(folder.newFolder().toPath()))) {
            client.setClientSideMetadataProvider(new StaticClientSideMetadataProvider(server));
            try (BasicHerdDBDataSource dataSource = new BasicHerdDBDataSource(client);
                Connection con = dataSource.getConnection();
                Connection con2 = dataSource.getConnection();
                Statement statement = con.createStatement()) {
                con.setAutoCommit(false);
                statement.execute("CREATE TABLE `queuebouncecategory_history` (\n" + "  `queueid` int(11) NOT NULL,\n" + "  `idbouncecategory` smallint(6) NOT NULL,\n" + "  `refdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n" + "  `messagecount` bigint(20) NOT NULL,\n" + "  PRIMARY KEY (`queueid`,`refdate`,`idbouncecategory`)\n" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
                statement.executeUpdate("INSERT INTO `queuebouncecategory_history` VALUES (1,3,'2015-03-29 01:00:00',1)");
                statement.executeUpdate("INSERT INTO `queuebouncecategory_history` VALUES (1,3,'2015-03-29 02:00:00',1)");
                statement.executeUpdate("INSERT INTO `queuebouncecategory_history` VALUES (1,3,'2015-03-29 03:00:00',1)");
            }
        }
    }
}