com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock Java Examples

The following examples show how to use com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: TestMySQLItemVisitor.java    From dble with GNU General Public License v2.0 6 votes vote down vote up
@Test
public void testGroupbyOrder() {
    MySqlSelectQueryBlock query = getQuery("select col1,col2 from table1 group by col1 desc,col2 asc ");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    int i = 0;
    for (SQLExpr p : groupBy.getItems()) {
        i++;
        String groupCol = "col" + i;
        MySqlOrderingExpr groupitem = (MySqlOrderingExpr) p;
        SQLExpr q = groupitem.getExpr();
        MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
        q.accept(v);
        Item item = v.getItem();
        Assert.assertEquals(true, groupCol.equals(item.getItemName()));
    }
}
 
Example #2
Source File: SqlParser.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
public JoinSelect parseJoinSelect(SQLQueryExpr sqlExpr) throws SqlParseException {

        MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) sqlExpr.getSubQuery().getQuery();

        List<From> joinedFrom = findJoinedFrom(query.getFrom());
        if (joinedFrom.size() != 2)
            throw new RuntimeException("currently supports only 2 tables join");

        JoinSelect joinSelect = createBasicJoinSelectAccordingToTableSource((SQLJoinTableSource) query.getFrom());
        List<Hint> hints = parseHints(query.getHints());
        joinSelect.setHints(hints);
        String firstTableAlias = joinedFrom.get(0).getAlias();
        String secondTableAlias = joinedFrom.get(1).getAlias();
        Map<String, Where> aliasToWhere = splitAndFindWhere(query.getWhere(), firstTableAlias, secondTableAlias);
        Map<String, List<SQLSelectOrderByItem>> aliasToOrderBy = splitAndFindOrder(query.getOrderBy(), firstTableAlias, secondTableAlias);
        List<Condition> connectedConditions = getConditionsFlatten(joinSelect.getConnectedWhere());
        joinSelect.setConnectedConditions(connectedConditions);
        fillTableSelectedJoin(joinSelect.getFirstTable(), query, joinedFrom.get(0), aliasToWhere.get(firstTableAlias), aliasToOrderBy.get(firstTableAlias), connectedConditions);
        fillTableSelectedJoin(joinSelect.getSecondTable(), query, joinedFrom.get(1), aliasToWhere.get(secondTableAlias), aliasToOrderBy.get(secondTableAlias), connectedConditions);

        updateJoinLimit(query.getLimit(), joinSelect);

        //todo: throw error feature not supported:  no group bys on joins ?
        return joinSelect;
    }
 
Example #3
Source File: DruidSelectParser.java    From Mycat2 with GNU General Public License v3.0 6 votes vote down vote up
@Override
	public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) {
		SQLSelectStatement selectStmt = (SQLSelectStatement)stmt;
		SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
		if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
			MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)selectStmt.getSelect().getQuery();

			parseOrderAggGroupMysql(schema, stmt,rrs, mysqlSelectQuery);
			//更改canRunInReadDB属性
			if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false)
			{
				rrs.setCanRunInReadDB(false);
			}

		} else if (sqlSelectQuery instanceof SQLUnionQuery) {
//			MySqlUnionQuery unionQuery = (MySqlUnionQuery)sqlSelectQuery;
//			MySqlSelectQueryBlock left = (MySqlSelectQueryBlock)unionQuery.getLeft();
//			MySqlSelectQueryBlock right = (MySqlSelectQueryBlock)unionQuery.getLeft();
//			System.out.println();
		}
	}
 
Example #4
Source File: DruidSelectParser.java    From Mycat2 with GNU General Public License v3.0 6 votes vote down vote up
protected void parseOrderAggGroupMysql(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs, MySqlSelectQueryBlock mysqlSelectQuery)
	{
		MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
		stmt.accept(visitor);
//		rrs.setGroupByCols((String[])visitor.getGroupByColumns().toArray());
		if(!isNeedParseOrderAgg)
		{
			return;
		}
		Map<String, String> aliaColumns = parseAggGroupCommon(schema, stmt, rrs, mysqlSelectQuery);

		//setOrderByCols
		if(mysqlSelectQuery.getOrderBy() != null) {
			List<SQLSelectOrderByItem> orderByItems = mysqlSelectQuery.getOrderBy().getItems();
			rrs.setOrderByCols(buildOrderByCols(orderByItems,aliaColumns));
		}
		isNeedParseOrderAgg=false;
	}
 
