/* * Copyright (c) 2015. Qubole Inc * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.qubole.quark.utilities; import org.apache.calcite.linq4j.Ord; import org.apache.calcite.linq4j.tree.Expressions; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Aggregate; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.core.Calc; import org.apache.calcite.rel.core.Filter; import org.apache.calcite.rel.core.Intersect; import org.apache.calcite.rel.core.Join; import org.apache.calcite.rel.core.JoinRelType; import org.apache.calcite.rel.core.Limit; import org.apache.calcite.rel.core.Minus; import org.apache.calcite.rel.core.Project; import org.apache.calcite.rel.core.Sort; import org.apache.calcite.rel.core.TableModify; import org.apache.calcite.rel.core.TableScan; import org.apache.calcite.rel.core.Union; import org.apache.calcite.rel.core.Values; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeField; import org.apache.calcite.rex.RexCall; import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexLocalRef; import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexProgram; import org.apache.calcite.sql.JoinConditionType; import org.apache.calcite.sql.JoinType; import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlDataTypeSpec; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlJoin; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlNodeList; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.SqlSelect; import org.apache.calcite.sql.SqlSelectKeyword; import org.apache.calcite.sql.SqlSetOperator; import org.apache.calcite.sql.fun.SqlCase; import org.apache.calcite.sql.fun.SqlSingleValueAggFunction; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.fun.SqlSumEmptyIsZeroAggFunction; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.BasicSqlType; import org.apache.calcite.sql.type.InferTypes; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.type.SqlTypeUtil; import org.apache.calcite.sql.validate.SqlValidatorUtil; import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; import com.google.common.collect.ImmutableList; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.AbstractList; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import java.util.Collections; import java.util.HashMap; import java.util.LinkedHashSet; import java.util.List; import java.util.Map; import java.util.Set; /** * Created by amargoor on 9/30/15. */ public class RelToSqlConverter { public static final SqlParserPos POS = SqlParserPos.ZERO; private static final Logger LOGGER = LoggerFactory.getLogger(RelToSqlConverter.class); final SqlDialect dialect; private final Set<String> aliasSet = new LinkedHashSet<String>(); private final Map<String, SqlNode> ordinalMap = new HashMap<>(); public RelToSqlConverter(SqlDialect dialect) { this.dialect = dialect; } /** * Creates a result based on a single relational expression. */ public Result result(SqlNode node, Collection<Clause> clauses, RelNode rel) { final String alias2 = SqlValidatorUtil.getAlias(node, -1); final String alias3 = alias2 != null ? alias2 : "t"; final String alias4 = SqlValidatorUtil.uniquify( alias3, aliasSet, SqlValidatorUtil.EXPR_SUGGESTER); final String alias5 = alias2 == null || !alias2.equals(alias4) ? alias4 : null; return new Result(node, clauses, alias5, Collections.singletonList(Pair.of(alias4, rel.getRowType()))); } /** * Creates a result based on a join. (Each join could contain one or more * relational expressions.) */ public Result result(SqlNode join, Result leftResult, Result rightResult) { final List<Pair<String, RelDataType>> list = new ArrayList<Pair<String, RelDataType>>(); list.addAll(leftResult.aliases); list.addAll(rightResult.aliases); return new Result(join, Expressions.list(Clause.FROM), null, list); } /** * Wraps a node in a SELECT statement that has no clauses: * "SELECT ... FROM (node)". */ SqlSelect wrapSelect(SqlNode node) { assert node instanceof SqlJoin || node instanceof SqlIdentifier || node instanceof SqlCall && (((SqlCall) node).getOperator() instanceof SqlSetOperator || ((SqlCall) node).getOperator() == SqlStdOperatorTable.AS) : node; return new SqlSelect(POS, SqlNodeList.EMPTY, null, node, null, null, null, SqlNodeList.EMPTY, null, null, null); } public Result visitChild(int i, RelNode e) { if (e instanceof Union) { return visitUnion((Union) e); } else if (e instanceof Join) { return visitJoin((Join) e); } else if (e instanceof Filter) { return visitFilter((Filter) e); } else if (e instanceof Project) { return visitProject((Project) e); } else if (e instanceof Aggregate) { return visitAggregate((Aggregate) e); } else if (e instanceof TableScan) { return visitTableScan((TableScan) e); } else if (e instanceof Intersect) { return visitIntersect((Intersect) e); } else if (e instanceof Minus) { return visitMinus((Minus) e); } else if (e instanceof Calc) { return visitCalc((Calc) e); } else if (e instanceof Sort) { return visitSort((Sort) e); } else if (e instanceof TableModify) { return visitTableModify((TableModify) e); } else if (e instanceof Limit) { return visitLimit((Limit) e); } else { throw new AssertionError("Need to Implement for " + e.getClass().getName()); // TODO: } } public Result visitLimit(Limit e) { final Result x = visitChild(0, e.getInput()); final Builder builder = x.builder(e, Clause.FETCH, Clause.OFFSET); final SqlNode fetch = builder.context.toSql(null, e.getFetch()); builder.setfetch(fetch); if (e.getOffset() != null) { final SqlNode offset = builder.context.toSql(null, e.getOffset()); builder.setOffset(offset); } return builder.result(); } public Result visitUnion(Union e) { final SqlSetOperator operator = e.all ? SqlStdOperatorTable.UNION_ALL : SqlStdOperatorTable.UNION; return setOpToSql(operator, e); } public Result visitJoin(Join e) { final Result leftResult = visitChild(0, e.getLeft()); final Result rightResult = visitChild(1, e.getRight()); final Context leftContext = leftResult.qualifiedContext(); final Context rightContext = rightResult.qualifiedContext(); SqlNode sqlCondition = convertConditionToSqlNode(e.getCondition(), leftContext, rightContext, e.getLeft().getRowType().getFieldCount()); SqlNode join = new SqlJoin(POS, leftResult.asFrom(), SqlLiteral.createBoolean(false, POS), joinType(e.getJoinType()).symbol(POS), rightResult.asFrom(), JoinConditionType.ON.symbol(POS), sqlCondition); return result(join, leftResult, rightResult); } public Result visitFilter(Filter e) { Result x = visitChild(0, e.getInput()); final Builder builder = x.builder(e, Clause.WHERE); builder.setWhere(builder.context.toSql(null, e.getCondition())); return builder.result(); } public Result visitProject(Project e) { Result x = visitChild(0, e.getInput()); if (isStar(e.getChildExps(), e.getInput().getRowType())) { return x; } final Builder builder = x.builder(e, Clause.SELECT); final List<SqlNode> selectList = new ArrayList<>(); for (RexNode ref : e.getChildExps()) { SqlNode sqlExpr = builder.context.toSql(null, ref); addSelect(selectList, sqlExpr, e.getRowType()); } builder.setSelect(new SqlNodeList(selectList, POS)); return builder.result(); } public Result visitAggregate(Aggregate e) { // "select a, b, sum(x) from ( ... ) group by a, b" final Result x = visitChild(0, e.getInput()); final Builder builder = x.builder(e, Clause.GROUP_BY); List<SqlNode> groupByList = Expressions.list(); final List<SqlNode> selectList = new ArrayList<>(); for (int group : e.getGroupSet()) { final SqlNode field = builder.context.field(group); addSelect(selectList, field, e.getRowType()); groupByList.add(field); } for (AggregateCall aggCall : e.getAggCallList()) { SqlNode aggCallSqlNode = builder.context.toSql(aggCall); if (aggCall.getAggregation() instanceof SqlSingleValueAggFunction) { aggCallSqlNode = rewriteSingleValueExpr(aggCallSqlNode, dialect); } addSelect(selectList, aggCallSqlNode, e.getRowType()); } builder.setSelect(new SqlNodeList(selectList, POS)); if (!groupByList.isEmpty() || e.getAggCallList().isEmpty()) { // Some databases don't support "GROUP BY ()". We can omit it as long // as there is at least one aggregate function. builder.setGroupBy(new SqlNodeList(groupByList, POS)); } return builder.result(); } public Result visitTableScan(TableScan e) { return result(new SqlIdentifier(e.getTable().getQualifiedName(), SqlParserPos.ZERO), Collections.singletonList(Clause.FROM), e); } public Result visitIntersect(Intersect e) { return setOpToSql(e.all ? SqlStdOperatorTable.INTERSECT_ALL : SqlStdOperatorTable.INTERSECT, e); } public Result visitMinus(Minus e) { return setOpToSql(e.all ? SqlStdOperatorTable.EXCEPT_ALL : SqlStdOperatorTable.EXCEPT, e); } public Result visitCalc(Calc e) { Result x = visitChild(0, e.getInput()); final RexProgram program = e.getProgram(); Builder builder = program.getCondition() != null ? x.builder(e, Clause.WHERE) : x.builder(e); if (!isStar(program)) { final List<SqlNode> selectList = new ArrayList<>(); for (RexLocalRef ref : program.getProjectList()) { SqlNode sqlExpr = builder.context.toSql(program, ref); addSelect(selectList, sqlExpr, e.getRowType()); } builder.setSelect(new SqlNodeList(selectList, POS)); } if (program.getCondition() != null) { builder.setWhere( builder.context.toSql(program, program.getCondition())); } return builder.result(); } public Result visitValues(Values e) { final List<String> fields = e.getRowType().getFieldNames(); final List<Clause> clauses = Collections.singletonList(Clause.SELECT); final Context context = new AliasContext(Collections.<Pair<String, RelDataType>>emptyList(), false); final List<SqlSelect> selects = new ArrayList<>(); for (List<RexLiteral> tuple : e.getTuples()) { final List<SqlNode> selectList = new ArrayList<>(); for (Pair<RexLiteral, String> literal : Pair.zip(tuple, fields)) { selectList.add( SqlStdOperatorTable.AS.createCall( POS, context.toSql(null, literal.left), new SqlIdentifier(literal.right, POS))); } selects.add( new SqlSelect(POS, SqlNodeList.EMPTY, new SqlNodeList(selectList, POS), null, null, null, null, null, null, null, null)); } SqlNode query = null; for (SqlSelect select : selects) { if (query == null) { query = select; } else { query = SqlStdOperatorTable.UNION_ALL.createCall(POS, query, select); } } return result(query, clauses, e); } public Result visitSort(Sort e) { final Result x = visitChild(0, e.getInput()); final Builder builder = x.builder(e, Clause.ORDER_BY); List<SqlNode> orderByList = Expressions.list(); for (RelFieldCollation fieldCollation : e.getCollation().getFieldCollations()) { if (fieldCollation.nullDirection != RelFieldCollation.NullDirection.UNSPECIFIED && dialect.getDatabaseProduct() == SqlDialect.DatabaseProduct.MYSQL) { orderByList.add( ISNULL_FUNCTION.createCall(POS, builder.context.field(fieldCollation.getFieldIndex()))); fieldCollation = new RelFieldCollation(fieldCollation.getFieldIndex(), fieldCollation.getDirection()); } orderByList.add(builder.context.toSql(fieldCollation)); } builder.setOrderBy(new SqlNodeList(orderByList, POS)); return builder.result(); } public Result visitTableModify(TableModify e) { throw new AssertionError(); // TODO: } /** * MySQL specific function. */ private static final SqlFunction ISNULL_FUNCTION = new SqlFunction("ISNULL", SqlKind.OTHER_FUNCTION, ReturnTypes.BOOLEAN, InferTypes.FIRST_KNOWN, OperandTypes.ANY, SqlFunctionCategory.SYSTEM); /** * Rewrite SINGLE_VALUE into expression based on database variants * E.g. HSQLDB, MYSQL, ORACLE, etc */ private SqlNode rewriteSingleValueExpr(SqlNode aggCall, SqlDialect sqlDialect) { final SqlNode operand = ((SqlBasicCall) aggCall).operand(0); final SqlNode caseOperand; final SqlNode elseExpr; final SqlNode countCall = SqlStdOperatorTable.COUNT.createCall(POS, operand); final SqlLiteral nullLiteral = SqlLiteral.createNull(POS); final SqlNode wrappedOperand; switch (sqlDialect.getDatabaseProduct()) { case MYSQL: case HSQLDB: caseOperand = countCall; final SqlNodeList selectList = new SqlNodeList(POS); selectList.add(nullLiteral); final SqlNode unionOperand; switch (sqlDialect.getDatabaseProduct()) { case MYSQL: wrappedOperand = operand; unionOperand = new SqlSelect(POS, SqlNodeList.EMPTY, selectList, null, null, null, null, SqlNodeList.EMPTY, null, null, null); break; default: wrappedOperand = SqlStdOperatorTable.MIN.createCall(POS, operand); unionOperand = SqlStdOperatorTable.VALUES.createCall(POS, SqlLiteral.createApproxNumeric("0", POS)); } SqlCall unionAll = SqlStdOperatorTable.UNION_ALL .createCall(POS, unionOperand, unionOperand); final SqlNodeList subQuery = new SqlNodeList(POS); subQuery.add(unionAll); final SqlNodeList selectList2 = new SqlNodeList(POS); selectList2.add(nullLiteral); elseExpr = SqlStdOperatorTable.SCALAR_QUERY.createCall(POS, subQuery); break; default: LOGGER.info("SINGLE_VALUE rewrite not supported for " + sqlDialect.getDatabaseProduct()); return aggCall; } final SqlNodeList whenList = new SqlNodeList(POS); whenList.add(SqlLiteral.createExactNumeric("0", POS)); whenList.add(SqlLiteral.createExactNumeric("1", POS)); final SqlNodeList thenList = new SqlNodeList(POS); thenList.add(nullLiteral); thenList.add(wrappedOperand); SqlNode caseExpr = new SqlCase(POS, caseOperand, whenList, thenList, elseExpr); LOGGER.info("SINGLE_VALUE rewritten into [" + caseExpr + "]"); return caseExpr; } private void addSelect( List<SqlNode> selectList, SqlNode node, RelDataType rowType) { String name = rowType.getFieldNames().get(selectList.size()); String alias = SqlValidatorUtil.getAlias(node, -1); if (name.toLowerCase().startsWith("expr$")) { //Put it in ordinalMap ordinalMap.put(name.toLowerCase(), node); } else if (alias == null || !alias.equals(name)) { node = SqlStdOperatorTable.AS.createCall( POS, node, new SqlIdentifier(name, POS)); } selectList.add(node); } private static boolean isStar(List<RexNode> exps, RelDataType inputRowType) { int i = 0; for (RexNode ref : exps) { if (!(ref instanceof RexInputRef)) { return false; } else if (((RexInputRef) ref).getIndex() != i++) { return false; } } return i == inputRowType.getFieldCount(); } private static boolean isStar(RexProgram program) { int i = 0; for (RexLocalRef ref : program.getProjectList()) { if (ref.getIndex() != i++) { return false; } } return i == program.getInputRowType().getFieldCount(); } private Result setOpToSql(SqlSetOperator operator, RelNode rel) { List<SqlNode> list = Expressions.list(); for (Ord<RelNode> input : Ord.zip(rel.getInputs())) { final Result result = this.visitChild(input.i, input.e); list.add(result.asSelect()); } final SqlCall node = operator.createCall(new SqlNodeList(list, POS)); final List<Clause> clauses = Expressions.list(Clause.SET_OP); return result(node, clauses, rel); } /** * Convert {@link RexNode} condition into {@link SqlNode} * * @param node condition Node * @param leftContext LeftContext * @param rightContext RightContext * @param leftFieldCount Number of field on left result * @return SqlJoin which represent the condition */ private SqlNode convertConditionToSqlNode(RexNode node, Context leftContext, Context rightContext, int leftFieldCount) { if (!(node instanceof RexCall)) { throw new AssertionError(node); } final List<RexNode> operands; final SqlOperator op; switch (node.getKind()) { case AND: case OR: operands = ((RexCall) node).getOperands(); op = ((RexCall) node).getOperator(); SqlNode sqlCondition = null; for (RexNode operand : operands) { SqlNode x = convertConditionToSqlNode(operand, leftContext, rightContext, leftFieldCount); if (sqlCondition == null) { sqlCondition = x; } else { sqlCondition = op.createCall(POS, sqlCondition, x); } } return sqlCondition; case EQUALS: case IS_NOT_DISTINCT_FROM: case NOT_EQUALS: case GREATER_THAN: case GREATER_THAN_OR_EQUAL: case LESS_THAN: case LESS_THAN_OR_EQUAL: operands = ((RexCall) node).getOperands(); op = ((RexCall) node).getOperator(); if (operands.get(0) instanceof RexInputRef && operands.get(1) instanceof RexInputRef) { final RexInputRef op0 = (RexInputRef) operands.get(0); final RexInputRef op1 = (RexInputRef) operands.get(1); if (op0.getIndex() < leftFieldCount && op1.getIndex() >= leftFieldCount) { // Arguments were of form 'op0 = op1' return op.createCall(POS, leftContext.field(op0.getIndex()), rightContext.field(op1.getIndex() - leftFieldCount)); } if (op1.getIndex() < leftFieldCount && op0.getIndex() >= leftFieldCount) { // Arguments were of form 'op1 = op0' return reverseOperatorDirection(op).createCall(POS, leftContext.field(op1.getIndex()), rightContext.field(op0.getIndex() - leftFieldCount)); } } } throw new AssertionError(node); } private static SqlOperator reverseOperatorDirection(SqlOperator op) { switch (op.kind) { case GREATER_THAN: return SqlStdOperatorTable.LESS_THAN; case GREATER_THAN_OR_EQUAL: return SqlStdOperatorTable.LESS_THAN_OR_EQUAL; case LESS_THAN: return SqlStdOperatorTable.GREATER_THAN; case LESS_THAN_OR_EQUAL: return SqlStdOperatorTable.GREATER_THAN_OR_EQUAL; case EQUALS: case IS_NOT_DISTINCT_FROM: case NOT_EQUALS: return op; default: throw new AssertionError(op); } } private static JoinType joinType(JoinRelType joinType) { switch (joinType) { case LEFT: return JoinType.LEFT; case RIGHT: return JoinType.RIGHT; case INNER: return JoinType.INNER; case FULL: return JoinType.FULL; default: throw new AssertionError(joinType); } } /** * Context for translating a {@link RexNode} expression (within a * {@link RelNode}) into a {@link SqlNode} expression (within a SQL parse * tree). */ public abstract class Context { private final int fieldCount; private final boolean ignoreCast; protected Context(int fieldCount) { this(fieldCount, true); } protected Context(int fieldCount, boolean ignoreCast) { this.fieldCount = fieldCount; this.ignoreCast = ignoreCast; } public abstract SqlNode field(int ordinal, boolean useAlias); public abstract SqlNode field(int ordinal); /** * Converts an expression from {@link RexNode} to {@link SqlNode} * format. */ SqlNode toSql(RexProgram program, RexNode rex) { switch (rex.getKind()) { case LOCAL_REF: final int index = ((RexLocalRef) rex).getIndex(); return toSql(program, program.getExprList().get(index)); case INPUT_REF: return field(((RexInputRef) rex).getIndex()); case LITERAL: final RexLiteral literal = (RexLiteral) rex; if (literal.getTypeName() == SqlTypeName.SYMBOL) { final Enum symbol = (Enum) literal.getValue(); return SqlLiteral.createSymbol(symbol, POS); } switch (literal.getTypeName().getFamily()) { case CHARACTER: return SqlLiteral.createCharString((String) literal.getValue2(), POS); case NUMERIC: case EXACT_NUMERIC: return SqlLiteral.createExactNumeric(literal.getValue().toString(), POS); case APPROXIMATE_NUMERIC: return SqlLiteral.createApproxNumeric( literal.getValue().toString(), POS); case BOOLEAN: return SqlLiteral.createBoolean((Boolean) literal.getValue(), POS); case DATE: return SqlLiteral.createDate((Calendar) literal.getValue(), POS); case TIME: return SqlLiteral.createTime((Calendar) literal.getValue(), literal.getType().getPrecision(), POS); case TIMESTAMP: return SqlLiteral.createTimestamp((Calendar) literal.getValue(), literal.getType().getPrecision(), POS); case ANY: case NULL: switch (literal.getTypeName()) { case NULL: return SqlLiteral.createNull(POS); // fall through } default: throw new AssertionError(literal + ": " + literal.getTypeName()); } case CASE: final RexCall caseCall = (RexCall) rex; final List<SqlNode> caseNodeList = toSql(program, caseCall.getOperands()); final SqlNode valueNode; final List<SqlNode> whenList = Expressions.list(); final List<SqlNode> thenList = Expressions.list(); final SqlNode elseNode; if (caseNodeList.size() % 2 == 0) { // switched: // "case x when v1 then t1 when v2 then t2 ... else e end" valueNode = caseNodeList.get(0); for (int i = 1; i < caseNodeList.size() - 1; i += 2) { whenList.add(caseNodeList.get(i)); thenList.add(caseNodeList.get(i + 1)); } } else { // other: "case when w1 then t1 when w2 then t2 ... else e end" valueNode = null; for (int i = 0; i < caseNodeList.size() - 1; i += 2) { whenList.add(caseNodeList.get(i)); thenList.add(caseNodeList.get(i + 1)); } } elseNode = caseNodeList.get(caseNodeList.size() - 1); return new SqlCase(POS, valueNode, new SqlNodeList(whenList, POS), new SqlNodeList(thenList, POS), elseNode); default: final RexCall call = (RexCall) rex; final SqlOperator op = call.getOperator(); final List<SqlNode> nodeList = toSql(program, call.getOperands()); switch (rex.getKind()) { case CAST: if (!ignoreCast) { nodeList.add(toSql(call.getType())); } else { assert nodeList.size() == 1; return nodeList.get(0); } } if (op instanceof SqlBinaryOperator && nodeList.size() > 2) { // In RexNode trees, OR and AND have any number of children; // SqlCall requires exactly 2. So, convert to a left-deep binary tree. return createLeftCall(op, nodeList); } return op.createCall(new SqlNodeList(nodeList, POS)); } } private SqlNode createLeftCall(SqlOperator op, List<SqlNode> nodeList) { if (nodeList.size() == 2) { return op.createCall(new SqlNodeList(nodeList, POS)); } final List<SqlNode> butLast = Util.skipLast(nodeList); final SqlNode last = nodeList.get(nodeList.size() - 1); final SqlNode call = createLeftCall(op, butLast); return op.createCall(new SqlNodeList(ImmutableList.of(call, last), POS)); } private SqlNode toSql(RelDataType type) { switch (dialect.getDatabaseProduct()) { case MYSQL: switch (type.getSqlTypeName()) { case VARCHAR: // MySQL doesn't have a VARCHAR type, only CHAR. return new SqlDataTypeSpec(new SqlIdentifier("CHAR", POS), type.getPrecision(), -1, null, null, POS); case INTEGER: return new SqlDataTypeSpec(new SqlIdentifier("_UNSIGNED", POS), type.getPrecision(), -1, null, null, POS); } break; } if (type instanceof BasicSqlType) { return new SqlDataTypeSpec( new SqlIdentifier(type.getSqlTypeName().name(), POS), type.getPrecision(), type.getScale(), type.getCharset() != null && dialect.supportsCharSet() ? type.getCharset().name() : null, null, POS); } return SqlTypeUtil.convertTypeToSpec(type); //throw new AssertionError(type); // TODO: implement } private List<SqlNode> toSql(RexProgram program, List<RexNode> operandList) { final List<SqlNode> list = new ArrayList<SqlNode>(); for (RexNode rex : operandList) { list.add(toSql(program, rex)); } return list; } public List<SqlNode> fieldList() { return new AbstractList<SqlNode>() { public SqlNode get(int index) { return field(index); } public int size() { return fieldCount; } }; } /** * Converts a call to an aggregate function to an expression. */ public SqlNode toSql(AggregateCall aggCall) { SqlOperator op = (SqlAggFunction) aggCall.getAggregation(); if (op instanceof SqlSumEmptyIsZeroAggFunction) { op = SqlStdOperatorTable.SUM; } final List<SqlNode> operands = Expressions.list(); for (int arg : aggCall.getArgList()) { operands.add(field(arg)); } return op.createCall( aggCall.isDistinct() ? SqlSelectKeyword.DISTINCT.symbol(POS) : null, POS, operands.toArray(new SqlNode[operands.size()])); } /** * Converts a collation to an ORDER BY item. */ public SqlNode toSql(RelFieldCollation collation) { SqlNode node = field(collation.getFieldIndex(), true); switch (collation.getDirection()) { case DESCENDING: case STRICTLY_DESCENDING: node = SqlStdOperatorTable.DESC.createCall(POS, node); } // ******* Disable Null Collation ****** // switch (collation.nullDirection) { // case FIRST: // node = SqlStdOperatorTable.NULLS_FIRST.createCall(POS, node); // break; // case LAST: // node = SqlStdOperatorTable.NULLS_LAST.createCall(POS, node); // break; // } return node; } } private static int computeFieldCount( List<Pair<String, RelDataType>> aliases) { int x = 0; for (Pair<String, RelDataType> alias : aliases) { x += alias.right.getFieldCount(); } return x; } /** * Implementation of Context that precedes field references with their * "table alias" based on the current sub-query's FROM clause. */ public class AliasContext extends Context { private final boolean qualified; private final List<Pair<String, RelDataType>> aliases; public AliasContext(List<Pair<String, RelDataType>> aliases, boolean qualified) { super(computeFieldCount(aliases)); this.aliases = aliases; this.qualified = qualified; } public SqlNode field(int ordinal, boolean useAlias) { return field(ordinal); } public SqlNode field(int ordinal) { for (Pair<String, RelDataType> alias : aliases) { final List<RelDataTypeField> fields = alias.right.getFieldList(); if (ordinal < fields.size()) { RelDataTypeField field = fields.get(ordinal); final SqlNode mappedSqlNode = ordinalMap.get(field.getName().toLowerCase()); if (mappedSqlNode != null) { return mappedSqlNode; } else { return new SqlIdentifier(!qualified ? ImmutableList.of(field.getName()) : ImmutableList.of(alias.left, field.getName()), POS); } } ordinal -= fields.size(); } throw new AssertionError( "field ordinal " + ordinal + " out of range " + aliases); } } /** * Result of implementing a node. */ public class Result { final SqlNode node; private final String neededAlias; private final List<Pair<String, RelDataType>> aliases; final Expressions.FluentList<Clause> clauses; private Result(SqlNode node, Collection<Clause> clauses, String neededAlias, List<Pair<String, RelDataType>> aliases) { this.node = node; this.neededAlias = neededAlias; this.aliases = aliases; this.clauses = Expressions.list(clauses); } /** * Once you have a Result of implementing a child relational expression, * call this method to create a Builder to implement the current relational * expression by adding additional clauses to the SQL query. * <p></p> * <p>You need to declare which clauses you intend to add. If the clauses * are "later", you can add to the same query. For example, "GROUP BY" comes * after "WHERE". But if they are the same or earlier, this method will * start a new SELECT that wraps the previous result.</p> * <p>When you have called * {@link Builder#setSelect(org.apache.calcite.sql.SqlNodeList)}, * {@link Builder#setWhere(org.apache.calcite.sql.SqlNode)} etc. call * {@link Builder#result(org.apache.calcite.sql.SqlNode, java.util.Collection, org.apache.calcite.rel.RelNode)} * to fix the new query.</p> * * @param rel Relational expression being implemented * @param clauses Clauses that will be generated to implement current * relational expression * @return A builder */ public Builder builder(RelNode rel, Clause... clauses) { final Clause maxClause = maxClause(); boolean needNew = false; for (Clause clause : clauses) { if (maxClause.ordinal() >= clause.ordinal()) { needNew = true; } } SqlSelect select; Expressions.FluentList<Clause> clauseList = Expressions.list(); if (needNew) { select = subSelect(); } else { select = asSelect(); clauseList.addAll(this.clauses); } clauseList.appendAll(clauses); Context newContext; final SqlNodeList selectList = select.getSelectList(); if (selectList != null) { newContext = new Context(selectList.size()) { @Override public SqlNode field(int ordinal) { return field(ordinal, false); } @Override public SqlNode field(int ordinal, boolean useAlias) { final SqlNode selectItem = selectList.get(ordinal); switch (selectItem.getKind()) { case AS: if (useAlias) { return ((SqlCall) selectItem).operand(1); } else { return ((SqlCall) selectItem).operand(0); } } return selectItem; } }; } else { newContext = new AliasContext(aliases, aliases.size() > 1); } return new Builder(rel, clauseList, select, newContext); } // make private? public Clause maxClause() { Clause maxClause = null; for (Clause clause : clauses) { if (maxClause == null || clause.ordinal() > maxClause.ordinal()) { maxClause = clause; } } assert maxClause != null; return maxClause; } /** * Returns a node that can be included in the FROM clause or a JOIN. It has * an alias that is unique within the query. The alias is implicit if it * can be derived using the usual rules (For example, "SELECT * FROM emp" is * equivalent to "SELECT * FROM emp AS emp".) */ public SqlNode asFrom() { if (neededAlias != null) { return SqlStdOperatorTable.AS.createCall(POS, node, new SqlIdentifier(neededAlias, POS)); } return node; } public SqlSelect subSelect() { return wrapSelect(asFrom()); } /** * Converts a non-query node into a SELECT node. Set operators (UNION, * INTERSECT, EXCEPT) remain as is. */ SqlSelect asSelect() { if (node instanceof SqlSelect) { return (SqlSelect) node; } return wrapSelect(node); } /** * Converts a non-query node into a SELECT node. Set operators (UNION, * INTERSECT, EXCEPT) remain as is. */ public SqlNode asQuery() { if (node instanceof SqlCall && ((SqlCall) node).getOperator() instanceof SqlSetOperator) { return node; } return asSelect(); } /** * Returns a context that always qualifies identifiers. Useful if the * Context deals with just one arm of a join, yet we wish to generate * a join condition that qualifies column names to disambiguate them. */ public Context qualifiedContext() { return new AliasContext(aliases, true); } } /** * Builder. */ public class Builder { private final RelNode rel; private final List<Clause> clauses; private final SqlSelect select; public final Context context; public Builder(RelNode rel, List<Clause> clauses, SqlSelect select, Context context) { this.rel = rel; this.clauses = clauses; this.select = select; this.context = context; } public void setSelect(SqlNodeList nodeList) { select.setSelectList(nodeList); } public void setWhere(SqlNode node) { assert clauses.contains(Clause.WHERE); select.setWhere(node); } public void setGroupBy(SqlNodeList nodeList) { assert clauses.contains(Clause.GROUP_BY); select.setGroupBy(nodeList); } public void setOrderBy(SqlNodeList nodeList) { assert clauses.contains(Clause.ORDER_BY); select.setOrderBy(nodeList); } public void setfetch(SqlNode fetch) { assert clauses.contains(Clause.FETCH); select.setFetch(fetch); } public void setOffset(SqlNode offset) { assert clauses.contains(Clause.OFFSET); select.setOffset(offset); } public Result result() { return RelToSqlConverter.this.result(select, clauses, rel); } } /** * Clauses in a SQL query. Ordered by evaluation order. * SELECT is set only when there is a NON-TRIVIAL SELECT clause. */ enum Clause { FROM, WHERE, GROUP_BY, HAVING, SELECT, SET_OP, ORDER_BY, FETCH, OFFSET } }