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

Example 66 with TranslatedQuery

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

the class TableSpaceManager method apply.

void apply(CommitLogResult position, LogEntry entry, boolean recovery) throws DataStorageManagerException, DDLException {
    if (!position.deferred || position.sync) {
        // this will wait for the write to be acknowledged by the log
        // it can throw LogNotAvailableException
        this.actualLogSequenceNumber = position.getLogSequenceNumber();
        if (LOGGER.isLoggable(Level.FINEST)) {
            LOGGER.log(Level.FINEST, "apply {0} {1}", new Object[] { position.getLogSequenceNumber(), entry });
        }
    } else {
        if (LOGGER.isLoggable(Level.FINEST)) {
            LOGGER.log(Level.FINEST, "apply {0} {1}", new Object[] { position, entry });
        }
    }
    switch(entry.type) {
        case LogEntryType.NOOP:
            {
            // NOOP
            }
            break;
        case LogEntryType.BEGINTRANSACTION:
            {
                long id = entry.transactionId;
                Transaction transaction = new Transaction(id, tableSpaceName, position);
                transactions.put(id, transaction);
            }
            break;
        case LogEntryType.ROLLBACKTRANSACTION:
            {
                long id = entry.transactionId;
                Transaction transaction = transactions.get(id);
                if (transaction == null) {
                    throw new DataStorageManagerException("invalid transaction id " + id + ", only " + transactions.keySet());
                }
                List<AbstractIndexManager> indexManagers = new ArrayList<>(indexes.values());
                for (AbstractIndexManager indexManager : indexManagers) {
                    if (indexManager.getCreatedInTransaction() == 0 || indexManager.getCreatedInTransaction() == id) {
                        indexManager.onTransactionRollback(transaction);
                    }
                }
                List<AbstractTableManager> managers = new ArrayList<>(tables.values());
                for (AbstractTableManager manager : managers) {
                    if (manager.getCreatedInTransaction() == 0 || manager.getCreatedInTransaction() == id) {
                        Table table = manager.getTable();
                        if (transaction.isNewTable(table.name)) {
                            LOGGER.log(Level.INFO, "rollback CREATE TABLE " + table.tablespace + "." + table.name);
                            disposeTable(manager);
                            Map<String, AbstractIndexManager> indexes = indexesByTable.remove(manager.getTable().name);
                            if (indexes != null) {
                                for (AbstractIndexManager indexManager : indexes.values()) {
                                    disposeIndexManager(indexManager);
                                }
                            }
                        } else {
                            manager.onTransactionRollback(transaction);
                        }
                    }
                }
                transactions.remove(transaction.transactionId);
            }
            break;
        case LogEntryType.COMMITTRANSACTION:
            {
                long id = entry.transactionId;
                Transaction transaction = transactions.get(id);
                if (transaction == null) {
                    throw new DataStorageManagerException("invalid transaction id " + id);
                }
                LogSequenceNumber commit = position.getLogSequenceNumber();
                transaction.sync(commit);
                List<AbstractTableManager> managers = new ArrayList<>(tables.values());
                for (AbstractTableManager manager : managers) {
                    if (manager.getCreatedInTransaction() == 0 || manager.getCreatedInTransaction() == id) {
                        manager.onTransactionCommit(transaction, recovery);
                    }
                }
                List<AbstractIndexManager> indexManagers = new ArrayList<>(indexes.values());
                for (AbstractIndexManager indexManager : indexManagers) {
                    if (indexManager.getCreatedInTransaction() == 0 || indexManager.getCreatedInTransaction() == id) {
                        indexManager.onTransactionCommit(transaction, recovery);
                    }
                }
                if ((transaction.droppedTables != null && !transaction.droppedTables.isEmpty()) || (transaction.droppedIndexes != null && !transaction.droppedIndexes.isEmpty())) {
                    if (transaction.droppedTables != null) {
                        for (String dropped : transaction.droppedTables) {
                            for (AbstractTableManager manager : managers) {
                                if (manager.getTable().name.equals(dropped)) {
                                    disposeTable(manager);
                                }
                            }
                        }
                    }
                    if (transaction.droppedIndexes != null) {
                        for (String dropped : transaction.droppedIndexes) {
                            for (AbstractIndexManager manager : indexManagers) {
                                if (manager.getIndex().name.equals(dropped)) {
                                    disposeIndexManager(manager);
                                }
                            }
                        }
                    }
                }
                if ((transaction.newTables != null && !transaction.newTables.isEmpty()) || (transaction.droppedTables != null && !transaction.droppedTables.isEmpty()) || (transaction.newIndexes != null && !transaction.newIndexes.isEmpty()) || (transaction.droppedIndexes != null && !transaction.droppedIndexes.isEmpty())) {
                    writeTablesOnDataStorageManager(position, false);
                    dbmanager.getPlanner().clearCache();
                }
                transactions.remove(transaction.transactionId);
            }
            break;
        case LogEntryType.CREATE_TABLE:
            {
                Table table = Table.deserialize(entry.value.to_array());
                if (entry.transactionId > 0) {
                    long id = entry.transactionId;
                    Transaction transaction = transactions.get(id);
                    transaction.registerNewTable(table, position);
                }
                bootTable(table, entry.transactionId, null, true);
                if (entry.transactionId <= 0) {
                    writeTablesOnDataStorageManager(position, false);
                }
            }
            break;
        case LogEntryType.CREATE_INDEX:
            {
                Index index = Index.deserialize(entry.value.to_array());
                if (entry.transactionId > 0) {
                    long id = entry.transactionId;
                    Transaction transaction = transactions.get(id);
                    transaction.registerNewIndex(index, position);
                }
                AbstractTableManager tableManager = tables.get(index.table);
                if (tableManager == null) {
                    throw new RuntimeException("table " + index.table + " does not exists");
                }
                bootIndex(index, tableManager, true, entry.transactionId, true, false);
                if (entry.transactionId <= 0) {
                    writeTablesOnDataStorageManager(position, false);
                }
            }
            break;
        case LogEntryType.DROP_TABLE:
            {
                String tableName = entry.tableName;
                if (entry.transactionId > 0) {
                    long id = entry.transactionId;
                    Transaction transaction = transactions.get(id);
                    transaction.registerDropTable(tableName, position);
                } else {
                    AbstractTableManager manager = tables.get(tableName);
                    if (manager != null) {
                        disposeTable(manager);
                        Map<String, AbstractIndexManager> indexes = indexesByTable.get(tableName);
                        if (indexes != null && !indexes.isEmpty()) {
                            LOGGER.log(Level.SEVERE, "It looks like we are dropping a table " + tableName + " with these indexes " + indexes);
                        }
                    }
                }
                if (entry.transactionId <= 0) {
                    writeTablesOnDataStorageManager(position, false);
                }
            }
            break;
        case LogEntryType.DROP_INDEX:
            {
                String indexName = entry.value.to_string();
                if (entry.transactionId > 0) {
                    long id = entry.transactionId;
                    Transaction transaction = transactions.get(id);
                    transaction.registerDropIndex(indexName, position);
                } else {
                    AbstractIndexManager manager = indexes.get(indexName);
                    if (manager != null) {
                        disposeIndexManager(manager);
                    }
                }
                if (entry.transactionId <= 0) {
                    writeTablesOnDataStorageManager(position, false);
                    dbmanager.getPlanner().clearCache();
                }
            }
            break;
        case LogEntryType.ALTER_TABLE:
            {
                Table table = Table.deserialize(entry.value.to_array());
                alterTable(table, null);
                writeTablesOnDataStorageManager(position, false);
            }
            break;
        case LogEntryType.TABLE_CONSISTENCY_CHECK:
            {
                /*
                    In recovery mode, we need to skip the consistency check.
                    The tablespace may not be avaible yet and therefore calcite will not able to performed the select query.
                */
                if (recovery) {
                    LOGGER.log(Level.INFO, "skip {0} consistency check LogEntry {1}", new Object[] { tableSpaceName, entry });
                    break;
                }
                try {
                    TableChecksum check = MAPPER.readValue(entry.value.to_array(), TableChecksum.class);
                    String tableSpace = check.getTableSpaceName();
                    String query = check.getQuery();
                    String tableName = entry.tableName;
                    // In the entry type = 14, the follower will have to run the query on the transaction log
                    if (!isLeader()) {
                        AbstractTableManager tablemanager = this.getTableManager(tableName);
                        DBManager manager = this.getDbmanager();
                        if (tablemanager == null || tablemanager.getCreatedInTransaction() > 0) {
                            throw new TableDoesNotExistException(String.format("Table %s does not exist.", tablemanager));
                        }
                        /*
                            scan = true
                            allowCache = false
                            returnValues = false
                            maxRows = -1
                        */
                        TranslatedQuery translated = manager.getPlanner().translate(tableSpace, query, Collections.emptyList(), true, false, false, -1);
                        TableChecksum scanResult = TableDataChecksum.createChecksum(manager, translated, this, tableSpace, tableName);
                        long followerDigest = scanResult.getDigest();
                        long leaderDigest = check.getDigest();
                        long leaderNumRecords = check.getNumRecords();
                        long followerNumRecords = scanResult.getNumRecords();
                        // the necessary condition to pass the check is to have exactly the same digest and the number of records processed
                        if (followerDigest == leaderDigest && leaderNumRecords == followerNumRecords) {
                            LOGGER.log(Level.INFO, "Data consistency check PASS for table {0}  tablespace {1} with  Checksum {2}", new Object[] { tableName, tableSpace, followerDigest });
                        } else {
                            LOGGER.log(Level.SEVERE, "Data consistency check FAILED for table {0} in tablespace {1} with Checksum {2}", new Object[] { tableName, tableSpace, followerDigest });
                        }
                    } else {
                        long digest = check.getDigest();
                        LOGGER.log(Level.INFO, "Created checksum {0}  for table {1} in tablespace {2} on node {3}", new Object[] { digest, entry.tableName, tableSpace, this.getDbmanager().getNodeId() });
                    }
                } catch (IOException | DataScannerException ex) {
                    LOGGER.log(Level.SEVERE, "Error during table consistency check ", ex);
                }
            }
            break;
        default:
            // other entry types are not important for the tablespacemanager
            break;
    }
    if (entry.tableName != null && entry.type != LogEntryType.CREATE_TABLE && entry.type != LogEntryType.CREATE_INDEX && entry.type != LogEntryType.ALTER_TABLE && entry.type != LogEntryType.DROP_TABLE && entry.type != LogEntryType.TABLE_CONSISTENCY_CHECK) {
        AbstractTableManager tableManager = tables.get(entry.tableName);
        tableManager.apply(position, entry, recovery);
    }
}

