Examples with Select org.h2.command.dml.Select used on opensource projects

Example 16 with Select

use of org.h2.command.dml.Select in project ignite by apache.

the class DmlAstUtils method selectForInsertOrMerge.

/**
     * Create SELECT on which subsequent INSERT or MERGE will be based.
     *
     * @param cols Columns to insert values into.
     * @param rows Rows to create pseudo-SELECT upon.
     * @param subQry Subquery to use rather than rows.
     * @param desc Row descriptor.
     * @return Subquery or pseudo-SELECT to evaluate inserted expressions.
     */
public static GridSqlQuery selectForInsertOrMerge(GridSqlColumn[] cols, List<GridSqlElement[]> rows, GridSqlQuery subQry, GridH2RowDescriptor desc) {
    if (!F.isEmpty(rows)) {
        assert !F.isEmpty(cols);
        GridSqlSelect sel = new GridSqlSelect();
        GridSqlFunction from = new GridSqlFunction(GridSqlFunctionType.TABLE);
        sel.from(from);
        GridSqlArray[] args = new GridSqlArray[cols.length];
        for (int i = 0; i < cols.length; i++) {
            GridSqlArray arr = new GridSqlArray(rows.size());
            String colName = cols[i].columnName();
            GridSqlAlias alias = new GridSqlAlias(colName, arr);
            alias.resultType(cols[i].resultType());
            from.addChild(alias);
            args[i] = arr;
            GridSqlColumn newCol = new GridSqlColumn(null, from, null, "TABLE", colName);
            newCol.resultType(cols[i].resultType());
            sel.addColumn(newCol, true);
        }
        for (GridSqlElement[] row : rows) {
            assert cols.length == row.length;
            for (int i = 0; i < row.length; i++) args[i].addChild(row[i]);
        }
        return sel;
    } else {
        assert subQry != null;
        return subQry;
    }
}

Example 17 with Select

use of org.h2.command.dml.Select in project ignite by apache.

the class GridSqlSelect method getSQL.

/** {@inheritDoc} */
@Override
public String getSQL() {
    StatementBuilder buff = new StatementBuilder(explain() ? "EXPLAIN SELECT" : "SELECT");
    if (distinct)
        buff.append(" DISTINCT");
    for (GridSqlAst expression : columns(true)) {
        buff.appendExceptFirst(",");
        buff.append('\n');
        buff.append(expression.getSQL());
    }
    if (from != null)
        buff.append("\nFROM ").append(from.getSQL());
    if (where != null)
        buff.append("\nWHERE ").append(StringUtils.unEnclose(where.getSQL()));
    if (grpCols != null) {
        buff.append("\nGROUP BY ");
        buff.resetCount();
        for (int grpCol : grpCols) {
            buff.appendExceptFirst(", ");
            addAlias(buff, cols.get(grpCol));
        }
    }
    if (havingCol >= 0) {
        buff.append("\nHAVING ");
        addAlias(buff, cols.get(havingCol));
    }
    getSortLimitSQL(buff);
    return buff.toString();
}

Example 18 with Select

use of org.h2.command.dml.Select in project ignite by apache.

the class GridH2TableSelfTest method testTable.

/**
     * Simple table test.
     *
     * @throws Exception If failed.
     */
