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

Example 6 with SelectOrderBy

use of org.h2.command.dml.SelectOrderBy in project h2database by h2database.

the class SortOrder method getColumn.

/**
 * Get the column for the given table filter, if the sort column is for this
 * filter.
 *
 * @param index the column index (0, 1,..)
 * @param filter the table filter
 * @return the column, or null
 */
public Column getColumn(int index, TableFilter filter) {
    if (orderList == null) {
        return null;
    }
    SelectOrderBy order = orderList.get(index);
    Expression expr = order.expression;
    if (expr == null) {
        return null;
    }
    expr = expr.getNonAliasExpression();
    if (expr.isConstant()) {
        return null;
    }
    if (!(expr instanceof ExpressionColumn)) {
        return null;
    }
    ExpressionColumn exprCol = (ExpressionColumn) expr;
    if (exprCol.getTableFilter() != filter) {
        return null;
    }
    return exprCol.getColumn();
}

Example 7 with SelectOrderBy

use of org.h2.command.dml.SelectOrderBy in project h2database by h2database.

the class Query method prepareOrder.

/**
 * Create a {@link SortOrder} object given the list of {@link SelectOrderBy}
 * objects. The expression list is extended if necessary.
 *
 * @param orderList a list of {@link SelectOrderBy} elements
 * @param expressionCount the number of columns in the query
 * @return the {@link SortOrder} object
 */
public SortOrder prepareOrder(ArrayList<SelectOrderBy> orderList, int expressionCount) {
    int size = orderList.size();
    int[] index = new int[size];
    int[] sortType = new int[size];
    for (int i = 0; i < size; i++) {
        SelectOrderBy o = orderList.get(i);
        int idx;
        boolean reverse = false;
        Expression expr = o.columnIndexExpr;
        Value v = expr.getValue(null);
        if (v == ValueNull.INSTANCE) {
            // parameter not yet set - order by first column
            idx = 0;
        } else {
            idx = v.getInt();
            if (idx < 0) {
                reverse = true;
                idx = -idx;
            }
            idx -= 1;
            if (idx < 0 || idx >= expressionCount) {
                throw DbException.get(ErrorCode.ORDER_BY_NOT_IN_RESULT, "" + (idx + 1));
            }
        }
        index[i] = idx;
        boolean desc = o.descending;
        if (reverse) {
            desc = !desc;
        }
        int type = desc ? SortOrder.DESCENDING : SortOrder.ASCENDING;
        if (o.nullsFirst) {
            type += SortOrder.NULLS_FIRST;
        } else if (o.nullsLast) {
            type += SortOrder.NULLS_LAST;
        }
        sortType[i] = type;
    }
    return new SortOrder(session.getDatabase(), index, sortType, orderList);
}

Example 8 with SelectOrderBy

use of org.h2.command.dml.SelectOrderBy in project h2database by h2database.

the class Query method initOrder.

/**
 * Initialize the order by list. This call may extend the expressions list.
 *
 * @param session the session
 * @param expressions the select list expressions
 * @param expressionSQL the select list SQL snippets
 * @param orderList the order by list
 * @param visible the number of visible columns in the select list
 * @param mustBeInResult all order by expressions must be in the select list
 * @param filters the table filters
 */
static void initOrder(Session session, ArrayList<Expression> expressions, ArrayList<String> expressionSQL, ArrayList<SelectOrderBy> orderList, int visible, boolean mustBeInResult, ArrayList<TableFilter> filters) {
    Database db = session.getDatabase();
    for (SelectOrderBy o : orderList) {
        Expression e = o.expression;
        if (e == null) {
            continue;
        }
        // special case: SELECT 1 AS A FROM DUAL ORDER BY A
        // (oracle supports it, but only in order by, not in group by and
        // not in having):
        // SELECT 1 AS A FROM DUAL ORDER BY -A
        boolean isAlias = false;
        int idx = expressions.size();
        if (e instanceof ExpressionColumn) {
            // order by expression
            ExpressionColumn exprCol = (ExpressionColumn) e;
            String tableAlias = exprCol.getOriginalTableAliasName();
            String col = exprCol.getOriginalColumnName();
            for (int j = 0; j < visible; j++) {
                boolean found = false;
                Expression ec = expressions.get(j);
                if (ec instanceof ExpressionColumn) {
                    // select expression
                    ExpressionColumn c = (ExpressionColumn) ec;
                    found = db.equalsIdentifiers(col, c.getColumnName());
                    if (found && tableAlias != null) {
                        String ca = c.getOriginalTableAliasName();
                        if (ca == null) {
                            found = false;
                            if (filters != null) {
                                // select id from test order by test.id
                                for (TableFilter f : filters) {
                                    if (db.equalsIdentifiers(f.getTableAlias(), tableAlias)) {
                                        found = true;
                                        break;
                                    }
                                }
                            }
                        } else {
                            found = db.equalsIdentifiers(ca, tableAlias);
                        }
                    }
                } else if (!(ec instanceof Alias)) {
                    continue;
                } else if (tableAlias == null && db.equalsIdentifiers(col, ec.getAlias())) {
                    found = true;
                } else {
                    Expression ec2 = ec.getNonAliasExpression();
                    if (ec2 instanceof ExpressionColumn) {
                        ExpressionColumn c2 = (ExpressionColumn) ec2;
                        String ta = exprCol.getSQL();
                        String tb = c2.getSQL();
                        String s2 = c2.getColumnName();
                        found = db.equalsIdentifiers(col, s2);
                        if (!db.equalsIdentifiers(ta, tb)) {
                            found = false;
                        }
                    }
                }
                if (found) {
                    idx = j;
                    isAlias = true;
                    break;
                }
            }
        } else {
            String s = e.getSQL();
            if (expressionSQL != null) {
                for (int j = 0, size = expressionSQL.size(); j < size; j++) {
                    String s2 = expressionSQL.get(j);
                    if (db.equalsIdentifiers(s2, s)) {
                        idx = j;
                        isAlias = true;
                        break;
                    }
                }
            }
        }
        if (!isAlias) {
            if (mustBeInResult) {
                throw DbException.get(ErrorCode.ORDER_BY_NOT_IN_RESULT, e.getSQL());
            }
            expressions.add(e);
            String sql = e.getSQL();
            expressionSQL.add(sql);
        }
        o.columnIndexExpr = ValueExpression.get(ValueInt.get(idx + 1));
        o.expression = expressions.get(idx).getNonAliasExpression();
    }
}