Example #5
Source File: DruidSelectParser.java    From Mycat2 with GNU General Public License v3.0 6 votes vote down vote up
/**
 * 现阶段目标为 有一个只涉及到一张表的子查询时,先执行子查询,获得返回结果后,改写原有sql继续执行,得到最终结果.
 * 在这种情况下,原sql不需要继续解析.
 * 使用catlet 的情况也不再继续解析.
 */
@Override
public boolean afterVisitorParser(RouteResultset rrs, SQLStatement stmt, MycatSchemaStatVisitor visitor) {
	int subQuerySize = visitor.getSubQuerys().size();

	if(subQuerySize==0&&ctx.getTables().size()==2){ //两表关联,考虑使用catlet
		if(ctx.getVisitor().getConditions() !=null && ctx.getVisitor().getConditions().size()>0){
			return true;
		}
	}else if(subQuerySize==1){     //只涉及一张表的子查询,使用  MiddlerResultHandler 获取中间结果后,改写原有 sql 继续执行 TODO 后期可能会考虑多个.
		SQLSelectQuery sqlSelectQuery = visitor.getSubQuerys().iterator().next().getQuery();
		if(((MySqlSelectQueryBlock)sqlSelectQuery).getFrom() instanceof SQLExprTableSource) {
			return true;
		}
	}

	return super.afterVisitorParser(rrs, stmt, visitor);
}
 
Example #6
Source File: MySQLSelectASTVisitor.java    From Zebra with Apache License 2.0 6 votes vote down vote up
@Override
public boolean visit(MySqlSelectQueryBlock.Limit x) {
	if (x.getOffset() instanceof SQLIntegerExpr) {
		SQLIntegerExpr offsetExpr = (SQLIntegerExpr) x.getOffset();
		if (offsetExpr != null) {
			int offset = offsetExpr.getNumber().intValue();
			result.getMergeContext().setOffset(offset);
		}
	}

	if (x.getRowCount() instanceof SQLIntegerExpr) {
		SQLIntegerExpr rowCountExpr = (SQLIntegerExpr) x.getRowCount();
		if (rowCountExpr != null) {
			int limit = rowCountExpr.getNumber().intValue();
			result.getMergeContext().setLimit(limit);
		}
	}

	result.getMergeContext().setLimitExpr(x);
	return true;
}
 
Example #7
Source File: DruidSelectParser.java    From Mycat2 with GNU General Public License v3.0 6 votes vote down vote up
private boolean isConditionAlwaysTrue(SQLStatement statement) {
	SQLSelectStatement selectStmt = (SQLSelectStatement)statement;
	SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
	if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
		MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)selectStmt.getSelect().getQuery();
		SQLExpr expr = mysqlSelectQuery.getWhere();

		Object o = WallVisitorUtils.getValue(expr);
		if(Boolean.TRUE.equals(o)) {
			return true;
		}
		return false;
	} else {//union
		return false;
	}

}
 
Example #8
Source File: DruidSelectParser.java    From dble with GNU General Public License v2.0 6 votes vote down vote up
private void tryAddLimit(SchemaConfig schema, TableConfig tableConfig,
                         MySqlSelectQueryBlock mysqlSelectQuery, RouteResultset rrs) {
    if (schema.getDefaultMaxLimit() == -1) {
        return;
    } else if (mysqlSelectQuery.getLimit() != null) {
        return;
    } else if (!tableConfig.isNeedAddLimit()) {
        return;
    } else if (mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) {
        return;
    } else if (rrs.isContainsPrimaryFilter()) {
        // single table and has primary key , need not limit because of only one row
        return;
    }
    SQLLimit limit = new SQLLimit();
    limit.setRowCount(new SQLIntegerExpr(schema.getDefaultMaxLimit()));
    mysqlSelectQuery.setLimit(limit);
}
 
