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 869E718CAB for ; Fri, 31 Jul 2015 22:42:45 +0000 (UTC) Received: (qmail 83488 invoked by uid 500); 31 Jul 2015 22:42:45 -0000 Delivered-To: apmail-calcite-commits-archive@calcite.apache.org Received: (qmail 83456 invoked by uid 500); 31 Jul 2015 22:42:45 -0000 Mailing-List: contact commits-help@calcite.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@calcite.incubator.apache.org Delivered-To: mailing list commits@calcite.incubator.apache.org Received: (qmail 83447 invoked by uid 99); 31 Jul 2015 22:42:45 -0000 Received: from Unknown (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 31 Jul 2015 22:42:45 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 06200D963A for ; Fri, 31 Jul 2015 22:42:45 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 0.352 X-Spam-Level: X-Spam-Status: No, score=0.352 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RP_MATCHES_RCVD=-1.428] autolearn=disabled Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id 6rckO2mFJIoW for ; Fri, 31 Jul 2015 22:42:44 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with SMTP id 1D87B24E0F for ; Fri, 31 Jul 2015 22:42:39 +0000 (UTC) Received: (qmail 82929 invoked by uid 99); 31 Jul 2015 22:42:39 -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; Fri, 31 Jul 2015 22:42:39 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id ED330E050A; Fri, 31 Jul 2015 22:42:38 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: jhyde@apache.org To: commits@calcite.incubator.apache.org Date: Fri, 31 Jul 2015 22:43:22 -0000 Message-Id: In-Reply-To: <242ee1ce99114d64a00e15c744d2b400@git.apache.org> References: <242ee1ce99114d64a00e15c744d2b400@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [45/50] [abbrv] incubator-calcite git commit: [CALCITE-259] Using sub-queries in CASE statement against JDBC tables generates invalid Oracle SQL (Yeong Wei) [CALCITE-259] Using sub-queries in CASE statement against JDBC tables generates invalid Oracle SQL (Yeong Wei) Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/01b2d057 Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/01b2d057 Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/01b2d057 Branch: refs/heads/branch-release Commit: 01b2d057c329165f1180c53a8d8329b1b225447f Parents: 3fd4fa5 Author: Julian Hyde Authored: Sun May 17 21:49:06 2015 -0700 Committer: Julian Hyde Committed: Sun May 17 21:49:06 2015 -0700 ---------------------------------------------------------------------- .../apache/calcite/adapter/jdbc/JdbcRules.java | 99 ++++++++++++++++++-- .../apache/calcite/test/JdbcAdapterTest.java | 10 ++ 2 files changed, 101 insertions(+), 8 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/01b2d057/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java index c75bcd4..fc5cab5 100644 --- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java +++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java @@ -71,6 +71,7 @@ import org.apache.calcite.schema.ModifiableTable; 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.SqlCall; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlFunction; @@ -84,6 +85,8 @@ import org.apache.calcite.sql.SqlNodeList; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.SqlSelect; 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.parser.SqlParserPos; import org.apache.calcite.sql.type.InferTypes; @@ -684,11 +687,14 @@ public class JdbcRules { List aggCalls) throws InvalidRelException { super(cluster, traitSet, input, indicator, groupSet, groupSets, aggCalls); + Convention convention = getConvention(); + assert convention instanceof JdbcConvention; assert getConvention() instanceof JdbcConvention; assert this.groupSets.size() == 1 : "Grouping sets not supported"; assert !this.indicator; for (AggregateCall aggCall : aggCalls) { - if (!canImplement(aggCall.getAggregation())) { + if (!canImplement(aggCall.getAggregation(), + ((JdbcConvention) convention).dialect)) { throw new InvalidRelException("cannot implement aggregate function " + aggCall.getAggregation()); } @@ -697,12 +703,23 @@ public class JdbcRules { /** Returns whether this JDBC data source can implement a given aggregate * function. */ - private boolean canImplement(SqlAggFunction aggregation) { - return Arrays.asList(SqlStdOperatorTable.COUNT, - SqlStdOperatorTable.SUM, - SqlStdOperatorTable.SUM0, - SqlStdOperatorTable.MIN, - SqlStdOperatorTable.MAX).contains(aggregation); + private boolean canImplement(SqlAggFunction aggregation, + SqlDialect sqlDialect) { + List stdAggFuncs = new ArrayList(); + stdAggFuncs.add(SqlStdOperatorTable.COUNT); + stdAggFuncs.add(SqlStdOperatorTable.SUM); + stdAggFuncs.add(SqlStdOperatorTable.SUM0); + stdAggFuncs.add(SqlStdOperatorTable.MIN); + stdAggFuncs.add(SqlStdOperatorTable.MAX); + + switch(sqlDialect.getDatabaseProduct()) { + case MYSQL: + stdAggFuncs.add(SqlStdOperatorTable.SINGLE_VALUE); + break; + default: + break; + } + return stdAggFuncs.contains(aggregation); } @Override public JdbcAggregate copy(RelTraitSet traitSet, RelNode input, @@ -731,7 +748,13 @@ public class JdbcRules { groupByList.add(field); } for (AggregateCall aggCall : aggCalls) { - addSelect(selectList, builder.context.toSql(aggCall), getRowType()); + SqlNode aggCallSqlNode = builder.context.toSql(aggCall); + if (aggCall.getAggregation() instanceof SqlSingleValueAggFunction) { + aggCallSqlNode = + rewriteSingleValueExpr(aggCallSqlNode, + builder.result().node, implementor.dialect); + } + addSelect(selectList, aggCallSqlNode, getRowType()); } builder.setSelect(new SqlNodeList(selectList, POS)); if (!groupByList.isEmpty() || aggCalls.isEmpty()) { @@ -741,6 +764,66 @@ public class JdbcRules { } return builder.result(); } + + /** Rewrite SINGLE_VALUE into expression based on database variants + * E.g. HSQLDB, MYSQL, ORACLE, etc + */ + private SqlNode rewriteSingleValueExpr(SqlNode aggCall, + SqlNode resultNode, SqlDialect sqlDialect) { + + SqlNode aggregatee = ((SqlBasicCall) aggCall).operand(0); + SqlNode caseOperand = null; + SqlNode elseExpr = null; + SqlNode countAggregatee = SqlStdOperatorTable + .COUNT.createCall(POS, Arrays.asList(aggregatee)); + SqlNode asterisk = new SqlIdentifier(Arrays.asList("*"), POS); + SqlNode derivedTable = null; + + switch(sqlDialect.getDatabaseProduct()) { + case MYSQL: + caseOperand = countAggregatee; + + SqlNode nullValue = SqlLiteral.createNull(POS); + SqlSelect selectNull = new SqlSelect( + POS, SqlNodeList.EMPTY, + new SqlNodeList(Arrays.asList(nullValue), POS), null, null, + null, null, SqlNodeList.EMPTY, null, null, null); + + SqlCall unionAll = SqlStdOperatorTable.UNION_ALL + .createCall(POS, selectNull, selectNull); + + SqlIdentifier derivedTableAlias = new SqlIdentifier("tbl", POS); + derivedTable = + SqlStdOperatorTable.AS.createCall(POS, Arrays.asList( + new SqlNodeList(Arrays.asList(unionAll), POS), + derivedTableAlias)); + + elseExpr = new SqlSelect( + POS, SqlNodeList.EMPTY, + new SqlNodeList(Arrays.asList(asterisk), POS), derivedTable, null, + null, null, SqlNodeList.EMPTY, null, null, null); + break; + default: + LOGGER.fine("SINGLE_VALUE rewrite not supported for " + + sqlDialect.getDatabaseProduct()); + return aggCall; + } + + SqlNodeList whenList = new SqlNodeList(Arrays.asList( + SqlLiteral.createExactNumeric("0", POS), + SqlLiteral.createExactNumeric("1", POS)), POS); + + SqlNodeList thenList = new SqlNodeList(Arrays.asList( + SqlLiteral.createNull(POS), + aggregatee), POS); + + SqlNode caseExpr = + new SqlCase(POS, caseOperand, whenList, thenList, elseExpr); + + LOGGER.fine("SINGLE_VALUE rewritten into [" + caseExpr + "]"); + + return caseExpr; + } } /** http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/01b2d057/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java index e7faeef..326d6ba 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java @@ -331,6 +331,16 @@ public class JdbcAdapterTest { rs.close(); calciteConnection.close(); } + + @Test(expected = RuntimeException.class) + public void testSubQueryWithSingleValue() { + String sql = "SELECT \"full_name\" FROM \"employee\" WHERE " + + "\"employee_id\" = (SELECT \"employee_id\" FROM \"salary\")"; + CalciteAssert.model(JdbcTest.FOODMART_MODEL).query(sql) + .explainContains("SINGLE_VALUE") + .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.MYSQL) + .runs(); + } } // End JdbcAdapterTest.java