Search in sources :

Example 1 with ConditionIn

use of org.h2.expression.ConditionIn in project h2database by h2database.

the class ConditionAndOr method optimize.

@Override
public Expression optimize(Session session) {
    // NULL handling: see wikipedia,
    // http://www-cs-students.stanford.edu/~wlam/compsci/sqlnulls
    left = left.optimize(session);
    right = right.optimize(session);
    int lc = left.getCost(), rc = right.getCost();
    if (rc < lc) {
        Expression t = left;
        left = right;
        right = t;
    }
    // SELECT * FROM TEST WHERE NOT (B=A AND B=0 AND A=0); // 1, NULL
    if (session.getDatabase().getSettings().optimizeTwoEquals && andOrType == AND) {
        // try to add conditions (A=B AND B=1: add A=1)
        if (left instanceof Comparison && right instanceof Comparison) {
            Comparison compLeft = (Comparison) left;
            Comparison compRight = (Comparison) right;
            Expression added = compLeft.getAdditional(session, compRight, true);
            if (added != null) {
                added = added.optimize(session);
                return new ConditionAndOr(AND, this, added);
            }
        }
    }
    // (A=1 AND (B=2 OR B=3))
    if (andOrType == OR && session.getDatabase().getSettings().optimizeOr) {
        // try to add conditions (A=B AND B=1: add A=1)
        if (left instanceof Comparison && right instanceof Comparison) {
            Comparison compLeft = (Comparison) left;
            Comparison compRight = (Comparison) right;
            Expression added = compLeft.getAdditional(session, compRight, false);
            if (added != null) {
                return added.optimize(session);
            }
        } else if (left instanceof ConditionIn && right instanceof Comparison) {
            Expression added = ((ConditionIn) left).getAdditional((Comparison) right);
            if (added != null) {
                return added.optimize(session);
            }
        } else if (right instanceof ConditionIn && left instanceof Comparison) {
            Expression added = ((ConditionIn) right).getAdditional((Comparison) left);
            if (added != null) {
                return added.optimize(session);
            }
        } else if (left instanceof ConditionInConstantSet && right instanceof Comparison) {
            Expression added = ((ConditionInConstantSet) left).getAdditional(session, (Comparison) right);
            if (added != null) {
                return added.optimize(session);
            }
        } else if (right instanceof ConditionInConstantSet && left instanceof Comparison) {
            Expression added = ((ConditionInConstantSet) right).getAdditional(session, (Comparison) left);
            if (added != null) {
                return added.optimize(session);
            }
        }
    }
    // TODO optimization: convert .. OR .. to UNION if the cost is lower
    Value l = left.isConstant() ? left.getValue(session) : null;
    Value r = right.isConstant() ? right.getValue(session) : null;
    if (l == null && r == null) {
        return this;
    }
    if (l != null && r != null) {
        return ValueExpression.get(getValue(session));
    }
    switch(andOrType) {
        case AND:
            if (l != null) {
                if (l != ValueNull.INSTANCE && !l.getBoolean()) {
                    return ValueExpression.get(l);
                } else if (l.getBoolean()) {
                    return right;
                }
            } else if (r != null) {
                if (r != ValueNull.INSTANCE && !r.getBoolean()) {
                    return ValueExpression.get(r);
                } else if (r.getBoolean()) {
                    return left;
                }
            }
            break;
        case OR:
            if (l != null) {
                if (l.getBoolean()) {
                    return ValueExpression.get(l);
                } else if (l != ValueNull.INSTANCE) {
                    return right;
                }
            } else if (r != null) {
                if (r.getBoolean()) {
                    return ValueExpression.get(r);
                } else if (r != ValueNull.INSTANCE) {
                    return left;
                }
            }
            break;
        default:
            DbException.throwInternalError("type=" + andOrType);
    }
    return this;
}
Also used : Value(org.h2.value.Value)

Example 2 with ConditionIn

use of org.h2.expression.ConditionIn in project ignite by apache.

the class GridSqlQueryParser method parseExpression0.

/**
 * @param expression Expression.
 * @param calcTypes Calculate types for all the expressions.
 * @return Parsed expression.
 */