Example #9
Source File: SelectHandler.java    From dble with GNU General Public License v2.0 6 votes vote down vote up
private static boolean isSupportSelect(String stmt) {
    SQLStatementParser parser = new MySqlStatementParser(stmt);
    SQLStatement statement = parser.parseStatement();
    if (!(statement instanceof SQLSelectStatement)) {
        return false;
    }

    SQLSelectQuery sqlSelectQuery = ((SQLSelectStatement) statement).getSelect().getQuery();
    if (!(sqlSelectQuery instanceof MySqlSelectQueryBlock)) {
        return false;
    }
    MySqlSelectQueryBlock selectQueryBlock = (MySqlSelectQueryBlock) sqlSelectQuery;
    SQLTableSource mysqlFrom = selectQueryBlock.getFrom();
    if (mysqlFrom != null) {
        return false;
    }
    for (SQLSelectItem item : selectQueryBlock.getSelectList()) {
        SQLExpr selectItem = item.getExpr();
        if (!isVariantRef(selectItem)) {
            return false;
        }
    }
    return true;
}
 
Example #10
Source File: SchemaUtil.java    From dble with GNU General Public License v2.0 6 votes vote down vote up
public static boolean isNoSharding(ServerConnection source, SQLSelectQuery sqlSelectQuery, SQLStatement selectStmt, SQLStatement childSelectStmt, String contextSchema, Set<String> schemas, StringPtr dataNode)
        throws SQLException {
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mySqlSelectQueryBlock = (MySqlSelectQueryBlock) sqlSelectQuery;
        //CHECK IF THE SELECT LIST HAS INNER_FUNC IN,WITCH SHOULD BE DEAL BY DBLE
        for (SQLSelectItem item : mySqlSelectQueryBlock.getSelectList()) {
            if (item.getExpr() instanceof SQLMethodInvokeExpr) {
                if (ItemCreate.getInstance().isInnerFunc(((SQLMethodInvokeExpr) item.getExpr()).getMethodName())) {
                    return false;
                }
            }
        }
        return isNoSharding(source, mySqlSelectQueryBlock.getFrom(), selectStmt, childSelectStmt, contextSchema, schemas, dataNode);
    } else if (sqlSelectQuery instanceof SQLUnionQuery) {
        return isNoSharding(source, (SQLUnionQuery) sqlSelectQuery, selectStmt, contextSchema, schemas, dataNode);
    } else {
        return false;
    }
}
 
Example #11
Source File: TestMySQLItemVisitor.java    From dble with GNU General Public License v2.0 5 votes vote down vote up
@Test
public void testWhere() {
    MySqlSelectQueryBlock query = getQuery("select col1,col2  from table1 where a =1 ");
    SQLExpr expr = query.getWhere();

    MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset,null, null);
    expr.accept(v);
    Item item = v.getItem();
    Assert.assertEquals(true, "a = 1".equals(item.getItemName()));
}
 
Example #12
Source File: ShareJoin.java    From Mycat2 with GNU General Public License v3.0 5 votes vote down vote up
public void route(SystemConfig sysConfig, SchemaConfig schema,int sqlType, String realSQL, String charset, ServerConnection sc,	LayerCachePool cachePool) {
	int rs = ServerParse.parse(realSQL);
	this.sqltype = rs & 0xff;
	this.sysConfig=sysConfig; 
	this.schema=schema;
	this.charset=charset; 
	this.sc=sc;	
	this.cachePool=cachePool;		
	try {
	 //  RouteStrategy routes=RouteStrategyFactory.getRouteStrategy();	
	  // rrs =RouteStrategyFactory.getRouteStrategy().route(sysConfig, schema, sqlType2, realSQL,charset, sc, cachePool);		   
		MySqlStatementParser parser = new MySqlStatementParser(realSQL);			
		SQLStatement statement = parser.parseStatement();
		if(statement instanceof SQLSelectStatement) {
		   SQLSelectStatement st=(SQLSelectStatement)statement;
		   SQLSelectQuery sqlSelectQuery =st.getSelect().getQuery();
			if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
				MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)sqlSelectQuery;
				joinParser=new JoinParser(mysqlSelectQuery,realSQL);
				joinParser.parser();
			}	
		}
	   /*	
	   if (routes instanceof DruidMysqlRouteStrategy) {
		   SQLSelectStatement st=((DruidMysqlRouteStrategy) routes).getSQLStatement();
		   SQLSelectQuery sqlSelectQuery =st.getSelect().getQuery();
			if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
				MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)st.getSelect().getQuery();
				joinParser=new JoinParser(mysqlSelectQuery,realSQL);
				joinParser.parser();
			}
	   }
	   */
	} catch (Exception e) {
	
	}
}
 
