Examples with SQLSelectQueryBlock com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock used on opensource projects

Example 61 with SQLSelectQueryBlock

use of com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock in project druid by alibaba.

the class MySqlSelectTest_233 method test_0.

public void test_0() throws Exception {
    String sql = "select f1 \n" + "from t1\n" + "union all\n" + "select f2\n" + "from t2";
    SQLSelectStatement stmt = (SQLSelectStatement) SQLUtils.parseSingleMysqlStatement(sql);
    SQLUnionQuery query = (SQLUnionQuery) stmt.getSelect().getQuery();
    SQLSelectQueryBlock left = (SQLSelectQueryBlock) query.getLeft();
    left.addOrderBy(new SQLIdentifierExpr("f1"));
    SQLSelectQueryBlock right = (SQLSelectQueryBlock) query.getRight();
    right.addOrderBy(new SQLIdentifierExpr("f1"));
    assertEquals("SELECT f1\n" + "FROM t1\n" + "ORDER BY f1\n" + "UNION ALL\n" + "(SELECT f2\n" + "FROM t2\n" + "ORDER BY f1)", stmt.toString());
}

Example 62 with SQLSelectQueryBlock

use of com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock in project druid by alibaba.

the class LargeOrTest method test_largeAnd.

public void test_largeAnd() throws Exception {
    StringBuffer buf = new StringBuffer();
    buf.append("SELECT 1 FROM T WHERE ID = ?");
    for (int i = 0; i < 10000; ++i) {
        buf.append(" AND ID = ?");
    }
    String sql = buf.toString();
    OracleStatementParser parser = new OracleStatementParser(sql, SQLParserFeature.EnableSQLBinaryOpExprGroup);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    SQLSelectQueryBlock select = (SQLSelectQueryBlock) stmt.getSelect().getQuery();
    SQLBinaryOpExprGroup where = (SQLBinaryOpExprGroup) select.getWhere();
    SQLBinaryOpExpr last = (SQLBinaryOpExpr) where.getItems().get(0);
    Assert.assertEquals(SQLBinaryOperator.Equality, last.getOperator());
}

Example 63 with SQLSelectQueryBlock

use of com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock in project Mycat-Server by MyCATApache.

the class MycatSchemaStatVisitor method visit.

/* 
     *  遇到 any 将子查询改写成  SELECT MIN(name) FROM subtest1
     *  例如:
     *    select * from subtest where id oper any (select name from subtest1);
     *    >/>= any ----> >/>= min
     *    </<= any ----> </<= max
     *    <>   any ----> not in
     *    =    some ----> in
     *    other  不改写
     */
@Override
public boolean visit(SQLAnyExpr x) {
    setSubQueryRelationOrFlag(x);
    List<SQLSelectItem> itemlist = ((SQLSelectQueryBlock) (x.getSubQuery().getQuery())).getSelectList();
    SQLExpr sexpr = itemlist.get(0).getExpr();
    if (x.getParent() instanceof SQLBinaryOpExpr) {
        SQLBinaryOpExpr parentExpr = (SQLBinaryOpExpr) x.getParent();
        SQLAggregateExpr saexpr = null;
        switch(parentExpr.getOperator()) {
            case GreaterThan:
            case GreaterThanOrEqual:
            case NotLessThan:
                this.hasChange = true;
                if (sexpr instanceof SQLIdentifierExpr || (sexpr instanceof SQLPropertyExpr && ((SQLPropertyExpr) sexpr).getOwner() instanceof SQLIdentifierExpr)) {
                    saexpr = new SQLAggregateExpr("MIN");
                    saexpr.getArguments().add(sexpr);
                    saexpr.setParent(itemlist.get(0));
                    itemlist.get(0).setExpr(saexpr);
                }
                SQLQueryExpr maxSubQuery = new SQLQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(maxSubQuery);
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    if (((SQLBinaryOpExpr) x.getParent()).getLeft().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setLeft(maxSubQuery);
                    } else if (((SQLBinaryOpExpr) x.getParent()).getRight().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setRight(maxSubQuery);
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(x.getSubQuery());
            case LessThan:
            case LessThanOrEqual:
            case NotGreaterThan:
                this.hasChange = true;
                if (sexpr instanceof SQLIdentifierExpr || (sexpr instanceof SQLPropertyExpr && ((SQLPropertyExpr) sexpr).getOwner() instanceof SQLIdentifierExpr)) {
                    saexpr = new SQLAggregateExpr("MAX");
                    saexpr.getArguments().add(sexpr);
                    saexpr.setParent(itemlist.get(0));
                    itemlist.get(0).setExpr(saexpr);
                }
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                SQLQueryExpr minSubQuery = new SQLQueryExpr(x.getSubQuery());
                x.subQuery.setParent(minSubQuery);
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    if (((SQLBinaryOpExpr) x.getParent()).getLeft().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setLeft(minSubQuery);
                    } else if (((SQLBinaryOpExpr) x.getParent()).getRight().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setRight(minSubQuery);
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(x.getSubQuery());
            case LessThanOrGreater:
            case NotEqual:
                this.hasChange = true;
                SQLInSubQueryExpr notInSubQueryExpr = new SQLInSubQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(notInSubQueryExpr);
                notInSubQueryExpr.setNot(true);
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr xp = (SQLBinaryOpExpr) x.getParent();
                    if (xp.getLeft().equals(x)) {
                        notInSubQueryExpr.setExpr(xp.getRight());
                    } else if (xp.getRight().equals(x)) {
                        notInSubQueryExpr.setExpr(xp.getLeft());
                    }
                    if (xp.getParent() instanceof MySqlSelectQueryBlock) {
                        ((MySqlSelectQueryBlock) xp.getParent()).setWhere(notInSubQueryExpr);
                    } else if (xp.getParent() instanceof SQLBinaryOpExpr) {
                        SQLBinaryOpExpr pp = ((SQLBinaryOpExpr) xp.getParent());
                        if (pp.getLeft().equals(xp)) {
                            pp.setLeft(notInSubQueryExpr);
                        } else if (pp.getRight().equals(xp)) {
                            pp.setRight(notInSubQueryExpr);
                        }
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(notInSubQueryExpr);
            case Equality:
                this.hasChange = true;
                SQLInSubQueryExpr inSubQueryExpr = new SQLInSubQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(inSubQueryExpr);
                inSubQueryExpr.setNot(false);
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr xp = (SQLBinaryOpExpr) x.getParent();
                    if (xp.getLeft().equals(x)) {
                        inSubQueryExpr.setExpr(xp.getRight());
                    } else if (xp.getRight().equals(x)) {
                        inSubQueryExpr.setExpr(xp.getLeft());
                    }
                    if (xp.getParent() instanceof MySqlSelectQueryBlock) {
                        ((MySqlSelectQueryBlock) xp.getParent()).setWhere(inSubQueryExpr);
                    } else if (xp.getParent() instanceof SQLBinaryOpExpr) {
                        SQLBinaryOpExpr pp = ((SQLBinaryOpExpr) xp.getParent());
                        if (pp.getLeft().equals(xp)) {
                            pp.setLeft(inSubQueryExpr);
                        } else if (pp.getRight().equals(xp)) {
                            pp.setRight(inSubQueryExpr);
                        }
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(inSubQueryExpr);
            default:
                break;
        }
    }
    addSubQuerys(x.getSubQuery());
    return super.visit(x);
}