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

Example 1 with SQLSelectGroupByClause

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

the class SQLSelectParser method parseGroupBy.

protected void parseGroupBy(SQLSelectQueryBlock queryBlock) {
    if (lexer.token() == (Token.GROUP)) {
        lexer.nextToken();
        accept(Token.BY);
        SQLSelectGroupByClause groupBy = new SQLSelectGroupByClause();
        for (; ; ) {
            SQLExpr item = parseGroupByItem();
            item.setParent(groupBy);
            groupBy.addItem(item);
            if (!(lexer.token() == (Token.COMMA))) {
                break;
            }
            lexer.nextToken();
        }
        if (lexer.token() == (Token.HAVING)) {
            lexer.nextToken();
            SQLExpr having = this.exprParser.expr();
            groupBy.setHaving(having);
        }
        if (lexer.token() == Token.WITH) {
            lexer.nextToken();
            if (identifierEquals("CUBE")) {
                lexer.nextToken();
                groupBy.setWithCube(true);
            } else {
                acceptIdentifier("ROLLUP");
                groupBy.setWithRollUp(true);
            }
        }
        queryBlock.setGroupBy(groupBy);
    } else if (lexer.token() == (Token.HAVING)) {
        lexer.nextToken();
        SQLSelectGroupByClause groupBy = new SQLSelectGroupByClause();
        groupBy.setHaving(this.exprParser.expr());
        if (lexer.token() == (Token.GROUP)) {
            lexer.nextToken();
            accept(Token.BY);
            for (; ; ) {
                SQLExpr item = parseGroupByItem();
                item.setParent(groupBy);
                groupBy.addItem(item);
                if (!(lexer.token() == (Token.COMMA))) {
                    break;
                }
                lexer.nextToken();
            }
        }
        if (lexer.token() == Token.WITH) {
            lexer.nextToken();
            acceptIdentifier("ROLLUP");
            groupBy.setWithRollUp(true);
        }
        if (JdbcConstants.MYSQL.equals(getDbType()) && lexer.token() == Token.DESC) {
            // skip
            lexer.nextToken();
        }
        queryBlock.setGroupBy(groupBy);
    }
}

Example 2 with SQLSelectGroupByClause

use of com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause in project Mycat_plus by coderczp.

the class DruidSelectParser method changeSql.

/**
 * 改写sql：需要加limit的加上
 */
@Override
public void changeSql(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt, LayerCachePool cachePool) throws SQLNonTransientException {
    tryRoute(schema, rrs, cachePool);
    rrs.copyLimitToNodes();
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        int limitStart = 0;
        int limitSize = schema.getDefaultMaxLimit();
        // clear group having
        SQLSelectGroupByClause groupByClause = mysqlSelectQuery.getGroupBy();
        // Modified by winbill, 20160614, do NOT include having clause when routing to multiple nodes
        if (groupByClause != null && groupByClause.getHaving() != null && isRoutMultiNode(schema, rrs)) {
            groupByClause.setHaving(null);
        }
        Map<String, Map<String, Set<ColumnRoutePair>>> allConditions = getAllConditions();
        boolean isNeedAddLimit = isNeedAddLimit(schema, rrs, mysqlSelectQuery, allConditions);
        if (isNeedAddLimit) {
            Limit limit = new Limit();
            limit.setRowCount(new SQLIntegerExpr(limitSize));
            mysqlSelectQuery.setLimit(limit);
            rrs.setLimitSize(limitSize);
            String sql = getSql(rrs, stmt, isNeedAddLimit);
            rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitSize, true);
        }
        Limit limit = mysqlSelectQuery.getLimit();
        if (limit != null && !isNeedAddLimit) {
            SQLIntegerExpr offset = (SQLIntegerExpr) limit.getOffset();
            SQLIntegerExpr count = (SQLIntegerExpr) limit.getRowCount();
            if (offset != null) {
                limitStart = offset.getNumber().intValue();
                rrs.setLimitStart(limitStart);
            }
            if (count != null) {
                limitSize = count.getNumber().intValue();
                rrs.setLimitSize(limitSize);
            }
            if (isNeedChangeLimit(rrs)) {
                Limit changedLimit = new Limit();
                changedLimit.setRowCount(new SQLIntegerExpr(limitStart + limitSize));
                if (offset != null) {
                    if (limitStart < 0) {
                        String msg = "You have an error in your SQL syntax; check the manual that " + "corresponds to your MySQL server version for the right syntax to use near '" + limitStart + "'";
                        throw new SQLNonTransientException(ErrorCode.ER_PARSE_ERROR + " - " + msg);
                    } else {
                        changedLimit.setOffset(new SQLIntegerExpr(0));
                    }
                }
                mysqlSelectQuery.setLimit(changedLimit);
                String sql = getSql(rrs, stmt, isNeedAddLimit);
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitStart + limitSize, true);
                // 设置改写后的sql
                ctx.setSql(sql);
            } else {
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), getCtx().getSql(), rrs.getLimitStart(), rrs.getLimitSize(), true);
            // ctx.setSql(nativeSql);
            }
        }
        if (rrs.isDistTable()) {
            SQLTableSource from = mysqlSelectQuery.getFrom();
            for (RouteResultsetNode node : rrs.getNodes()) {
                SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr();
                sqlIdentifierExpr.setParent(from);
                sqlIdentifierExpr.setName(node.getSubTableName());
                SQLExprTableSource from2 = new SQLExprTableSource(sqlIdentifierExpr);
                from2.setAlias(from.getAlias());
                mysqlSelectQuery.setFrom(from2);
                node.setStatement(stmt.toString());
            }
        }
        rrs.setCacheAble(isNeedCache(schema, rrs, mysqlSelectQuery, allConditions));
    }
}