public void testTable() throws Exception {
    // Test insert.
    long x = MAX_X;
    Random rnd = new Random();
    while (x-- > 0) {
        UUID id = UUID.randomUUID();
        GridH2Row row = row(id, System.currentTimeMillis(), rnd.nextBoolean() ? id.toString() : UUID.randomUUID().toString(), rnd.nextInt(100));
        tbl.doUpdate(row, false);
    }
    assertEquals(MAX_X, tbl.getRowCountApproximation());
    assertEquals(MAX_X, tbl.getRowCount(null));
    for (GridH2IndexBase idx : tbl.indexes()) {
        assertEquals(MAX_X, idx.getRowCountApproximation());
        assertEquals(MAX_X, idx.getRowCount(null));
    }
    // Check correct rows order.
    checkOrdered((GridH2TreeIndex) tbl.indexes().get(0), new Comparator<SearchRow>() {

        @Override
        public int compare(SearchRow o1, SearchRow o2) {
            UUID id1 = (UUID) o1.getValue(0).getObject();
            UUID id2 = (UUID) o2.getValue(0).getObject();
            return id1.compareTo(id2);
        }
    });
    checkOrdered((GridH2TreeIndex) tbl.indexes().get(1), new Comparator<SearchRow>() {

        @Override
        public int compare(SearchRow o1, SearchRow o2) {
            Long x1 = (Long) o1.getValue(3).getObject();
            Long x2 = (Long) o2.getValue(3).getObject();
            int c = x2.compareTo(x1);
            if (c != 0)
                return c;
            Timestamp t1 = (Timestamp) o1.getValue(1).getObject();
            Timestamp t2 = (Timestamp) o2.getValue(1).getObject();
            return t1.compareTo(t2);
        }
    });
    checkOrdered((GridH2TreeIndex) tbl.indexes().get(2), new Comparator<SearchRow>() {

        @Override
        public int compare(SearchRow o1, SearchRow o2) {
            String s1 = (String) o1.getValue(2).getObject();
            String s2 = (String) o2.getValue(2).getObject();
            return s2.compareTo(s1);
        }
    });
    // Indexes data consistency.
    ArrayList<? extends Index> idxs = tbl.indexes();
    checkIndexesConsistent((ArrayList<Index>) idxs, null);
    // Check unique index.
    UUID id = UUID.randomUUID();
    UUID id2 = UUID.randomUUID();
    assertTrue(tbl.doUpdate(row(id, System.currentTimeMillis(), id.toString(), rnd.nextInt(100)), false));
    assertTrue(tbl.doUpdate(row(id2, System.currentTimeMillis(), id2.toString(), rnd.nextInt(100)), false));
    // Check index selection.
    checkQueryPlan(conn, "SELECT * FROM T", SCAN_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE ID IS NULL", PK_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE ID = RANDOM_UUID()", PK_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE ID > RANDOM_UUID()", PK_NAME);
    checkQueryPlan(conn, "SELECT * FROM T ORDER BY ID", PK_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE STR IS NULL", STR_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE STR = 'aaaa'", STR_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE STR > 'aaaa'", STR_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T ORDER BY STR DESC", STR_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE X IS NULL", NON_UNIQUE_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE X = 10000", NON_UNIQUE_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T WHERE X > 10000", NON_UNIQUE_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T ORDER BY X DESC", NON_UNIQUE_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T ORDER BY X DESC, T", NON_UNIQUE_IDX_NAME);
    checkQueryPlan(conn, "SELECT * FROM T ORDER BY T, X DESC", SCAN_IDX_NAME);
    // Simple queries.
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("select id from t where x between 0 and 100");
    int i = 0;
    while (rs.next()) i++;
    assertEquals(MAX_X + 2, i);
    // -----
    rs = s.executeQuery("select id from t where t is not null");
    i = 0;
    while (rs.next()) i++;
    assertEquals(MAX_X + 2, i);
    // ----
    int cnt = 10 + rnd.nextInt(25);
    long t = System.currentTimeMillis();
    for (i = 0; i < cnt; i++) {
        id = UUID.randomUUID();
        assertTrue(tbl.doUpdate(row(id, t, id.toString(), 51), false));
    }
    rs = s.executeQuery("select x, id from t where x = 51 limit " + cnt);
    i = 0;
    while (rs.next()) {
        assertEquals(51, rs.getInt(1));
        i++;
    }
    assertEquals(cnt, i);
}

Example 19 with Select

use of org.h2.command.dml.Select in project ignite by apache.

the class GridH2TableSelfTest method checkQueryPlan.

/**
     * Check query plan to correctly select index.
     *
     * @param conn Connection.
     * @param sql Select.
     * @param search Search token in result.
     * @throws SQLException If failed.
     */
private void checkQueryPlan(Connection conn, String sql, String search) throws SQLException {
    try (Statement s = conn.createStatement()) {
        try (ResultSet r = s.executeQuery("EXPLAIN ANALYZE " + sql)) {
            assertTrue(r.next());
            String plan = r.getString(1);
            assertTrue("Execution plan for '" + sql + "' query should contain '" + search + "'", plan.contains(search));
        }
    }
}

Example 20 with Select

use of org.h2.command.dml.Select in project ignite by apache.

the class UpdatePlanBuilder method planForUpdate.

/**
     * Prepare update plan for UPDATE or DELETE.
     *
     * @param stmt UPDATE or DELETE statement.
     * @param errKeysPos index to inject param for re-run keys at. Null if it's not a re-run plan.
     * @return Update plan.
     * @throws IgniteCheckedException if failed.
     */
private static UpdatePlan planForUpdate(GridSqlStatement stmt, @Nullable Integer errKeysPos) throws IgniteCheckedException {
    GridSqlElement target;
    FastUpdateArguments fastUpdate;
    UpdateMode mode;
    if (stmt instanceof GridSqlUpdate) {
        // Let's verify that user is not trying to mess with key's columns directly
        verifyUpdateColumns(stmt);
        GridSqlUpdate update = (GridSqlUpdate) stmt;
        target = update.target();
        fastUpdate = DmlAstUtils.getFastUpdateArgs(update);
        mode = UpdateMode.UPDATE;
    } else if (stmt instanceof GridSqlDelete) {
        GridSqlDelete del = (GridSqlDelete) stmt;
        target = del.from();
        fastUpdate = DmlAstUtils.getFastDeleteArgs(del);
        mode = UpdateMode.DELETE;
    } else
        throw new IgniteSQLException("Unexpected DML operation [cls=" + stmt.getClass().getName() + ']', IgniteQueryErrorCode.UNEXPECTED_OPERATION);
    GridSqlTable tbl = gridTableForElement(target);
    GridH2Table gridTbl = tbl.dataTable();
    GridH2RowDescriptor desc = gridTbl.rowDescriptor();
    if (desc == null)
        throw new IgniteSQLException("Row descriptor undefined for table '" + gridTbl.getName() + "'", IgniteQueryErrorCode.NULL_TABLE_DESCRIPTOR);
    if (fastUpdate != null)
        return UpdatePlan.forFastUpdate(mode, gridTbl, fastUpdate);
    else {
        GridSqlSelect sel;
        if (stmt instanceof GridSqlUpdate) {
            List<GridSqlColumn> updatedCols = ((GridSqlUpdate) stmt).cols();
            int valColIdx = -1;
            String[] colNames = new String[updatedCols.size()];
            int[] colTypes = new int[updatedCols.size()];
            for (int i = 0; i < updatedCols.size(); i++) {
                colNames[i] = updatedCols.get(i).columnName();
                colTypes[i] = updatedCols.get(i).resultType().type();
                Column column = updatedCols.get(i).column();
                if (desc.isValueColumn(column.getColumnId()))
                    valColIdx = i;
            }
            boolean hasNewVal = (valColIdx != -1);
            // Statement updates distinct properties if it does not have _val in updated columns list
            // or if its list of updated columns includes only _val, i.e. is single element.
            boolean hasProps = !hasNewVal || updatedCols.size() > 1;
            // Index of new _val in results of SELECT
            if (hasNewVal)
                valColIdx += 2;
            int newValColIdx = (hasNewVal ? valColIdx : 1);
            KeyValueSupplier newValSupplier = createSupplier(desc.context(), desc.type(), newValColIdx, hasProps, false, true);
            sel = DmlAstUtils.selectForUpdate((GridSqlUpdate) stmt, errKeysPos);
            return UpdatePlan.forUpdate(gridTbl, colNames, colTypes, newValSupplier, valColIdx, sel.getSQL());
        } else {
            sel = DmlAstUtils.selectForDelete((GridSqlDelete) stmt, errKeysPos);
            return UpdatePlan.forDelete(gridTbl, sel.getSQL());
        }
    }
}