Search in sources :

Example 1 with PrimaryIndexRangeScan

use of herddb.index.PrimaryIndexRangeScan in project herddb by diennea.

the class SQLPlanner method discoverIndexOperations.

private void discoverIndexOperations(Expression expressionWhere, Table table, String mainTableAlias, SQLRecordPredicate where, TableSpaceManager tableSpaceManager) throws StatementExecutionException {
    SQLRecordKeyFunction keyFunction = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, EqualsTo.class);
    IndexOperation result = null;
    if (keyFunction != null) {
        if (keyFunction.isFullPrimaryKey()) {
            result = new PrimaryIndexSeek(keyFunction);
        } else {
            result = new PrimaryIndexPrefixScan(keyFunction);
        }
    } else {
        SQLRecordKeyFunction rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, GreaterThanEquals.class);
        if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
            rangeMin = null;
        }
        if (rangeMin == null) {
            rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, GreaterThan.class);
            if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
                rangeMin = null;
            }
        }
        SQLRecordKeyFunction rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, MinorThanEquals.class);
        if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
            rangeMax = null;
        }
        if (rangeMax == null) {
            rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, MinorThan.class);
            if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
                rangeMax = null;
            }
        }
        if (rangeMin != null || rangeMax != null) {
            result = new PrimaryIndexRangeScan(table.primaryKey, rangeMin, rangeMax);
        }
    }
    if (result == null) {
        Map<String, AbstractIndexManager> indexes = tableSpaceManager.getIndexesOnTable(table.name);
        if (indexes != null) {
            // TODO: use some kind of statistics, maybe using an index is more expensive than a full table scan
            for (AbstractIndexManager index : indexes.values()) {
                if (!index.isAvailable()) {
                    continue;
                }
                IndexOperation secondaryIndexOperation = findSecondaryIndexOperation(index, expressionWhere, table);
                if (secondaryIndexOperation != null) {
                    result = secondaryIndexOperation;
                    break;
                }
            }
        }
    }
    where.setIndexOperation(result);
    Expression filterPk = findFiltersOnPrimaryKey(table, table.name, expressionWhere);
    where.setPrimaryKeyFilter(filterPk);
}
Also used : MinorThan(net.sf.jsqlparser.expression.operators.relational.MinorThan) IndexOperation(herddb.index.IndexOperation) PrimaryIndexSeek(herddb.index.PrimaryIndexSeek) AbstractIndexManager(herddb.core.AbstractIndexManager) GreaterThan(net.sf.jsqlparser.expression.operators.relational.GreaterThan) Expression(net.sf.jsqlparser.expression.Expression) AlterExpression(net.sf.jsqlparser.statement.alter.AlterExpression) BinaryExpression(net.sf.jsqlparser.expression.BinaryExpression) AndExpression(net.sf.jsqlparser.expression.operators.conditional.AndExpression) SignedExpression(net.sf.jsqlparser.expression.SignedExpression) CompiledSQLExpression(herddb.sql.expressions.CompiledSQLExpression) PrimaryIndexPrefixScan(herddb.index.PrimaryIndexPrefixScan) PrimaryIndexRangeScan(herddb.index.PrimaryIndexRangeScan)

Example 2 with PrimaryIndexRangeScan

use of herddb.index.PrimaryIndexRangeScan in project herddb by diennea.

the class CalcitePlanner method scanForIndexAccess.