private GridSqlElement parseExpression0(Expression expression, boolean calcTypes) {
    if (expression instanceof ExpressionColumn) {
        ExpressionColumn expCol = (ExpressionColumn) expression;
        return new GridSqlColumn(expCol.getColumn(), parseTableFilter(expCol.getTableFilter()), SCHEMA_NAME.get(expCol), expCol.getOriginalTableAliasName(), expCol.getColumnName());
    }
    if (expression instanceof Alias)
        return new GridSqlAlias(expression.getAlias(), parseExpression(expression.getNonAliasExpression(), calcTypes), true);
    if (expression instanceof ValueExpression)
        // == comparison is legit, see ValueExpression#getSQL()
        return expression == ValueExpression.getDefault() ? GridSqlKeyword.DEFAULT : new GridSqlConst(expression.getValue(null));
    if (expression instanceof Operation) {
        Operation operation = (Operation) expression;
        Integer type = OPERATION_TYPE.get(operation);
        if (type == Operation.NEGATE) {
            assert OPERATION_RIGHT.get(operation) == null;
            return new GridSqlOperation(GridSqlOperationType.NEGATE, parseExpression(OPERATION_LEFT.get(operation), calcTypes));
        }
        return new GridSqlOperation(OPERATION_OP_TYPES[type], parseExpression(OPERATION_LEFT.get(operation), calcTypes), parseExpression(OPERATION_RIGHT.get(operation), calcTypes));
    }
    if (expression instanceof Comparison) {
        Comparison cmp = (Comparison) expression;
        GridSqlOperationType opType = COMPARISON_TYPES[COMPARISON_TYPE.get(cmp)];
        assert opType != null : COMPARISON_TYPE.get(cmp);
        Expression leftExp = COMPARISON_LEFT.get(cmp);
        GridSqlElement left = parseExpression(leftExp, calcTypes);
        if (opType.childrenCount() == 1)
            return new GridSqlOperation(opType, left);
        Expression rightExp = COMPARISON_RIGHT.get(cmp);
        GridSqlElement right = parseExpression(rightExp, calcTypes);
        return new GridSqlOperation(opType, left, right);
    }
    if (expression instanceof ConditionNot)
        return new GridSqlOperation(NOT, parseExpression(expression.getNotIfPossible(null), calcTypes));
    if (expression instanceof ConditionAndOr) {
        ConditionAndOr andOr = (ConditionAndOr) expression;
        int type = ANDOR_TYPE.get(andOr);
        assert type == ConditionAndOr.AND || type == ConditionAndOr.OR;
        return new GridSqlOperation(type == ConditionAndOr.AND ? AND : OR, parseExpression(ANDOR_LEFT.get(andOr), calcTypes), parseExpression(ANDOR_RIGHT.get(andOr), calcTypes));
    }
    if (expression instanceof Subquery) {
        Query qry = ((Subquery) expression).getQuery();
        return parseQueryExpression(qry);
    }
    if (expression instanceof ConditionIn) {
        GridSqlOperation res = new GridSqlOperation(IN);
        res.addChild(parseExpression(LEFT_CI.get((ConditionIn) expression), calcTypes));
        List<Expression> vals = VALUE_LIST_CI.get((ConditionIn) expression);
        for (Expression val : vals) res.addChild(parseExpression(val, calcTypes));
        return res;
    }
    if (expression instanceof ConditionInConstantSet) {
        GridSqlOperation res = new GridSqlOperation(IN);
        res.addChild(parseExpression(LEFT_CICS.get((ConditionInConstantSet) expression), calcTypes));
        List<Expression> vals = VALUE_LIST_CICS.get((ConditionInConstantSet) expression);
        for (Expression val : vals) res.addChild(parseExpression(val, calcTypes));
        return res;
    }
    if (expression instanceof ConditionInSelect) {
        GridSqlOperation res = new GridSqlOperation(IN);
        boolean all = ALL.get((ConditionInSelect) expression);
        int compareType = COMPARE_TYPE.get((ConditionInSelect) expression);
        assert0(!all, expression);
        assert0(compareType == Comparison.EQUAL, expression);
        res.addChild(parseExpression(LEFT_CIS.get((ConditionInSelect) expression), calcTypes));
        Query qry = QUERY_IN.get((ConditionInSelect) expression);
        res.addChild(parseQueryExpression(qry));
        return res;
    }
    if (expression instanceof CompareLike) {
        assert0(ESCAPE.get((CompareLike) expression) == null, expression);
        boolean regexp = REGEXP_CL.get((CompareLike) expression);
        return new GridSqlOperation(regexp ? REGEXP : LIKE, parseExpression(LEFT.get((CompareLike) expression), calcTypes), parseExpression(RIGHT.get((CompareLike) expression), calcTypes));
    }
    if (expression instanceof Function) {
        Function f = (Function) expression;
        GridSqlFunction res = new GridSqlFunction(null, f.getName());
        if (f.getArgs() != null) {
            if (f.getFunctionType() == Function.TABLE || f.getFunctionType() == Function.TABLE_DISTINCT) {
                Column[] cols = FUNC_TBL_COLS.get((TableFunction) f);
                Expression[] args = f.getArgs();
                assert cols.length == args.length;
                for (int i = 0; i < cols.length; i++) {
                    GridSqlElement arg = parseExpression(args[i], calcTypes);
                    GridSqlAlias alias = new GridSqlAlias(cols[i].getName(), arg, false);
                    alias.resultType(fromColumn(cols[i]));
                    res.addChild(alias);
                }
            } else {
                for (Expression arg : f.getArgs()) {
                    if (arg == null) {
                        if (f.getFunctionType() != Function.CASE)
                            throw new IllegalStateException("Function type with null arg: " + f.getFunctionType());
                        res.addChild(GridSqlPlaceholder.EMPTY);
                    } else
                        res.addChild(parseExpression(arg, calcTypes));
                }
            }
        }
        if (f.getFunctionType() == Function.CAST || f.getFunctionType() == Function.CONVERT)
            res.resultType(fromExpression(f));
        return res;
    }
    if (expression instanceof JavaFunction) {
        JavaFunction f = (JavaFunction) expression;
        FunctionAlias alias = FUNC_ALIAS.get(f);
        GridSqlFunction res = new GridSqlFunction(alias.getSchema().getName(), f.getName());
        if (f.getArgs() != null) {
            for (Expression arg : f.getArgs()) res.addChild(parseExpression(arg, calcTypes));
        }
        return res;
    }
    if (expression instanceof Parameter)
        return new GridSqlParameter(((Parameter) expression).getIndex());
    if (expression instanceof Aggregate) {
        int typeId = TYPE.get((Aggregate) expression);
        if (GridSqlAggregateFunction.isValidType(typeId)) {
            GridSqlAggregateFunction res = new GridSqlAggregateFunction(DISTINCT.get((Aggregate) expression), typeId);
            Expression on = ON.get((Aggregate) expression);
            if (on != null)
                res.addChild(parseExpression(on, calcTypes));
            return res;
        }
    }
    if (expression instanceof ExpressionList) {
        Expression[] exprs = EXPR_LIST.get((ExpressionList) expression);
        GridSqlArray res = new GridSqlArray(exprs.length);
        for (Expression expr : exprs) res.addChild(parseExpression(expr, calcTypes));
        return res;
    }
    if (expression instanceof ConditionExists) {
        Query qry = QUERY_EXISTS.get((ConditionExists) expression);
        GridSqlOperation res = new GridSqlOperation(EXISTS);
        res.addChild(parseQueryExpression(qry));
        return res;
    }
    throw new IgniteException("Unsupported expression: " + expression + " [type=" + expression.getClass().getSimpleName() + ']');
}
Also used : ConditionNot(org.h2.expression.ConditionNot) SqlFieldsQuery(org.apache.ignite.cache.query.SqlFieldsQuery) Query(org.h2.command.dml.Query) JavaFunction(org.h2.expression.JavaFunction) Operation(org.h2.expression.Operation) ConditionAndOr(org.h2.expression.ConditionAndOr) Subquery(org.h2.expression.Subquery) ExpressionColumn(org.h2.expression.ExpressionColumn) Function(org.h2.expression.Function) TableFunction(org.h2.expression.TableFunction) JavaFunction(org.h2.expression.JavaFunction) Comparison(org.h2.expression.Comparison) GridSqlType.fromColumn(org.apache.ignite.internal.processors.query.h2.sql.GridSqlType.fromColumn) AlterTableAlterColumn(org.h2.command.ddl.AlterTableAlterColumn) Column(org.h2.table.Column) ExpressionColumn(org.h2.expression.ExpressionColumn) IndexColumn(org.h2.table.IndexColumn) IgniteException(org.apache.ignite.IgniteException) ExpressionList(org.h2.expression.ExpressionList) FunctionAlias(org.h2.engine.FunctionAlias) ConditionIn(org.h2.expression.ConditionIn) AlterTableAddConstraint(org.h2.command.ddl.AlterTableAddConstraint) CompareLike(org.h2.expression.CompareLike) ConditionInConstantSet(org.h2.expression.ConditionInConstantSet) ConditionInSelect(org.h2.expression.ConditionInSelect) Expression(org.h2.expression.Expression) GridSqlType.fromExpression(org.apache.ignite.internal.processors.query.h2.sql.GridSqlType.fromExpression) ValueExpression(org.h2.expression.ValueExpression) FunctionAlias(org.h2.engine.FunctionAlias) Alias(org.h2.expression.Alias) ValueExpression(org.h2.expression.ValueExpression) Parameter(org.h2.expression.Parameter) Aggregate(org.h2.expression.Aggregate) ConditionExists(org.h2.expression.ConditionExists)

