Examples with MemoryCommitLogManager herddb.mem.MemoryCommitLogManager used on opensource projects

Example 36 with MemoryCommitLogManager

use of herddb.mem.MemoryCommitLogManager in project herddb by diennea.

the class IndexScanRangeTest method secondaryIndexRangeScan.

private void secondaryIndexRangeScan(String indexType) 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);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(indexType).column("n1", ColumnTypes.INTEGER).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',3,5,'n2')", Collections.emptyList());
        // create index, it will be built using existing data
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1>=1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(5, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<=1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<=2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(4, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1>= 1 and n1<=2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(4, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<= 1 and n1>=2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(0, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1>1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(4, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<3", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(4, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(0, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1> 1 and n1<3", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(3, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1' and n1>=1 and n1<=2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(2, scan1.consume().size());
            }
        }
    }
}

Example 37 with MemoryCommitLogManager

use of herddb.mem.MemoryCommitLogManager in project herddb by diennea.

the class IndexScanRangeTest method noIndexOperation.

private void noIndexOperation(String indexType) 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);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(indexType).column("n1", ColumnTypes.INTEGER).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',3,5,'n2')", Collections.emptyList());
        // create index, it will be built using existing data
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<n2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertNull(scan.getPredicate().getIndexOperation());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(5, scan1.consume().size());
            }
        }
    }
}

Example 38 with MemoryCommitLogManager

use of herddb.mem.MemoryCommitLogManager in project herddb by diennea.

the class IndexScanRangeTest method secondaryIndexPrefixScan.

private void secondaryIndexPrefixScan(String indexType) 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);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(indexType).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',3,5,'n2')", Collections.emptyList());
        // create index, it will be built using existing data
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexPrefixScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(3, scan1.consume().size());
            }
        }
    }
}

Example 39 with MemoryCommitLogManager

use of herddb.mem.MemoryCommitLogManager in project herddb by diennea.

the class PrimaryIndexScanRangeTest method primaryIndexPrefixScanTest.

@Test
public void primaryIndexPrefixScanTest() 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);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("id", ColumnTypes.STRING).column("name", ColumnTypes.STRING).primaryKey("n1").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',3,6,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',4,7,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',5,5,'n2')", Collections.emptyList());
        performBasicPlannerTests(manager);
        assertFalse(manager.getTableSpaceManager("tblspace1").getTableManager("t1").isKeyToPageSortedAscending());
    }
}

Example 40 with MemoryCommitLogManager

use of herddb.mem.MemoryCommitLogManager in project herddb by diennea.

the class PrimaryIndexScanRangeTest method primaryIndexPrefixScanFileTest.

