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 <[email protected]> Authored: Fri Jul 28 10:38:12 2017 +0200 Committer: Julian Hyde <[email protected]> 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) {