Example 3 with SQLSelectGroupByClause

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

the class SQLQueryResultHandler method dohandler.

@Override
public String dohandler(SQLStatement statement, SQLSelect sqlselect, SQLObject parent, List param) {
    if (parent.getParent() instanceof SQLBinaryOpExpr) {
        SQLBinaryOpExpr pp = (SQLBinaryOpExpr) parent.getParent();
        SQLExprImpl listExpr = null;
        if (null == param || param.isEmpty()) {
            listExpr = new SQLNullExpr();
        } else {
            listExpr = new SQLListExpr();
            ((SQLListExpr) listExpr).getItems().addAll(param);
        }
        if (pp.getLeft().equals(parent)) {
            pp.setLeft(listExpr);
        } else if (pp.getRight().equals(parent)) {
            pp.setRight(listExpr);
        }
    } else if (parent.getParent() instanceof SQLSelectItem) {
        SQLSelectItem pp = (SQLSelectItem) parent.getParent();
        SQLExprImpl listExpr = null;
        if (null == param || param.isEmpty()) {
            listExpr = new SQLNullExpr();
        } else {
            listExpr = new SQLListExpr();
            ((SQLListExpr) listExpr).getItems().addAll(param);
        }
        pp.setExpr(listExpr);
    } else if (parent.getParent() instanceof SQLSelectGroupByClause) {
        SQLSelectGroupByClause pp = (SQLSelectGroupByClause) parent.getParent();
        List<SQLExpr> items = pp.getItems();
        for (int i = 0; i < items.size(); i++) {
            SQLExpr expr = items.get(i);
            if (expr instanceof SQLQueryExpr && ((SQLQueryExpr) expr).getSubQuery().equals(sqlselect)) {
                SQLExprImpl listExpr = null;
                if (null == param || param.isEmpty()) {
                    listExpr = new SQLNullExpr();
                } else {
                    listExpr = new SQLListExpr();
                    ((SQLListExpr) listExpr).getItems().addAll(param);
                }
                items.set(i, listExpr);
            }
        }
    } else if (parent.getParent() instanceof SQLSelectOrderByItem) {
        SQLSelectOrderByItem orderItem = (SQLSelectOrderByItem) parent.getParent();
        SQLExprImpl listExpr = null;
        if (null == param || param.isEmpty()) {
            listExpr = new SQLNullExpr();
        } else {
            listExpr = new SQLListExpr();
            ((SQLListExpr) listExpr).getItems().addAll(param);
        }
        listExpr.setParent(orderItem);
        orderItem.setExpr(listExpr);
    } else if (parent.getParent() instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) parent.getParent();
        // select * from subtest1 a where (select 1 from subtest3); 这种情况会进入到当前分支.
        // 改写为   select * from subtest1 a where (1); 或  select * from subtest1 a where (null);
        SQLExprImpl listExpr = null;
        if (null == param || param.isEmpty()) {
            listExpr = new SQLNullExpr();
        } else {
            listExpr = new SQLListExpr();
            ((SQLListExpr) listExpr).getItems().addAll(param);
        }
        listExpr.setParent(query);
        query.setWhere(listExpr);
    }
    return statement.toString();
}

Example 4 with SQLSelectGroupByClause

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

the class DruidSelectParser method parseAggGroupCommon.