private IndexOperation scanForIndexAccess(CompiledSQLExpression expressionWhere, Table table, TableSpaceManager tableSpaceManager) {
    SQLRecordKeyFunction keyFunction = findIndexAccess(expressionWhere, table.primaryKey, table, "=", table);
    IndexOperation result = null;
    if (keyFunction != null) {
        if (keyFunction.isFullPrimaryKey()) {
            result = new PrimaryIndexSeek(keyFunction);
        } else {
            result = new PrimaryIndexPrefixScan(keyFunction);
        }
    } else {
        SQLRecordKeyFunction rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, ">=", table);
        if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
            rangeMin = null;
        }
        if (rangeMin == null) {
            rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, ">", table);
            if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
                rangeMin = null;
            }
        }
        SQLRecordKeyFunction rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, "<=", table);
        if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
            rangeMax = null;
        }
        if (rangeMax == null) {
            rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, "<", table);
            if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
                rangeMax = null;
            }
        }
        if (rangeMin != null || rangeMax != null) {
            result = new PrimaryIndexRangeScan(table.primaryKey, rangeMin, rangeMax);
        }
    }
    if (result == null) {
        Map<String, AbstractIndexManager> indexes = tableSpaceManager.getIndexesOnTable(table.name);
        if (indexes != null) {
            // TODO: use some kind of statistics, maybe using an index is more expensive than a full table scan
            for (AbstractIndexManager index : indexes.values()) {
                if (!index.isAvailable()) {
                    continue;
                }
                IndexOperation secondaryIndexOperation = findSecondaryIndexOperation(index, expressionWhere, table);
                if (secondaryIndexOperation != null) {
                    result = secondaryIndexOperation;
                    break;
                }
            }
        }
    }
    return result;
}
Also used : IndexOperation(herddb.index.IndexOperation) PrimaryIndexSeek(herddb.index.PrimaryIndexSeek) AbstractIndexManager(herddb.core.AbstractIndexManager) PrimaryIndexPrefixScan(herddb.index.PrimaryIndexPrefixScan) PrimaryIndexRangeScan(herddb.index.PrimaryIndexRangeScan)

Example 3 with PrimaryIndexRangeScan

use of herddb.index.PrimaryIndexRangeScan 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);
        assertTrue(manager.getTableSpaceManager("tblspace1").getTableManager("t1").getKeyToPageIndex().isSortedAscending());
        {
            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"));
            }
        }
        System.out.println("QUAAAAAAAA");
        {
            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"));
            }
        }
        System.out.println("QUIIIIII");
        {
            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"));
            }
        }
    }
}
Also used : Path(java.nio.file.Path) PrimaryIndexRangeScan(herddb.index.PrimaryIndexRangeScan) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DataScannerException(herddb.model.DataScannerException) Table(herddb.model.Table) CreateTableStatement(herddb.model.commands.CreateTableStatement) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) TransactionContext(herddb.model.TransactionContext) DMLStatementExecutionResult(herddb.model.DMLStatementExecutionResult) Path(java.nio.file.Path) DataAccessor(herddb.utils.DataAccessor) DataScanner(herddb.model.DataScanner) StatementExecutionException(herddb.model.StatementExecutionException) Assert.assertNotNull(org.junit.Assert.assertNotNull) Assert.assertTrue(org.junit.Assert.assertTrue) Test(org.junit.Test) ColumnTypes(herddb.model.ColumnTypes) ScanStatement(herddb.model.commands.ScanStatement) List(java.util.List) Assert.assertNull(org.junit.Assert.assertNull) Rule(org.junit.Rule) Assert.assertFalse(org.junit.Assert.assertFalse) FileDataStorageManager(herddb.file.FileDataStorageManager) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) SQLPlannedOperationStatement(herddb.model.commands.SQLPlannedOperationStatement) TranslatedQuery(herddb.sql.TranslatedQuery) StatementEvaluationContext(herddb.model.StatementEvaluationContext) PlannerOp(herddb.model.planner.PlannerOp) TableSpace(herddb.model.TableSpace) Collections(java.util.Collections) Assert.assertEquals(org.junit.Assert.assertEquals) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) TemporaryFolder(org.junit.rules.TemporaryFolder) Table(herddb.model.Table) TranslatedQuery(herddb.sql.TranslatedQuery) DataAccessor(herddb.utils.DataAccessor) CreateTableStatement(herddb.model.commands.CreateTableStatement) PrimaryIndexRangeScan(herddb.index.PrimaryIndexRangeScan) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DataScanner(herddb.model.DataScanner) DMLStatementExecutionResult(herddb.model.DMLStatementExecutionResult) TransactionContext(herddb.model.TransactionContext) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) FileDataStorageManager(herddb.file.FileDataStorageManager) List(java.util.List) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) ScanStatement(herddb.model.commands.ScanStatement) Test(org.junit.Test)

Example 4 with PrimaryIndexRangeScan

use of herddb.index.PrimaryIndexRangeScan in project herddb by diennea.

the class BLinkKeyToPageIndex method scanner.