Example #13
Source File: TestMySQLItemVisitor.java    From dble with GNU General Public License v2.0 5 votes vote down vote up
@Test
public void testJoinCondition() {
    MySqlSelectQueryBlock query = getQuery("select a.col1,b.col2  from table1 a inner join table2 b on a.id =b.id");
    SQLJoinTableSource from = (SQLJoinTableSource) query.getFrom();

    MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset,null, null);
    from.getCondition().accept(v);
    Item item = v.getItem();
    Assert.assertEquals(true, "a.id = b.id".equals(item.getItemName()));
}
 
Example #14
Source File: TestMySQLItemVisitor.java    From dble with GNU General Public License v2.0 5 votes vote down vote up
@Test
public void testSelectItem() {
    MySqlSelectQueryBlock query = getQuery("select sum(col1) from table1 where a >1 ");
    List<SQLSelectItem> items = query.getSelectList();

    MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset,null, null);
    items.get(0).accept(v);
    Item item = v.getItem();
    Assert.assertEquals(true, "SUM(col1)".equals(item.getItemName()));
}
 
Example #15
Source File: MySqlSelectParser.java    From baymax with Apache License 2.0 5 votes vote down vote up
protected void parseStatement(ParseResult result, ExecutePlan plan, SQLSelectStatement statement){
     // 单库单表
     if (plan.getSqlList().size() <= 1){
         return;
     }

     SQLSelectQuery sqlSelectQuery = statement.getSelect().getQuery();
     if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
         // mysql查询
         parseMysqlQueary(result, plan, (MySqlSelectQueryBlock) sqlSelectQuery);
     } else if (sqlSelectQuery instanceof MySqlUnionQuery) {
         throw new BayMaxException("Union暂不支持发送到多库多表上执行,只能在单库单表执行!");
         // TODO 测试
/*
MySqlUnionQuery unionQuery = (MySqlUnionQuery)sqlSelectQuery;
         SQLSelectQuery left = unionQuery.getLeft();
         SQLSelectQuery right = unionQuery.getLeft();
         if (left instanceof MySqlSelectQueryBlock){
             parseMysqlQueary(result, plan, (MySqlSelectQueryBlock) left);
         }
         if (right instanceof MySqlSelectQueryBlock){
             parseMysqlQueary(result, plan, (MySqlSelectQueryBlock) right);
         }
         */
         //if (left.getFrom().getAlias().equalsIgnoreCase(plan.getSqlList().get(0).getLogicTableName())){

         //}
     }
 }
 
Example #16
Source File: MySqlSelectParser.java    From baymax with Apache License 2.0 5 votes vote down vote up
protected void parseMysqlQueary(ParseResult result, ExecutePlan plan, MySqlSelectQueryBlock mysqlSelectQuery){
    // 解析聚合函数
    parseAggregate(result, plan, mysqlSelectQuery);
    // 解析groupby
    parseGroupBy(result, plan, mysqlSelectQuery);
    // 解析orderby
    parseOrderby(result, plan, mysqlSelectQuery);
    // 解析limit
    parseLimit(result, plan, mysqlSelectQuery);

}
 
