You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/08/04 02:18:42 UTC
calcite git commit: [CALCITE-1895] MSSQL's SUBSTRING operator has
different syntax (Chris Baynes)
Repository: calcite
Updated Branches:
refs/heads/master 3d88b254a -> 84b49a9c1
[CALCITE-1895] MSSQL's SUBSTRING operator has different syntax (Chris Baynes)
Close apache/calcite#504
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/84b49a9c
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/84b49a9c
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/84b49a9c
Branch: refs/heads/master
Commit: 84b49a9c1e0436bc61fba1c5d499492fddb31ebd
Parents: 3d88b25
Author: Chris Baynes <bi...@gmail.com>
Authored: Fri Jul 28 10:38:12 2017 +0200
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Aug 3 16:40:46 2017 -0700
----------------------------------------------------------------------
.../calcite/sql/dialect/MssqlHandler.java | 37 ++++++++++++++------
.../rel/rel2sql/RelToSqlConverterTest.java | 29 +++++++++++----
2 files changed, 49 insertions(+), 17 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/84b49a9c/core/src/main/java/org/apache/calcite/sql/dialect/MssqlHandler.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlHandler.java b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlHandler.java
index 96f4a89..31aa287 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlHandler.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlHandler.java
@@ -19,8 +19,14 @@ package org.apache.calcite.sql.dialect;
import org.apache.calcite.avatica.util.TimeUnitRange;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.ReturnTypes;
/**
* Defines how a SQL parse tree should be unparsed to SQL
@@ -31,21 +37,32 @@ import org.apache.calcite.sql.SqlWriter;
*/
public class MssqlHandler extends SqlDialect.BaseHandler {
public static final MssqlHandler INSTANCE = new MssqlHandler();
+ public static final SqlFunction MSSQL_SUBSTRING =
+ new SqlFunction("SUBSTRING", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.ARG0_NULLABLE_VARYING, null, null,
+ SqlFunctionCategory.STRING);
@Override public void unparseCall(SqlWriter writer, SqlCall call,
int leftPrec, int rightPrec) {
- switch (call.getKind()) {
- case FLOOR:
- if (call.operandCount() != 2) {
- super.unparseCall(writer, call, leftPrec, rightPrec);
- return;
+ if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) {
+ if (call.operandCount() != 3) {
+ throw new IllegalArgumentException("MSSQL SUBSTRING requires FROM and FOR arguments");
}
+ SqlUtil.unparseFunctionSyntax(MSSQL_SUBSTRING, writer, call);
- unparseFloor(writer, call);
- break;
+ } else {
+ switch (call.getKind()) {
+ case FLOOR:
+ if (call.operandCount() != 2) {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ return;
+ }
+ unparseFloor(writer, call);
+ break;
- default:
- super.unparseCall(writer, call, leftPrec, rightPrec);
+ default:
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ }
}
}
@@ -88,7 +105,7 @@ public class MssqlHandler extends SqlDialect.BaseHandler {
unparseFloorWithUnit(writer, call, 19, ":00");
break;
default:
- throw new AssertionError("MSSQL does not support FLOOR for time unit: "
+ throw new IllegalArgumentException("MSSQL does not support FLOOR for time unit: "
+ unit);
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/84b49a9c/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
index 274f559..82fee32 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -48,6 +48,8 @@ import org.junit.Test;
import java.util.List;
+import junit.framework.AssertionFailedError;
+
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
@@ -794,8 +796,6 @@ public class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"";
final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2)\n"
+ "FROM `foodmart`.`product`";
- final String expectedMssql = "SELECT SUBSTRING([brand_name] FROM 2)\n"
- + "FROM [foodmart].[product]";
sql(query)
.dialect(DatabaseProduct.ORACLE.getDialect())
.ok(expectedOracle)
@@ -804,7 +804,8 @@ public class RelToSqlConverterTest {
.dialect(DatabaseProduct.MYSQL.getDialect())
.ok(expectedMysql)
.dialect(DatabaseProduct.MSSQL.getDialect())
- .ok(expectedMssql);
+ // mssql does not support this syntax and so should fail
+ .throws_("MSSQL SUBSTRING requires FROM and FOR arguments");
}
@Test public void testSubstringWithFor() {
@@ -816,7 +817,7 @@ public class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"";
final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2 FOR 3)\n"
+ "FROM `foodmart`.`product`";
- final String expectedMssql = "SELECT SUBSTRING([brand_name] FROM 2 FOR 3)\n"
+ final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n"
+ "FROM [foodmart].[product]";
sql(query)
.dialect(DatabaseProduct.ORACLE.getDialect())
@@ -1831,6 +1832,22 @@ public class RelToSqlConverterTest {
}
Sql ok(String expectedQuery) {
+ assertThat(exec(), is(expectedQuery));
+ return this;
+ }
+
+ Sql throws_(String errorMessage) {
+ try {
+ final String s = exec();
+ throw new AssertionFailedError("Expected exception with message `"
+ + errorMessage + "` but nothing was thrown; got " + s);
+ } catch (Exception e) {
+ assertThat(e.getMessage(), is(errorMessage));
+ return this;
+ }
+ }
+
+ String exec() {
final Planner planner =
getPlanner(null, SqlParser.Config.DEFAULT, schemaSpec);
try {
@@ -1843,14 +1860,12 @@ public class RelToSqlConverterTest {
final RelToSqlConverter converter =
new RelToSqlConverter(dialect);
final SqlNode sqlNode = converter.visitChild(0, rel).asStatement();
- assertThat(Util.toLinux(sqlNode.toSqlString(dialect).getSql()),
- is(expectedQuery));
+ return Util.toLinux(sqlNode.toSqlString(dialect).getSql());
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
throw new RuntimeException(e);
}
- return this;
}
public Sql schema(CalciteAssert.SchemaSpec schemaSpec) {