@Override
public Stream<Entry<Bytes, Long>> scanner(IndexOperation operation, StatementEvaluationContext context, TableContext tableContext, AbstractIndexManager index) throws DataStorageManagerException, StatementExecutionException {
    if (operation instanceof PrimaryIndexSeek) {
        PrimaryIndexSeek seek = (PrimaryIndexSeek) operation;
        byte[] seekValue = seek.value.computeNewValue(null, context, tableContext);
        if (seekValue == null) {
            return Stream.empty();
        }
        Bytes key = Bytes.from_array(seekValue);
        Long pageId = getTree().search(key);
        if (pageId == null) {
            return Stream.empty();
        }
        return Stream.of(new AbstractMap.SimpleImmutableEntry<>(key, pageId));
    }
    if (operation instanceof PrimaryIndexPrefixScan) {
        PrimaryIndexPrefixScan scan = (PrimaryIndexPrefixScan) operation;
        // SQLRecordKeyFunction value = sis.value;
        byte[] refvalue = scan.value.computeNewValue(null, context, tableContext);
        Bytes firstKey = Bytes.from_array(refvalue);
        Bytes lastKey = firstKey.next();
        return getTree().scan(firstKey, lastKey);
    }
    // every predicate (WHEREs...) will always be evaluated anyway on every record, in order to guarantee correctness
    if (index != null) {
        return index.recordSetScanner(operation, context, tableContext, this);
    }
    if (operation == null) {
        Stream<Map.Entry<Bytes, Long>> baseStream = getTree().scan(null, null);
        return baseStream;
    } else if (operation instanceof PrimaryIndexRangeScan) {
        Bytes refminvalue;
        PrimaryIndexRangeScan sis = (PrimaryIndexRangeScan) operation;
        SQLRecordKeyFunction minKey = sis.minValue;
        if (minKey != null) {
            refminvalue = Bytes.from_array(minKey.computeNewValue(null, context, tableContext));
        } else {
            refminvalue = null;
        }
        Bytes refmaxvalue;
        SQLRecordKeyFunction maxKey = sis.maxValue;
        if (maxKey != null) {
            refmaxvalue = Bytes.from_array(maxKey.computeNewValue(null, context, tableContext));
        } else {
            refmaxvalue = null;
        }
        return getTree().scan(refminvalue, refmaxvalue, refmaxvalue != null);
    }
    throw new DataStorageManagerException("operation " + operation + " not implemented on " + this.getClass());
}
Also used : AbstractMap(java.util.AbstractMap) PrimaryIndexSeek(herddb.index.PrimaryIndexSeek) Bytes(herddb.utils.Bytes) Entry(java.util.Map.Entry) DataStorageManagerException(herddb.storage.DataStorageManagerException) AtomicLong(java.util.concurrent.atomic.AtomicLong) PrimaryIndexPrefixScan(herddb.index.PrimaryIndexPrefixScan) PrimaryIndexRangeScan(herddb.index.PrimaryIndexRangeScan) SQLRecordKeyFunction(herddb.sql.SQLRecordKeyFunction)

Aggregations

PrimaryIndexRangeScan (herddb.index.PrimaryIndexRangeScan)4 PrimaryIndexPrefixScan (herddb.index.PrimaryIndexPrefixScan)3 PrimaryIndexSeek (herddb.index.PrimaryIndexSeek)3 AbstractIndexManager (herddb.core.AbstractIndexManager)2 IndexOperation (herddb.index.IndexOperation)2 FileDataStorageManager (herddb.file.FileDataStorageManager)1 MemoryCommitLogManager (herddb.mem.MemoryCommitLogManager)1 MemoryDataStorageManager (herddb.mem.MemoryDataStorageManager)1 MemoryMetadataStorageManager (herddb.mem.MemoryMetadataStorageManager)1 ColumnTypes (herddb.model.ColumnTypes)1 DMLStatementExecutionResult (herddb.model.DMLStatementExecutionResult)1 DataScanner (herddb.model.DataScanner)1 DataScannerException (herddb.model.DataScannerException)1 StatementEvaluationContext (herddb.model.StatementEvaluationContext)1 StatementExecutionException (herddb.model.StatementExecutionException)1 Table (herddb.model.Table)1 TableSpace (herddb.model.TableSpace)1 TransactionContext (herddb.model.TransactionContext)1 CreateTableSpaceStatement (herddb.model.commands.CreateTableSpaceStatement)1 CreateTableStatement (herddb.model.commands.CreateTableStatement)1