Example 67 with TranslatedQuery

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

the class TableDataChecksum method createChecksum.

public static TableChecksum createChecksum(DBManager manager, TranslatedQuery query, TableSpaceManager tableSpaceManager, String tableSpace, String tableName) throws DataScannerException {
    AbstractTableManager tablemanager = tableSpaceManager.getTableManager(tableName);
    String nodeID = tableSpaceManager.getDbmanager().getNodeId();
    TranslatedQuery translated = query;
    final Table table = manager.getTableSpaceManager(tableSpace).getTableManager(tableName).getTable();
    // Number of records
    long nrecords = 0;
    // For example, in leader node we may not know the query
    if (translated == null) {
        String columns = formatColumns(table);
        /*
                scan = true
                allowCache = false
                returnValues = false
                maxRows = -1
            */
        translated = manager.getPlanner().translate(tableSpace, "SELECT  " + columns + " FROM " + tableName + " order by " + formatPrimaryKeys(table), Collections.emptyList(), true, false, false, -1);
    }
    ScanStatement statement = translated.plan.mainStatement.unwrap(ScanStatement.class);
    statement.setAllowExecutionFromFollower(true);
    LOGGER.log(Level.INFO, "creating checksum for table {0}.{1} on node {2}", new Object[] { tableSpace, tableName, nodeID });
    try (DataScanner scan = manager.scan(statement, translated.context, TransactionContext.NO_TRANSACTION)) {
        StreamingXXHash64 hash64 = FACTORY.newStreamingHash64(SEED);
        long _start = System.currentTimeMillis();
        while (scan.hasNext()) {
            nrecords++;
            DataAccessor data = scan.next();
            data.forEach((String key, Object value) -> {
                int type = table.getColumn(key).type;
                byte[] serialize = RecordSerializer.serialize(value, type);
                /*
                        Update need three parameters
                        update(byte[]buff, int off, int len)
                        buff is the input data
                        off is the start offset in buff
                        len is the number of bytes to hash
                     */
                if (serialize != null) {
                    hash64.update(serialize, 0, serialize.length);
                }
            });
        }
        LOGGER.log(Level.INFO, "Number of processed records for table {0}.{1} on node {2} = {3} ", new Object[] { tableSpace, tableName, nodeID, nrecords });
        long _stop = System.currentTimeMillis();
        long nextAutoIncrementValue = tablemanager.getNextPrimaryKeyValue();
        long scanduration = (_stop - _start);
        LOGGER.log(Level.INFO, "Creating checksum for table {0}.{1} on node {2} finished in {3} ms", new Object[] { tableSpace, tableName, nodeID, scanduration });
        SystemInstrumentation.instrumentationPoint("createChecksum", tableSpace, tableName);
        return new TableChecksum(tableSpace, tableName, hash64.getValue(), HASH_TYPE, nrecords, nextAutoIncrementValue, translated.context.query, scanduration);
    } catch (DataScannerException ex) {
        LOGGER.log(Level.SEVERE, "Scan failled", ex);
        throw new DataScannerException(ex);
    }
}