Example #17
Source File: SqlParser.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
private void findSelect(MySqlSelectQueryBlock query, Select select, String tableAlias) throws SqlParseException {
    List<SQLSelectItem> selectList = query.getSelectList();
    for (SQLSelectItem sqlSelectItem : selectList) {
        Field field = FieldMaker.makeField(sqlSelectItem.getExpr(), sqlSelectItem.getAlias(), tableAlias);
        select.addField(field);
    }
}
 
Example #18
Source File: MySqlSelectParser.java    From baymax with Apache License 2.0 5 votes vote down vote up
/**
 * 解析Orderby
 * @param result
 * @param plan
 * @param mysqlSelectQuery
 */
protected void parseOrderby(ParseResult result, ExecutePlan plan, MySqlSelectQueryBlock mysqlSelectQuery){
    if (mysqlSelectQuery.getOrderBy() == null){
        return;
    }
    List<SQLSelectOrderByItem> orderByItems = mysqlSelectQuery.getOrderBy().getItems();
    if (orderByItems == null || orderByItems.size() == 0){
        return;
    }
    List<SQLSelectItem> selectList      = mysqlSelectQuery.getSelectList();
    List<OrderbyColumn> orderbyColumns  = new ArrayList<OrderbyColumn>(orderByItems.size());
    for(SQLSelectOrderByItem item : orderByItems){
        String name = StringUtil.removeDot(item.getExpr().toString());
        if (result.getAliaColumns() != null){
            String alias = result.getAliaColumns().get(name);
            if (alias != null){
                // select user_id as uid ....order by user_id
                // 要把oderby的user_id转换为uid,以便结果集合并
                orderbyColumns.add(new OrderbyColumn(alias, OrderbyColumn.buildOrderbyType(item.getType())));
                continue;
            }
        }
        if (!result.isHasAllColumnExpr()){
            // select列表中没有orderby的字段 添加,用于后面做合并
            SQLIdentifierExpr exp = new SQLIdentifierExpr(name);

            // item
            SQLSelectItem addItem = new SQLSelectItem();
            addItem.setExpr(exp);
            exp.setParent(item);
            selectList.add(addItem);
        }
        orderbyColumns.add(new OrderbyColumn(name, OrderbyColumn.buildOrderbyType(item.getType())));
    }
    plan.setOrderbyColumns(orderbyColumns);
}
 
Example #19
Source File: MySqlSelectParser.java    From baymax with Apache License 2.0 5 votes vote down vote up
protected void parseLimit(ParseResult result, ExecutePlan plan, MySqlSelectQueryBlock mysqlSelectQuery){
    MySqlSelectQueryBlock.Limit x = mysqlSelectQuery.getLimit();
    if (x == null){
        return;
    }
    Map<Integer, Object> overrideParameters = new HashMap<Integer, Object>(2);
    int offset = 0;
    if (null != x.getOffset()) {
        if (x.getOffset() instanceof SQLNumericLiteralExpr) {
            offset = ((SQLNumericLiteralExpr) x.getOffset()).getNumber().intValue();
            SQLNumberExpr offsetExpr = new SQLNumberExpr();
            offsetExpr.setNumber(0);
            x.setOffset(offsetExpr);
        } else {
            offset = ((Number) parameters.get(((SQLVariantRefExpr) x.getOffset()).getIndex())).intValue();
            overrideParameters.put(((SQLVariantRefExpr) x.getOffset()).getIndex() + 1, 0);
        }
    }
    int rowCount;
    if (x.getRowCount() instanceof SQLNumericLiteralExpr) {
        rowCount = ((SQLNumericLiteralExpr) x.getRowCount()).getNumber().intValue();
        SQLNumberExpr rowsExpr = new SQLNumberExpr();
        rowsExpr.setNumber(rowCount + offset);
        x.setRowCount(rowsExpr);
    } else {
        rowCount = ((Number) parameters.get(((SQLVariantRefExpr) x.getRowCount()).getIndex())).intValue();
        overrideParameters.put(((SQLVariantRefExpr) x.getRowCount()).getIndex() + 1, rowCount + offset);
    }
    plan.setLimit(new Limit(offset, rowCount));
    plan.setOverrideParameters(overrideParameters);
}
 