@Test
public void primaryIndexPrefixScanFileTest() throws Exception {
    Path dataPath = folder.newFolder("data").toPath();
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new FileDataStorageManager(dataPath), 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);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("id", ColumnTypes.STRING).column("name", ColumnTypes.STRING).primaryKey("n1").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',3,6,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',5,7,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',6,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('f',7,7,'n2')", Collections.emptyList());
        performBasicPlannerTests(manager);
        assertFalse(manager.getTableSpaceManager("tblspace1").getTableManager("t1").isKeyToPageSortedAscending());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=6 " + "order by n1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(3, tuples.size());
                assertEquals(2, tuples.get(0).get("n1"));
                assertEquals(3, tuples.get(1).get("n1"));
                assertEquals(6, tuples.get(2).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=6 " + "order by n1 " + "limit 2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(2, tuples.get(0).get("n1"));
                assertEquals(3, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=6 " + "limit 2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertNull(scan.getComparator());
            assertNotNull(scan.getLimits());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=6 " + "order by n1 " + "limit 1,3", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(3, tuples.get(0).get("n1"));
                assertEquals(6, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 1,2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(3, tuples.get(0).get("n1"));
                assertEquals(5, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 3,2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(6, tuples.get(0).get("n1"));
                assertEquals(7, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 4,2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(1, tuples.size());
                assertEquals(7, tuples.get(0).get("n1"));
            }
        }
        // add a record in the middle of the sorted recordset
        DMLStatementExecutionResult res = TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('g',4,6,'n2')", Collections.emptyList(), TransactionContext.AUTOTRANSACTION_TRANSACTION);
        long tx = res.transactionId;
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=6 " + "order by n1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(4, tuples.size());
                assertEquals(2, tuples.get(0).get("n1"));
                assertEquals(3, tuples.get(1).get("n1"));
                assertEquals(4, tuples.get(2).get("n1"));
                assertEquals(6, tuples.get(3).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(6, tuples.size());
                assertEquals(2, tuples.get(0).get("n1"));
                assertEquals(3, tuples.get(1).get("n1"));
                assertEquals(4, tuples.get(2).get("n1"));
                assertEquals(5, tuples.get(3).get("n1"));
                assertEquals(6, tuples.get(4).get("n1"));
                assertEquals(7, tuples.get(5).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=6 " + "order by n1 " + "limit 2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(2, tuples.get(0).get("n1"));
                assertEquals(3, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 1,3", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                for (DataAccessor tuple : tuples) {
                    System.out.println("tuple: " + tuple.toMap());
                }
                assertEquals(3, tuples.size());
                assertEquals(3, tuples.get(0).get("n1"));
                assertEquals(4, tuples.get(1).get("n1"));
                assertEquals(5, tuples.get(2).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=6 " + "order by n1 " + "limit 1,3", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(3, tuples.size());
                assertEquals(3, tuples.get(0).get("n1"));
                assertEquals(4, tuples.get(1).get("n1"));
                assertEquals(6, tuples.get(2).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 1,2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(3, tuples.get(0).get("n1"));
                assertEquals(4, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 3,2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(5, tuples.get(0).get("n1"));
                assertEquals(6, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 4,2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(2, tuples.size());
                assertEquals(6, tuples.get(0).get("n1"));
                assertEquals(7, tuples.get(1).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 5,2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(1, tuples.size());
                assertEquals(7, tuples.get(0).get("n1"));
            }
        }
        // add other records in the context of the transaction
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('g',8,6,'n3')", Collections.emptyList(), new TransactionContext(tx));
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('g',9,6,'n3')", Collections.emptyList(), new TransactionContext(tx));
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('g',10,6,'n3')", Collections.emptyList(), new TransactionContext(tx));
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 ", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(9, tuples.size());
                assertEquals(2, tuples.get(0).get("n1"));
                assertEquals(3, tuples.get(1).get("n1"));
                assertEquals(4, tuples.get(2).get("n1"));
                assertEquals(5, tuples.get(3).get("n1"));
                assertEquals(6, tuples.get(4).get("n1"));
                assertEquals(7, tuples.get(5).get("n1"));
                assertEquals(8, tuples.get(6).get("n1"));
                assertEquals(9, tuples.get(7).get("n1"));
                assertEquals(10, tuples.get(8).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2 " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and n2<=7 " + "order by n1 " + "limit 7", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                assertEquals(7, tuples.size());
                assertEquals(2, tuples.get(0).get("n1"));
                assertEquals(3, tuples.get(1).get("n1"));
                assertEquals(4, tuples.get(2).get("n1"));
                assertEquals(5, tuples.get(3).get("n1"));
                assertEquals(6, tuples.get(4).get("n1"));
                assertEquals(7, tuples.get(5).get("n1"));
                assertEquals(8, tuples.get(6).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2,name " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and name='n3' " + "order by n1 " + "limit 200", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                tuples.forEach(t -> {
                    System.out.println("OK sortedByClusteredIndex tuple " + t.toMap());
                });
                assertEquals(3, tuples.size());
                assertEquals(8, tuples.get(0).get("n1"));
                assertEquals(9, tuples.get(1).get("n1"));
                assertEquals(10, tuples.get(2).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2,name " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and name='n3' " + "order by n1 ", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                tuples.forEach(t -> {
                    System.out.println("OK sortedByClusteredIndex tuple " + t.toMap());
                });
                assertEquals(3, tuples.size());
                assertEquals(8, tuples.get(0).get("n1"));
                assertEquals(9, tuples.get(1).get("n1"));
                assertEquals(10, tuples.get(2).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2,name " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and name='n3' " + "order by n1 desc", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertFalse(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                tuples.forEach(t -> {
                    System.out.println("OK sortedByClusteredIndex tuple " + t.toMap());
                });
                assertEquals(3, tuples.size());
                assertEquals(10, tuples.get(0).get("n1"));
                assertEquals(9, tuples.get(1).get("n1"));
                assertEquals(8, tuples.get(2).get("n1"));
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT n1,n2,name " + "FROM tblspace1.t1 " + "WHERE n1>=2 " + "and name='n3' " + "order by n1 " + "limit 2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexRangeScan);
            assertTrue(scan.getComparator().isOnlyPrimaryKeyAndAscending());
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                List<DataAccessor> tuples = scan1.consume();
                tuples.forEach(t -> {
                    System.out.println("OK sortedByClusteredIndex tuple " + t.toMap());
                });
                assertEquals(2, tuples.size());
                assertEquals(8, tuples.get(0).get("n1"));
                assertEquals(9, tuples.get(1).get("n1"));
            }
        }
    }
}