Example 68 with TranslatedQuery

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

the class ShowCreateTableCalculator method calculateShowCreateTable.

public static TranslatedQuery calculateShowCreateTable(String query, String defaultTablespace, List<Object> parameters, DBManager manager) {
    String[] items = { "SHOW", "CREATE", "TABLE" };
    if (Arrays.stream(items).allMatch(query::contains)) {
        query = query.substring(Arrays.stream(items).collect(Collectors.joining(" ")).length()).trim();
        String tableSpace = defaultTablespace;
        String tableName;
        boolean showCreateIndex = query.contains("WITH INDEXES");
        if (showCreateIndex) {
            query = query.substring(0, query.indexOf("WITH INDEXES"));
        }
        if (query.contains(".")) {
            String[] tokens = query.split("\\.");
            tableSpace = tokens[0].trim();
            tableName = tokens[1].trim();
        } else {
            tableName = query.trim();
        }
        tableName = tableName.toLowerCase();
        TableSpaceManager tableSpaceManager = manager.getTableSpaceManager(tableSpace);
        if (tableSpaceManager == null) {
            throw new TableSpaceDoesNotExistException(String.format("Tablespace %s does not exist.", tableSpace));
        }
        AbstractTableManager tableManager = tableSpaceManager.getTableManager(tableName);
        if (tableManager == null || tableManager.getCreatedInTransaction() > 0) {
            throw new TableDoesNotExistException(String.format("Table %s does not exist.", tableName));
        }
        String showCreateResult = calculate(showCreateIndex, tableName, tableSpace, tableManager);
        ValuesOp values = new ValuesOp(manager.getNodeId(), new String[] { "tabledef" }, new Column[] { column("tabledef", ColumnTypes.STRING) }, Arrays.asList(Arrays.asList(new ConstantExpression(showCreateResult, ColumnTypes.NOTNULL_STRING))));
        ExecutionPlan executionPlan = ExecutionPlan.simple(new SQLPlannedOperationStatement(values), values);
        return new TranslatedQuery(executionPlan, new SQLStatementEvaluationContext(query, parameters, false, false));
    } else {
        throw new StatementExecutionException(String.format("Incorrect Syntax for SHOW CREATE TABLE tablespace.tablename"));
    }
}

