Return-Path: X-Original-To: apmail-calcite-commits-archive@www.apache.org Delivered-To: apmail-calcite-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 0BF5218F8E for ; Thu, 10 Dec 2015 04:53:11 +0000 (UTC) Received: (qmail 19899 invoked by uid 500); 10 Dec 2015 04:53:11 -0000 Delivered-To: apmail-calcite-commits-archive@calcite.apache.org Received: (qmail 19849 invoked by uid 500); 10 Dec 2015 04:53:11 -0000 Mailing-List: contact commits-help@calcite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@calcite.apache.org Delivered-To: mailing list commits@calcite.apache.org Received: (qmail 19762 invoked by uid 99); 10 Dec 2015 04:53:10 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 10 Dec 2015 04:53:10 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id B917AE0D5B; Thu, 10 Dec 2015 04:53:10 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: jhyde@apache.org To: commits@calcite.apache.org Date: Thu, 10 Dec 2015 04:53:12 -0000 Message-Id: <7f25e8dbd6bf4ed7883bb3c4840d8a60@git.apache.org> In-Reply-To: References: X-Mailer: ASF-Git Admin Mailer Subject: [3/5] calcite git commit: [CALCITE-1003] Utility to convert RelNode to SQL (Amogh Margoor) [CALCITE-1003] Utility to convert RelNode to SQL (Amogh Margoor) Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/a8a8878d Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/a8a8878d Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/a8a8878d Branch: refs/heads/master Commit: a8a8878da1d3f9a1847ca2f39747cd37af890571 Parents: fa08c46 Author: Amogh Margoor Authored: Tue Dec 1 03:05:55 2015 -0500 Committer: Julian Hyde Committed: Wed Dec 9 13:35:29 2015 -0800 ---------------------------------------------------------------------- .../calcite/rel/rel2sql/RelToSqlConverter.java | 1058 ++++++++++++++++++ .../calcite/rel/rel2sql/package-info.java | 26 + .../rel/rel2sql/RelToSqlConverterTest.java | 347 ++++++ 3 files changed, 1431 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/a8a8878d/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java new file mode 100644 index 0000000..12f9ab0 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java @@ -0,0 +1,1058 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you 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 org.apache.calcite.rel.rel2sql; + +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.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 com.google.common.collect.ImmutableSet; + +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; +import java.util.logging.Logger; + +/** + * Utility to convert RelNodes to SqlNode + */ +public class RelToSqlConverter { + + private static final Logger LOGGER = Logger.getLogger(RelToSqlConverter.class.getName()); + + public static final SqlParserPos POS = SqlParserPos.ZERO; + + final SqlDialect dialect; + private final Set aliasSet = new LinkedHashSet(); + + private final Map 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 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> list = + new ArrayList>(); + 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 { + throw new AssertionError("Need to Implement for " + e.getClass().getName()); // TODO: + } + } + + 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 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; + if (e.getInput() instanceof Project) { + builder = x.builder(e); + builder.clauses.add(Clause.GROUP_BY); + } else { + builder = x.builder(e, Clause.GROUP_BY); + } + List groupByList = Expressions.list(); + final List 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 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 fields = e.getRowType().getFieldNames(); + final List clauses = Collections.singletonList(Clause.SELECT); + final Context context = + new AliasContext(Collections.>emptyList(), false); + final List selects = new ArrayList<>(); + for (List tuple : e.getTuples()) { + final List selectList = new ArrayList<>(); + for (Pair 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 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 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 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 list = Expressions.list(); + for (Ord 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 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 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); + + /** + * 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 SqlLiteral.SqlSymbol symbol = + (SqlLiteral.SqlSymbol) 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 caseNodeList = + toSql(program, caseCall.getOperands()); + final SqlNode valueNode; + final List whenList = Expressions.list(); + final List 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 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 nodeList) { + if (nodeList.size() == 2) { + return op.createCall(new SqlNodeList(nodeList, POS)); + } + final List 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 toSql(RexProgram program, List operandList) { + final List list = new ArrayList(); + for (RexNode rex : operandList) { + list.add(toSql(program, rex)); + } + return list; + } + + public List fieldList() { + return new AbstractList() { + 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 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()); + switch (collation.getDirection()) { + case DESCENDING: + case STRICTLY_DESCENDING: + node = SqlStdOperatorTable.DESC.createCall(POS, node); + } + 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> aliases) { + int x = 0; + for (Pair 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> aliases; + + public AliasContext(List> aliases, + boolean qualified) { + super(computeFieldCount(aliases)); + this.aliases = aliases; + this.qualified = qualified; + } + + public SqlNode field(int ordinal) { + for (Pair alias : aliases) { + final List 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> aliases; + final Expressions.FluentList clauses; + + private Result(SqlNode node, Collection clauses, String neededAlias, + List> 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. + * + *

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. + * + *

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. + * + * @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; + // If old and new clause are equal and belong to below set, + // then new SELECT wrap is not required + Set nonWrapSet = ImmutableSet.of(Clause.SELECT); + for (Clause clause : clauses) { + if (maxClause.ordinal() > clause.ordinal() + || (maxClause.equals(clause) && !nonWrapSet.contains(clause))) { + needNew = true; + } + } + SqlSelect select; + Expressions.FluentList 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) { + final SqlNode selectItem = selectList.get(ordinal); + switch (selectItem.getKind()) { + case AS: + 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 clauses; + private final SqlSelect select; + public final Context context; + + public Builder(RelNode rel, List 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 + } +} http://git-wip-us.apache.org/repos/asf/calcite/blob/a8a8878d/core/src/main/java/org/apache/calcite/rel/rel2sql/package-info.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/package-info.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/package-info.java new file mode 100644 index 0000000..8339ac5 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/package-info.java @@ -0,0 +1,26 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you 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. + */ + +/** + * Translates a relational expression to SQL parse tree. + */ +@PackageMarker +package org.apache.calcite.rel.rel2sql; + +import org.apache.calcite.avatica.util.PackageMarker; + +// End package-info.java http://git-wip-us.apache.org/repos/asf/calcite/blob/a8a8878d/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java new file mode 100644 index 0000000..33858fa --- /dev/null +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -0,0 +1,347 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you 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 org.apache.calcite.rel.rel2sql; + +import org.apache.calcite.plan.RelTraitDef; +import org.apache.calcite.rel.RelNode; +import org.apache.calcite.schema.SchemaPlus; +import org.apache.calcite.sql.SqlDialect; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.parser.SqlParser; +import org.apache.calcite.test.CalciteAssert; +import org.apache.calcite.tools.FrameworkConfig; +import org.apache.calcite.tools.Frameworks; +import org.apache.calcite.tools.Planner; +import org.apache.calcite.tools.Program; + +import org.junit.Ignore; +import org.junit.Test; + +import java.util.List; + +import static org.hamcrest.CoreMatchers.equalTo; +import static org.junit.Assert.assertThat; +import static org.junit.Assert.assertTrue; + +/** + * Tests for utility {@link RelToSqlConverter} + */ +public class RelToSqlConverterTest { + private Planner logicalPlanner = getPlanner(null); + + private void checkRel2Sql(Planner planner, String query, String expectedQeury) { + try { + SqlNode parse = planner.parse(query); + SqlNode validate = planner.validate(parse); + RelNode rel = planner.rel(validate).rel; + final RelToSqlConverter converter = + new RelToSqlConverter(SqlDialect.CALCITE); + final SqlNode sqlNode = converter.visitChild(0, rel).asQuery(); + assertThat(sqlNode.toSqlString(SqlDialect.CALCITE).getSql(), + equalTo(expectedQeury)); + } catch (Exception e) { + assertTrue("Parsing failed throwing error: " + e.getMessage(), false); + } + } + + private Planner getPlanner(List traitDefs, Program... programs) { + return getPlanner(traitDefs, SqlParser.Config.DEFAULT, programs); + } + + private Planner getPlanner(List traitDefs, + SqlParser.Config parserConfig, + Program... programs) { + final SchemaPlus rootSchema = Frameworks.createRootSchema(true); + final FrameworkConfig config = Frameworks.newConfigBuilder() + .parserConfig(parserConfig) + .defaultSchema( + CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.JDBC_FOODMART)) + .traitDefs(traitDefs) + .programs(programs) + .build(); + return Frameworks.getPlanner(config); + } + + @Test + public void testSimpleSelectStarFromProductTable() { + String query = "select * from \"product\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT *\nFROM \"foodmart\".\"product\""); + } + + + @Test + public void testSimpleSelectQueryFromProductTable() { + String query = "select \"product_id\", \"product_class_id\" from \"product\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\", \"product_class_id\"\n" + + "FROM \"foodmart\".\"product\""); + } + + //TODO: add test for query -> select * from product + + @Test + public void testSelectQueryWithWhereClauseOfLessThan() { + String query = + "select \"product_id\", \"shelf_width\" from \"product\" where \"product_id\" < 10"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\", \"shelf_width\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"product_id\" < 10"); + } + + @Test + public void testSelectQueryWithWhereClauseOfBasicOperators() { + String query = "select * from \"product\" " + + "where (\"product_id\" = 10 OR \"product_id\" <= 5) " + + "AND (80 >= \"shelf_width\" OR \"shelf_width\" > 30)"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT *\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE (\"product_id\" = 10 OR \"product_id\" <= 5) " + + "AND (80 >= \"shelf_width\" OR \"shelf_width\" > 30)"); + } + + + @Test + public void testSelectQueryWithGroupBy() { + String query = "select count(*) from \"product\" group by \"product_class_id\", \"product_id\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT COUNT(*)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\", \"product_id\""); + } + + @Test + public void testSelectQueryWithMinAggregateFunction() { + String query = "select min(\"net_weight\") from \"product\" group by \"product_class_id\" "; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT MIN(\"net_weight\")\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""); + } + + @Test + public void testSelectQueryWithMinAggregateFunction1() { + String query = "select \"product_class_id\", min(\"net_weight\") from" + + " \"product\" group by \"product_class_id\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_class_id\", MIN(\"net_weight\")\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""); + } + + @Test + public void testSelectQueryWithSumAggregateFunction() { + String query = + "select sum(\"net_weight\") from \"product\" group by \"product_class_id\" "; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT SUM(\"net_weight\")\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""); + } + + @Test + public void testSelectQueryWithMultipleAggregateFunction() { + String query = + "select sum(\"net_weight\"), min(\"low_fat\"), count(*)" + + " from \"product\" group by \"product_class_id\" "; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT SUM(\"net_weight\"), MIN(\"low_fat\"), COUNT(*)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""); + } + + @Test + public void testSelectQueryWithMultipleAggregateFunction1() { + String query = + "select \"product_class_id\", sum(\"net_weight\"), min(\"low_fat\"), count(*)" + + " from \"product\" group by \"product_class_id\" "; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_class_id\", SUM(\"net_weight\"), MIN(\"low_fat\"), COUNT(*)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\"" + ); + } + + @Test + public void testSelectQueryWithGroupByAndProjectList() { + String query = + "select \"product_class_id\", \"product_id\", count(*) from \"product\" group " + + "by \"product_class_id\", \"product_id\" "; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_class_id\", \"product_id\", COUNT(*)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\", \"product_id\""); + } + + @Test + public void testSelectQueryWithGroupByAndProjectList1() { + String query = + "select count(*) from \"product\" group by \"product_class_id\", \"product_id\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT COUNT(*)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\", \"product_id\""); + } + + @Test + public void testSelectQueryWithGroupByHaving() { + String query = "select count(*) from \"product\" group by \"product_class_id\"," + + " \"product_id\" having \"product_id\" > 10"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT COUNT(*)\n" + + "FROM (SELECT \"product_class_id\", \"product_id\", COUNT(*)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\", \"product_id\") AS \"t0\"\n" + + "WHERE \"product_id\" > 10"); + } + + @Test + public void testSelectQueryWithOrderByClause() { + String query = "select \"product_id\" from \"product\" order by \"net_weight\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\", \"net_weight\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY \"net_weight\""); + } + + @Test + public void testSelectQueryWithOrderByClause1() { + String query = + "select \"product_id\", \"net_weight\" from \"product\" order by \"net_weight\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\", \"net_weight\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY \"net_weight\""); + } + + @Test + public void testSelectQueryWithTwoOrderByClause() { + String query = + "select \"product_id\" from \"product\" order by \"net_weight\", \"gross_weight\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\", \"net_weight\", \"gross_weight\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY \"net_weight\", \"gross_weight\""); + } + + @Test + public void testSelectQueryWithAscDescOrderByClause() { + String query = + "select \"product_id\" from \"product\" order by \"net_weight\" asc, " + + "\"gross_weight\" desc, \"low_fat\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\", \"net_weight\", \"gross_weight\", \"low_fat\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY \"net_weight\", \"gross_weight\" DESC, \"low_fat\""); + } + + @Ignore + @Test + public void testSelectQueryWithLimitClause() { + String query = "select \"product_id\" from \"product\" limit 100 offset 10"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "LIMIT 100 OFFSET 10"); + } + + @Test + public void testSelectQueryComplex() { + String query = + "select count(*), \"units_per_case\" from \"product\" where \"cases_per_pallet\" > 100 " + + "group by \"product_id\", \"units_per_case\" order by \"units_per_case\" desc"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT COUNT(*), \"units_per_case\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"cases_per_pallet\" > 100\n" + + "GROUP BY \"product_id\", \"units_per_case\"\n" + + "ORDER BY \"units_per_case\" DESC"); + } + + @Test + public void testSelectQueryWithGroup() { + String query = + "select count(*), sum(\"employee_id\") from \"reserve_employee\" " + + "where \"hire_date\" > '2015-01-01' " + + "and (\"position_title\" = 'SDE' or \"position_title\" = 'SDM') " + + "group by \"store_id\", \"position_title\""; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT COUNT(*), SUM(\"employee_id\")\n" + + "FROM \"foodmart\".\"reserve_employee\"\n" + + "WHERE \"hire_date\" > '2015-01-01' " + + "AND (\"position_title\" = 'SDE' OR \"position_title\" = 'SDM')\n" + + "GROUP BY \"store_id\", \"position_title\""); + } + + @Test + public void testSimpleJoin() { + String query = "select *\n" + + "from \"sales_fact_1997\" as s\n" + + " join \"customer\" as c using (\"customer_id\")\n" + + " join \"product\" as p using (\"product_id\")\n" + + " join \"product_class\" as pc using (\"product_class_id\")\n" + + "where c.\"city\" = 'San Francisco'\n" + + "and pc.\"product_department\" = 'Snacks'\n"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT *\nFROM \"foodmart\".\"sales_fact_1997\"\n" + + "INNER JOIN \"foodmart\".\"customer\" " + + "ON \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n" + + "INNER JOIN \"foodmart\".\"product\" " + + "ON \"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\"\n" + + "INNER JOIN \"foodmart\".\"product_class\" " + + "ON \"product\".\"product_class_id\" = \"product_class\".\"product_class_id\"\n" + + "WHERE \"customer\".\"city\" = 'San Francisco' AND " + + "\"product_class\".\"product_department\" = 'Snacks'" + ); + } + + @Test public void testSimpleIn() { + String query = "select * from \"department\" where \"department_id\" in (\n" + + " select \"department_id\" from \"employee\"\n" + + " where \"store_id\" < 150)"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"department\".\"department_id\", \"department\".\"department_description\"\n" + + "FROM \"foodmart\".\"department\"\nINNER JOIN " + + "(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\"\n" + + "WHERE \"store_id\" < 150\nGROUP BY \"department_id\") AS \"t1\" " + + "ON \"department\".\"department_id\" = \"t1\".\"department_id\""); + } +}