protected Map<String, String> parseAggGroupCommon(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs, SQLSelectQueryBlock mysqlSelectQuery) {
    Map<String, String> aliaColumns = new HashMap<String, String>();
    Map<String, Integer> aggrColumns = new HashMap<String, Integer>();
    // Added by winbill, 20160314, for having clause, Begin ==>
    List<String> havingColsName = new ArrayList<String>();
    // Added by winbill, 20160314, for having clause, End  <==
    List<SQLSelectItem> selectList = mysqlSelectQuery.getSelectList();
    boolean isNeedChangeSql = false;
    int size = selectList.size();
    boolean isDistinct = mysqlSelectQuery.getDistionOption() == 2;
    for (int i = 0; i < size; i++) {
        SQLSelectItem item = selectList.get(i);
        if (item.getExpr() instanceof SQLAggregateExpr) {
            SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr();
            String method = expr.getMethodName();
            boolean isHasArgument = !expr.getArguments().isEmpty();
            if (isHasArgument) {
                // Added by winbill, 20160314, for having clause
                String aggrColName = method + "(" + expr.getArguments().get(0) + ")";
                // Added by winbill, 20160314, for having clause
                havingColsName.add(aggrColName);
            }
            // 只处理有别名的情况，无别名添加别名，否则某些数据库会得不到正确结果处理
            int mergeType = MergeCol.getMergeType(method);
            if (MergeCol.MERGE_AVG == mergeType && isRoutMultiNode(schema, rrs)) {
                // 跨分片avg需要特殊处理，直接avg结果是不对的
                String colName = item.getAlias() != null ? item.getAlias() : method + i;
                SQLSelectItem sum = new SQLSelectItem();
                String sumColName = colName + "SUM";
                sum.setAlias(sumColName);
                SQLAggregateExpr sumExp = new SQLAggregateExpr("SUM");
                ObjectUtil.copyProperties(expr, sumExp);
                sumExp.getArguments().addAll(expr.getArguments());
                sumExp.setMethodName("SUM");
                sum.setExpr(sumExp);
                selectList.set(i, sum);
                aggrColumns.put(sumColName, MergeCol.MERGE_SUM);
                // Added by winbill, 20160314, for having clause
                havingColsName.add(sumColName);
                // Added by winbill, 20160314, two aliases for AVG
                havingColsName.add(item.getAlias() != null ? item.getAlias() : "");
                SQLSelectItem count = new SQLSelectItem();
                String countColName = colName + "COUNT";
                count.setAlias(countColName);
                SQLAggregateExpr countExp = new SQLAggregateExpr("COUNT");
                ObjectUtil.copyProperties(expr, countExp);
                countExp.getArguments().addAll(expr.getArguments());
                countExp.setMethodName("COUNT");
                count.setExpr(countExp);
                selectList.add(count);
                aggrColumns.put(countColName, MergeCol.MERGE_COUNT);
                isNeedChangeSql = true;
                aggrColumns.put(colName, mergeType);
                rrs.setHasAggrColumn(true);
            } else if (MergeCol.MERGE_UNSUPPORT != mergeType) {
                String aggColName = null;
                StringBuilder sb = new StringBuilder();
                if (mysqlSelectQuery instanceof MySqlSelectQueryBlock) {
                    expr.accept(new MySqlOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof OracleSelectQueryBlock) {
                    expr.accept(new OracleOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof PGSelectQueryBlock) {
                    expr.accept(new PGOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof SQLServerSelectQueryBlock) {
                    expr.accept(new SQLASTOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof DB2SelectQueryBlock) {
                    expr.accept(new DB2OutputVisitor(sb));
                }
                aggColName = sb.toString();
                if (item.getAlias() != null && item.getAlias().length() > 0) {
                    aggrColumns.put(item.getAlias(), mergeType);
                    aliaColumns.put(aggColName, item.getAlias());
                } else {
                    // 如果不加，jdbc方式时取不到正确结果   ;修改添加别名
                    item.setAlias(method + i);
                    aggrColumns.put(method + i, mergeType);
                    aliaColumns.put(aggColName, method + i);
                    isNeedChangeSql = true;
                }
                rrs.setHasAggrColumn(true);
                // Added by winbill, 20160314, for having clause
                havingColsName.add(item.getAlias());
                // Added by winbill, 20160314, one alias for non-AVG
                havingColsName.add("");
            }
        } else {
            if (!(item.getExpr() instanceof SQLAllColumnExpr)) {
                String alia = item.getAlias();
                String field = getFieldName(item);
                if (alia == null) {
                    alia = field;
                }
                aliaColumns.put(field, alia);
            }
        }
    }
    if (aggrColumns.size() > 0) {
        rrs.setMergeCols(aggrColumns);
    }
    // 通过优化转换成group by来实现
    if (isDistinct) {
        mysqlSelectQuery.setDistionOption(0);
        SQLSelectGroupByClause groupBy = new SQLSelectGroupByClause();
        for (String fieldName : aliaColumns.keySet()) {
            groupBy.addItem(new SQLIdentifierExpr(fieldName));
        }
        mysqlSelectQuery.setGroupBy(groupBy);
        isNeedChangeSql = true;
    }
    // setGroupByCols
    if (mysqlSelectQuery.getGroupBy() != null) {
        List<SQLExpr> groupByItems = mysqlSelectQuery.getGroupBy().getItems();
        String[] groupByCols = buildGroupByCols(groupByItems, aliaColumns);
        rrs.setGroupByCols(groupByCols);
        rrs.setHavings(buildGroupByHaving(mysqlSelectQuery.getGroupBy().getHaving(), aliaColumns));
        rrs.setHasAggrColumn(true);
        // Added by winbill, 20160314, for having clause
        rrs.setHavingColsName(havingColsName.toArray());
    }
    if (isNeedChangeSql) {
        String sql = stmt.toString();
        rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, -1, false);
        getCtx().setSql(sql);
    }
    return aliaColumns;
}

Example 5 with SQLSelectGroupByClause

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

the class SQLASTParameterizedVisitor method visit.

@Override
public boolean visit(SQLIntegerExpr x) {
    SQLObject parent = x.getParent();
    if (parent instanceof SQLSelectGroupByClause || parent instanceof SQLSelectOrderByItem) {
        return false;
    }
    parameterizeAndExportPara(x);
    return false;
}