Example #20
Source File: MySqlSelectAggParserTest.java    From baymax with Apache License 2.0 5 votes vote down vote up
/**
 * //agg
 * getMergeColumns
 * getAliaColumns
 *
 * //group by
 * setGroupbyColumns
 *
 * @param sql
 */
public void test(String sql){
    ParseResult result = new ParseResult();
    ExecutePlan plan = new ExecutePlan();

    parser.init(sql, null);
    parser.parse(result);

    plan.setExecuteType(ExecuteType.PARTITION);

    SQLSelectStatement stmt = (SQLSelectStatement) parser.statement;

    parser.parseMysqlQueary(result, plan, (MySqlSelectQueryBlock) stmt.getSelect().getQuery());

    StringBuilder out = new StringBuilder();
    MySqlOutputVisitor outPutVisitor = new MySqlOutputVisitor(out);
    stmt.accept(outPutVisitor);

    System.out.println();
    System.out.println("/***********************agg*************************/");
    System.out.println(plan.getMergeColumns());
    System.out.println("setHasAllColumnExpr:" + result.isHasAllColumnExpr());
    System.out.println(out.toString());
    PrintUtil.printFildAlisMap(result.getAliaColumns());
    System.out.println("/*********************group by**********************/");
    System.out.println(plan.getGroupbyColumns());
    System.out.println("/*********************order by**********************/");
    System.out.println(plan.getOrderbyColumns());

}
 
Example #21
Source File: MysqlCountOutputVisitor.java    From Zebra with Apache License 2.0 5 votes vote down vote up
public boolean visit(MySqlSelectQueryBlock x) {
	if (x.getOrderBy() != null) {
		x.getOrderBy().setParent(x);
	}

	boolean rewriteDistinct = false;
	if (x.getSelectList() != null) {
		rewriteDistinct = visitSelectItems(x.getSelectList(), SQLSetQuantifier.DISTINCT == x.getDistionOption());
	}

	if (x.getFrom() != null) {
		println();
		print0(ucase ? "FROM " : "from ");
		x.getFrom().accept(this);
	}

	if (x.getWhere() != null) {
		println();
		print0(ucase ? "WHERE " : "where ");
		x.getWhere().setParent(x);
		x.getWhere().accept(this);
	}

	if (x.getGroupBy() != null) {
		println();
		x.getGroupBy().accept(this);
	}

	if (x.getOrderBy() != null) {
		println();
		x.getOrderBy().accept(this);
	}

	if (rewriteDistinct) {
		print0(") ZebraDaoDistinctTable");
	}

	return false;
}
 
Example #22
Source File: DefaultSQLRewrite.java    From Zebra with Apache License 2.0 5 votes vote down vote up
@Override
public boolean visit(MySqlSelectQueryBlock.Limit x) {
	print0(ucase ? "LIMIT " : "limit ");

	int offset = Integer.MIN_VALUE;
	if (x.getOffset() != null) {
		if (x.getOffset() instanceof SQLIntegerExpr && !pr.getMergeContext().isOrderBySplitSql()) {
			SQLIntegerExpr offsetExpr = (SQLIntegerExpr) x.getOffset();
			offset = (Integer) offsetExpr.getValue();
			offsetExpr.setNumber(0);
			offsetExpr.accept(this);
		} else {
			x.getOffset().accept(this);
		}

		print0(", ");
	}

	int limit = Integer.MAX_VALUE;
	if (x.getRowCount() instanceof SQLIntegerExpr && !pr.getMergeContext().isOrderBySplitSql()) {
		SQLIntegerExpr rowCountExpr = (SQLIntegerExpr) x.getRowCount();
		if (offset != Integer.MIN_VALUE) {
			limit = (Integer) rowCountExpr.getValue();
			rowCountExpr.setNumber(offset + limit);
		}
		rowCountExpr.accept(this);
	} else {
		x.getRowCount().accept(this);
	}

	return false;
}
 