Example 3 with ConditionIn

use of org.h2.expression.ConditionIn in project h2database by h2database.

the class Comparison method getAdditional.

/**
 * Get an additional condition if possible. Example: given two conditions
 * A=B AND B=C, the new condition A=C is returned. Given the two conditions
 * A=1 OR A=2, the new condition A IN(1, 2) is returned.
 *
 * @param session the session
 * @param other the second condition
 * @param and true for AND, false for OR
 * @return null or the third condition
 */
Expression getAdditional(Session session, Comparison other, boolean and) {
    if (compareType == other.compareType && compareType == EQUAL) {
        boolean lc = left.isConstant();
        boolean rc = right.isConstant();
        boolean l2c = other.left.isConstant();
        boolean r2c = other.right.isConstant();
        String l = left.getSQL();
        String l2 = other.left.getSQL();
        String r = right.getSQL();
        String r2 = other.right.getSQL();
        if (and) {
            // must not compare constants. example: NOT(B=2 AND B=3)
            if (!(rc && r2c) && l.equals(l2)) {
                return new Comparison(session, EQUAL, right, other.right);
            } else if (!(rc && l2c) && l.equals(r2)) {
                return new Comparison(session, EQUAL, right, other.left);
            } else if (!(lc && r2c) && r.equals(l2)) {
                return new Comparison(session, EQUAL, left, other.right);
            } else if (!(lc && l2c) && r.equals(r2)) {
                return new Comparison(session, EQUAL, left, other.left);
            }
        } else {
            // a=b OR a=c
            Database db = session.getDatabase();
            if (rc && r2c && l.equals(l2)) {
                return new ConditionIn(db, left, new ArrayList<>(Arrays.asList(right, other.right)));
            } else if (rc && l2c && l.equals(r2)) {
                return new ConditionIn(db, left, new ArrayList<>(Arrays.asList(right, other.left)));
            } else if (lc && r2c && r.equals(l2)) {
                return new ConditionIn(db, right, new ArrayList<>(Arrays.asList(left, other.right)));
            } else if (lc && l2c && r.equals(r2)) {
                return new ConditionIn(db, right, new ArrayList<>(Arrays.asList(left, other.left)));
            }
        }
    }
    return null;
}
Also used : Database(org.h2.engine.Database) ArrayList(java.util.ArrayList)