Example 9 with SelectOrderBy

use of org.h2.command.dml.SelectOrderBy in project h2database by h2database.

the class Aggregate method getSQLGroupConcat.

private String getSQLGroupConcat() {
    StatementBuilder buff = new StatementBuilder("GROUP_CONCAT(");
    if (distinct) {
        buff.append("DISTINCT ");
    }
    buff.append(on.getSQL());
    if (orderByList != null) {
        buff.append(" ORDER BY ");
        for (SelectOrderBy o : orderByList) {
            buff.appendExceptFirst(", ");
            buff.append(o.expression.getSQL());
            if (o.descending) {
                buff.append(" DESC");
            }
        }
    }
    if (groupConcatSeparator != null) {
        buff.append(" SEPARATOR ").append(groupConcatSeparator.getSQL());
    }
    buff.append(')');
    if (filterCondition != null) {
        buff.append(" FILTER (WHERE ").append(filterCondition.getSQL()).append(')');
    }
    return buff.toString();
}

Example 10 with SelectOrderBy

use of org.h2.command.dml.SelectOrderBy in project h2database by h2database.

the class Aggregate method optimize.

@Override
public Expression optimize(Session session) {
    if (on != null) {
        on = on.optimize(session);
        dataType = on.getType();
        scale = on.getScale();
        precision = on.getPrecision();
        displaySize = on.getDisplaySize();
    }
    if (orderByList != null) {
        for (SelectOrderBy o : orderByList) {
            o.expression = o.expression.optimize(session);
        }
        orderBySort = initOrder(session);
    }
    if (groupConcatSeparator != null) {
        groupConcatSeparator = groupConcatSeparator.optimize(session);
    }
    if (filterCondition != null) {
        filterCondition = filterCondition.optimize(session);
    }
    switch(type) {
        case GROUP_CONCAT:
            dataType = Value.STRING;
            scale = 0;
            precision = displaySize = Integer.MAX_VALUE;
            break;
        case COUNT_ALL:
        case COUNT:
            dataType = Value.LONG;
            scale = 0;
            precision = ValueLong.PRECISION;
            displaySize = ValueLong.DISPLAY_SIZE;
            break;
        case SELECTIVITY:
            dataType = Value.INT;
            scale = 0;
            precision = ValueInt.PRECISION;
            displaySize = ValueInt.DISPLAY_SIZE;
            break;
        case HISTOGRAM:
            dataType = Value.ARRAY;
            scale = 0;
            precision = displaySize = Integer.MAX_VALUE;
            break;
        case SUM:
            if (dataType == Value.BOOLEAN) {
                // example: sum(id > 3) (count the rows)
                dataType = Value.LONG;
            } else if (!DataType.supportsAdd(dataType)) {
                throw DbException.get(ErrorCode.SUM_OR_AVG_ON_WRONG_DATATYPE_1, getSQL());
            } else {
                dataType = DataType.getAddProofType(dataType);
            }
            break;
        case AVG:
            if (!DataType.supportsAdd(dataType)) {
                throw DbException.get(ErrorCode.SUM_OR_AVG_ON_WRONG_DATATYPE_1, getSQL());
            }
            break;
        case MIN:
        case MAX:
        case MEDIAN:
            break;
        case STDDEV_POP:
        case STDDEV_SAMP:
        case VAR_POP:
        case VAR_SAMP:
            dataType = Value.DOUBLE;
            precision = ValueDouble.PRECISION;
            displaySize = ValueDouble.DISPLAY_SIZE;
            scale = 0;
            break;
        case BOOL_AND:
        case BOOL_OR:
            dataType = Value.BOOLEAN;
            precision = ValueBoolean.PRECISION;
            displaySize = ValueBoolean.DISPLAY_SIZE;
            scale = 0;
            break;
        case BIT_AND:
        case BIT_OR:
            if (!DataType.supportsAdd(dataType)) {
                throw DbException.get(ErrorCode.SUM_OR_AVG_ON_WRONG_DATATYPE_1, getSQL());
            }
            break;
        case ARRAY_AGG:
            dataType = Value.ARRAY;
            scale = 0;
            precision = displaySize = Integer.MAX_VALUE;
            break;
        default:
            DbException.throwInternalError("type=" + type);
    }
    return this;
}