Example #23
Source File: ShardPreparedStatement.java    From Zebra with Apache License 2.0 5 votes vote down vote up
private void replaceLimitParams(SQLParsedResult parseResult) {
	if (parseResult != null) {
		SQLStatement sqlStatement = parseResult.getStmt();
		if (parseResult.getStmt() != null && sqlStatement instanceof SQLSelectStatement) {
			SQLSelect sqlSelect = ((SQLSelectStatement) sqlStatement).getSelect();
			if (sqlSelect != null) {
				SQLSelectQuery sqlSelectQuery = sqlSelect.getQuery();
				if (sqlSelectQuery != null && sqlSelectQuery instanceof MySqlSelectQueryBlock) {
					MySqlSelectQueryBlock sqlSelectQueryBlock = (MySqlSelectQueryBlock) sqlSelectQuery;
					MySqlSelectQueryBlock.Limit limitExpr = sqlSelectQueryBlock.getLimit();
					if (limitExpr != null) {
						int offsetRefIndex = -1;
						int countRefIndex = -1;
						if (limitExpr.getOffset() instanceof SQLVariantRefExpr
						      && limitExpr.getRowCount() instanceof SQLVariantRefExpr) {
							SQLVariantRefExpr offsetExpr = (SQLVariantRefExpr) limitExpr.getOffset();
							SQLVariantRefExpr countExpr = (SQLVariantRefExpr) limitExpr.getRowCount();

							offsetRefIndex = offsetExpr.getIndex();
							countRefIndex = countExpr.getIndex();

							if (offsetRefIndex > countRefIndex && offsetRefIndex != -1 && countRefIndex != -1) {
								offsetExpr.setIndex(countRefIndex);
								countExpr.setIndex(offsetRefIndex);
							}
						}
					}
				}
			}
		}
	}
}
 
Example #24
Source File: WhereParser.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
private Object[] getMethodValuesWithSubQueries(SQLMethodInvokeExpr method) throws SqlParseException {
    List<Object> values = new ArrayList<>();
    for (SQLExpr innerExpr : method.getParameters()) {
        if (innerExpr instanceof SQLQueryExpr) {
            Select select = sqlParser.parseSelect((MySqlSelectQueryBlock) ((SQLQueryExpr) innerExpr).getSubQuery().getQuery());
            values.add(new SubQueryExpression(select));
        } else if (innerExpr instanceof SQLTextLiteralExpr) {
            values.add(((SQLTextLiteralExpr) innerExpr).getText());
        } else {
            values.add(innerExpr);
        }

    }
    return values.toArray();
}
 
Example #25
Source File: TestMySQLItemVisitor.java    From dble with GNU General Public License v2.0 5 votes vote down vote up
@Test
public void testGroupbyHaving() {
    MySqlSelectQueryBlock query = getQuery("select col1  from table1 group by col1 having count(*)>1  ");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    SQLExpr q = groupBy.getHaving();
    MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
    q.accept(v);
    Item item = v.getItem();
    Assert.assertEquals(true, "COUNT(*) > 1".equals(item.getItemName()));
}
 
Example #26
Source File: SqlParser.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
private void fillTableSelectedJoin(TableOnJoinSelect tableOnJoin, MySqlSelectQueryBlock query, From tableFrom, Where where, List<SQLSelectOrderByItem> orderBys, List<Condition> conditions) throws SqlParseException {
    String alias = tableFrom.getAlias();
    fillBasicTableSelectJoin(tableOnJoin, tableFrom, where, orderBys, query);
    tableOnJoin.setConnectedFields(getConnectedFields(conditions, alias));
    tableOnJoin.setSelectedFields(new ArrayList<Field>(tableOnJoin.getFields()));
    tableOnJoin.setAlias(alias);
    tableOnJoin.fillSubQueries();
}
 
Example #27
Source File: DruidSelectParser.java    From Mycat2 with GNU General Public License v3.0 5 votes vote down vote up
private void fixLimit(MySqlSelectQueryBlock mysqlSelectQuery, RouteResultsetNode node) {
	if(!getCurentDbType().equalsIgnoreCase("mysql")) {
		MySqlSelectQueryBlock.Limit  _limit = mysqlSelectQuery.getLimit();
		if (_limit != null) {
			SQLIntegerExpr offset = (SQLIntegerExpr) _limit.getOffset();
			SQLIntegerExpr count = (SQLIntegerExpr) _limit.getRowCount();
			if (offset != null && count != null) {
				String nativeSql = PageSQLUtil
						.convertLimitToNativePageSql(getCurentDbType(), node.getStatement(),
								offset.getNumber().intValue(), count.getNumber().intValue());
				node.setStatement(nativeSql);
			}
		}
	}
}
 