Example 69 with TranslatedQuery

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

the class SecondaryNonUniqueIndexAccessSuite method updateIndexOnDML.

@Test
public void updateIndexOnDML() 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("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("name", ColumnTypes.STRING).build();
        // create index, initially it will be empty
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('a','n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('b','n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('c','n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('d','n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('e','n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "DELETE FROM tblspace1.t1 WHERE id='c'", Collections.emptyList());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(2, scan1.consume().size());
            }
        }
        TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set name='n1' WHERE id='e'", Collections.emptyList());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(3, scan1.consume().size());
            }
        }
    }
}

Example 70 with TranslatedQuery

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

the class SecondaryNonUniqueIndexAccessSuite method secondaryIndexRangeScan.

@Test
public void secondaryIndexRangeScan() 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());
        // since 0.17.0 NULL values are allowed in single-column indexes
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('f',null,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 SecondaryIndexSeek);
            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 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>=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", 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 n1<n2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertFalse(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 is null", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            // no index here
            assertNull(scan.getPredicate().getIndexOperation());
            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 is not null", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            // no index here
            assertNull(scan.getPredicate().getIndexOperation());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(5, scan1.consume().size());
            }
        }
        // update NULL value, set a non null value
        TestUtils.executeUpdate(manager, "UPSERT INTO tblspace1.t1(id,n1,n2,name) values('f',4,5,'n2')", Collections.emptyList());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=4", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        // set NULL again
        TestUtils.executeUpdate(manager, "UPSERT INTO tblspace1.t1(id,n1,n2,name) values('f',null,5,'n2')", Collections.emptyList());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1 is null", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertNull(scan.getPredicate().getIndexOperation());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        // delete record that contains NULL
        TestUtils.executeUpdate(manager, "DELETE FROM tblspace1.t1 where n1 is null", Collections.emptyList());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1 is null", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertNull(scan.getPredicate().getIndexOperation());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(0, scan1.consume().size());
            }
        }
    }
}