Example 4 with ConditionIn

use of org.h2.expression.ConditionIn in project h2database by h2database.

the class Parser method readCondition.

private Expression readCondition() {
    if (readIf("NOT")) {
        return new ConditionNot(readCondition());
    }
    if (readIf("EXISTS")) {
        read("(");
        Query query = parseSelect();
        // can not reduce expression because it might be a union except
        // query with distinct
        read(")");
        return new ConditionExists(query);
    }
    if (readIf("INTERSECTS")) {
        read("(");
        Expression r1 = readConcat();
        read(",");
        Expression r2 = readConcat();
        read(")");
        return new Comparison(session, Comparison.SPATIAL_INTERSECTS, r1, r2);
    }
    Expression r = readConcat();
    while (true) {
        // special case: NOT NULL is not part of an expression (as in CREATE
        // TABLE TEST(ID INT DEFAULT 0 NOT NULL))
        int backup = parseIndex;
        boolean not = false;
        if (readIf("NOT")) {
            not = true;
            if (isToken("NULL")) {
                // this really only works for NOT NULL!
                parseIndex = backup;
                currentToken = "NOT";
                break;
            }
        }
        if (readIf("LIKE")) {
            Expression b = readConcat();
            Expression esc = null;
            if (readIf("ESCAPE")) {
                esc = readConcat();
            }
            recompileAlways = true;
            r = new CompareLike(database, r, b, esc, false);
        } else if (readIf("ILIKE")) {
            Function function = Function.getFunction(database, "CAST");
            function.setDataType(new Column("X", Value.STRING_IGNORECASE));
            function.setParameter(0, r);
            r = function;
            Expression b = readConcat();
            Expression esc = null;
            if (readIf("ESCAPE")) {
                esc = readConcat();
            }
            recompileAlways = true;
            r = new CompareLike(database, r, b, esc, false);
        } else if (readIf("REGEXP")) {
            Expression b = readConcat();
            recompileAlways = true;
            r = new CompareLike(database, r, b, null, true);
        } else if (readIf("IS")) {
            if (readIf("NOT")) {
                if (readIf("NULL")) {
                    r = new Comparison(session, Comparison.IS_NOT_NULL, r, null);
                } else if (readIf("DISTINCT")) {
                    read("FROM");
                    r = new Comparison(session, Comparison.EQUAL_NULL_SAFE, r, readConcat());
                } else {
                    r = new Comparison(session, Comparison.NOT_EQUAL_NULL_SAFE, r, readConcat());
                }
            } else if (readIf("NULL")) {
                r = new Comparison(session, Comparison.IS_NULL, r, null);
            } else if (readIf("DISTINCT")) {
                read("FROM");
                r = new Comparison(session, Comparison.NOT_EQUAL_NULL_SAFE, r, readConcat());
            } else {
                r = new Comparison(session, Comparison.EQUAL_NULL_SAFE, r, readConcat());
            }
        } else if (readIf("IN")) {
            read("(");
            if (readIf(")")) {
                if (database.getMode().prohibitEmptyInPredicate) {
                    throw getSyntaxError();
                }
                r = ValueExpression.get(ValueBoolean.FALSE);
            } else {
                if (isSelect()) {
                    Query query = parseSelect();
                    // can not be lazy because we have to call
                    // method ResultInterface.containsDistinct
                    // which is not supported for lazy execution
                    query.setNeverLazy(true);
                    r = new ConditionInSelect(database, r, query, false, Comparison.EQUAL);
                } else {
                    ArrayList<Expression> v = New.arrayList();
                    Expression last;
                    do {
                        last = readExpression();
                        v.add(last);
                    } while (readIf(","));
                    if (v.size() == 1 && (last instanceof Subquery)) {
                        Subquery s = (Subquery) last;
                        Query q = s.getQuery();
                        r = new ConditionInSelect(database, r, q, false, Comparison.EQUAL);
                    } else {
                        r = new ConditionIn(database, r, v);
                    }
                }
                read(")");
            }
        } else if (readIf("BETWEEN")) {
            Expression low = readConcat();
            read("AND");
            Expression high = readConcat();
            Expression condLow = new Comparison(session, Comparison.SMALLER_EQUAL, low, r);
            Expression condHigh = new Comparison(session, Comparison.BIGGER_EQUAL, high, r);
            r = new ConditionAndOr(ConditionAndOr.AND, condLow, condHigh);
        } else {
            int compareType = getCompareType(currentTokenType);
            if (compareType < 0) {
                break;
            }
            read();
            if (readIf("ALL")) {
                read("(");
                Query query = parseSelect();
                r = new ConditionInSelect(database, r, query, true, compareType);
                read(")");
            } else if (readIf("ANY") || readIf("SOME")) {
                read("(");
                if (currentTokenType == PARAMETER && compareType == 0) {
                    Parameter p = readParameter();
                    r = new ConditionInParameter(database, r, p);
                } else {
                    Query query = parseSelect();
                    r = new ConditionInSelect(database, r, query, false, compareType);
                }
                read(")");
            } else {
                Expression right = readConcat();
                if (SysProperties.OLD_STYLE_OUTER_JOIN && readIf("(") && readIf("+") && readIf(")")) {
                    // join with (+)
                    if (r instanceof ExpressionColumn && right instanceof ExpressionColumn) {
                        ExpressionColumn leftCol = (ExpressionColumn) r;
                        ExpressionColumn rightCol = (ExpressionColumn) right;
                        ArrayList<TableFilter> filters = currentSelect.getTopFilters();
                        for (TableFilter f : filters) {
                            while (f != null) {
                                leftCol.mapColumns(f, 0);
                                rightCol.mapColumns(f, 0);
                                f = f.getJoin();
                            }
                        }
                        TableFilter leftFilter = leftCol.getTableFilter();
                        TableFilter rightFilter = rightCol.getTableFilter();
                        r = new Comparison(session, compareType, r, right);
                        if (leftFilter != null && rightFilter != null) {
                            int idx = filters.indexOf(rightFilter);
                            if (idx >= 0) {
                                filters.remove(idx);
                                leftFilter.addJoin(rightFilter, true, r);
                            } else {
                                rightFilter.mapAndAddFilter(r);
                            }
                            r = ValueExpression.get(ValueBoolean.TRUE);
                        }
                    }
                } else {
                    r = new Comparison(session, compareType, r, right);
                }
            }
        }
        if (not) {
            r = new ConditionNot(r);
        }
    }
    return r;
}
Also used : ConditionNot(org.h2.expression.ConditionNot) Query(org.h2.command.dml.Query) Subquery(org.h2.expression.Subquery) ConditionIn(org.h2.expression.ConditionIn) ConditionAndOr(org.h2.expression.ConditionAndOr) AlterTableRenameConstraint(org.h2.command.ddl.AlterTableRenameConstraint) AlterTableAddConstraint(org.h2.command.ddl.AlterTableAddConstraint) AlterTableDropConstraint(org.h2.command.ddl.AlterTableDropConstraint) CompareLike(org.h2.expression.CompareLike) ExpressionColumn(org.h2.expression.ExpressionColumn) Function(org.h2.expression.Function) TableFunction(org.h2.expression.TableFunction) JavaFunction(org.h2.expression.JavaFunction) ConditionInParameter(org.h2.expression.ConditionInParameter) ConditionInSelect(org.h2.expression.ConditionInSelect) Expression(org.h2.expression.Expression) ValueExpression(org.h2.expression.ValueExpression) Comparison(org.h2.expression.Comparison) AlterTableRenameColumn(org.h2.command.ddl.AlterTableRenameColumn) AlterTableAlterColumn(org.h2.command.ddl.AlterTableAlterColumn) Column(org.h2.table.Column) ExpressionColumn(org.h2.expression.ExpressionColumn) IndexColumn(org.h2.table.IndexColumn) TableFilter(org.h2.table.TableFilter) Parameter(org.h2.expression.Parameter) ConditionInParameter(org.h2.expression.ConditionInParameter) ConditionExists(org.h2.expression.ConditionExists)

Aggregations

AlterTableAddConstraint (org.h2.command.ddl.AlterTableAddConstraint)2 AlterTableAlterColumn (org.h2.command.ddl.AlterTableAlterColumn)2 Query (org.h2.command.dml.Query)2 CompareLike (org.h2.expression.CompareLike)2 Comparison (org.h2.expression.Comparison)2 ConditionAndOr (org.h2.expression.ConditionAndOr)2 ConditionExists (org.h2.expression.ConditionExists)2 ConditionIn (org.h2.expression.ConditionIn)2 ConditionInSelect (org.h2.expression.ConditionInSelect)2 ConditionNot (org.h2.expression.ConditionNot)2 Expression (org.h2.expression.Expression)2 ExpressionColumn (org.h2.expression.ExpressionColumn)2 Function (org.h2.expression.Function)2 JavaFunction (org.h2.expression.JavaFunction)2 Parameter (org.h2.expression.Parameter)2 Subquery (org.h2.expression.Subquery)2 TableFunction (org.h2.expression.TableFunction)2 ValueExpression (org.h2.expression.ValueExpression)2 Column (org.h2.table.Column)2 IndexColumn (org.h2.table.IndexColumn)2