Example #28
Source File: DruidSelectParser.java    From Mycat2 with GNU General Public License v3.0 5 votes vote down vote up
private boolean isNeedCache(SchemaConfig schema, RouteResultset rrs,
								MySqlSelectQueryBlock mysqlSelectQuery, Map<String, Map<String, Set<ColumnRoutePair>>> allConditions) {
		if(ctx.getTables() == null || ctx.getTables().size() == 0 ) {
			return false;
		}
		TableConfig tc = schema.getTables().get(ctx.getTables().get(0));
		if(tc==null ||(ctx.getTables().size() == 1 && tc.isGlobalTable())
				) {//|| (ctx.getTables().size() == 1) && tc.getRule() == null && tc.getDataNodes().size() == 1
			return false;
		} else {
			//单表主键查询
			if(ctx.getTables().size() == 1) {
				String tableName = ctx.getTables().get(0);
				String primaryKey = schema.getTables().get(tableName).getPrimaryKey();
//				schema.getTables().get(ctx.getTables().get(0)).getParentKey() != null;
				if(ctx.getRouteCalculateUnit().getTablesAndConditions().get(tableName) != null
						&& ctx.getRouteCalculateUnit().getTablesAndConditions().get(tableName).get(primaryKey) != null
						&& tc.getDataNodes().size() > 1) {//有主键条件
					return false;
				}
				//全局表不缓存
			}else if(RouterUtil.isAllGlobalTable(ctx, schema)){
				return false;
			}
			return true;
		}
	}
 
Example #29
Source File: DruidSelectOracleParser.java    From Mycat2 with GNU General Public License v3.0 5 votes vote down vote up
@Override
public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) {
	SQLSelectStatement selectStmt = (SQLSelectStatement)stmt;
	SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
      //从mysql解析过来
	if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
		MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)selectStmt.getSelect().getQuery();
		MySqlSelectQueryBlock.Limit limit=mysqlSelectQuery.getLimit();
		if(limit==null)
		{
			  //使用oracle的解析,否则会有部分oracle语法识别错误
			  OracleStatementParser oracleParser = new OracleStatementParser(getCtx().getSql());
			  SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
               selectStmt= oracleStmt;
			  SQLSelectQuery oracleSqlSelectQuery = oracleStmt.getSelect().getQuery();
			  if(oracleSqlSelectQuery instanceof OracleSelectQueryBlock)
			  {
				  parseNativePageSql(oracleStmt, rrs, (OracleSelectQueryBlock) oracleSqlSelectQuery, schema);
			  }



		  }
		if(isNeedParseOrderAgg)
		{
			parseOrderAggGroupMysql(schema, selectStmt,rrs, mysqlSelectQuery);
			//更改canRunInReadDB属性
			if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false)
			{
				rrs.setCanRunInReadDB(false);
			}
		}

	}


}
 
Example #30
Source File: DruidSelectSqlServerParser.java    From Mycat2 with GNU General Public License v3.0 5 votes vote down vote up
@Override
public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) {
	SQLSelectStatement selectStmt = (SQLSelectStatement)stmt;
	SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
	//从mysql解析过来
	if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
		MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)selectStmt.getSelect().getQuery();
		MySqlSelectQueryBlock.Limit limit=mysqlSelectQuery.getLimit();
		if(limit==null)
		{
               sqlserverParse(schema, rrs);


           }
		if(isNeedParseOrderAgg)
		{
			parseOrderAggGroupMysql(schema, stmt,rrs, mysqlSelectQuery);
			//更改canRunInReadDB属性
			if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false)
			{
				rrs.setCanRunInReadDB(false);
			}
		}

	}


}