[CALCITE-2188] JDBC adapter generates invalid SQL for DATE/INTERVAL arithmetic (Rahul Raj)
Close apache/calcite#641 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/5bf7b9a4 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/5bf7b9a4 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/5bf7b9a4 Branch: refs/heads/master Commit: 5bf7b9a4d7b54778bbbcb71ed77f15a88b6b9680 Parents: 499f0c6 Author: Rahul Raj <[email protected]> Authored: Sun Mar 4 10:56:26 2018 +0530 Committer: Julian Hyde <[email protected]> Committed: Sat Mar 24 15:58:01 2018 -0700 ---------------------------------------------------------------------- .../java/org/apache/calcite/sql/SqlDialect.java | 79 +++++++++++++++++ .../apache/calcite/sql/SqlIntervalLiteral.java | 8 +- .../calcite/sql/SqlIntervalQualifier.java | 52 ++--------- .../calcite/sql/dialect/Db2SqlDialect.java | 54 ++++++++++++ .../calcite/sql/dialect/MssqlSqlDialect.java | 68 +++++++++++++++ .../calcite/sql/dialect/MysqlSqlDialect.java | 60 +++++++++++++ .../sql/fun/SqlDatetimePlusOperator.java | 85 ++++++++++++++++++ .../sql/fun/SqlDatetimeSubtractionOperator.java | 13 ++- .../calcite/sql/fun/SqlStdOperatorTable.java | 27 +----- .../apache/calcite/sql/type/OperandTypes.java | 2 +- .../rel/rel2sql/RelToSqlConverterTest.java | 92 ++++++++++++++++++++ .../apache/calcite/test/SqlValidatorTest.java | 2 +- 12 files changed, 453 insertions(+), 89 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/SqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index 07bc60a..c0ebbb9 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -17,10 +17,12 @@ package org.apache.calcite.sql; import org.apache.calcite.avatica.util.DateTimeUtils; +import org.apache.calcite.avatica.util.TimeUnit; import org.apache.calcite.config.NullCollation; import org.apache.calcite.linq4j.function.Experimental; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.sql.dialect.AnsiSqlDialect; import org.apache.calcite.sql.dialect.CalciteSqlDialect; import org.apache.calcite.sql.dialect.JethroDataSqlDialect; @@ -338,6 +340,83 @@ public class SqlDialect { writer.literal(literal.toString()); } + public void unparseSqlDatetimeArithmetic(SqlWriter writer, + SqlCall call, SqlKind sqlKind, int leftPrec, int rightPrec) { + final SqlWriter.Frame frame = writer.startList("(", ")"); + call.operand(0).unparse(writer, leftPrec, rightPrec); + writer.sep((SqlKind.PLUS == sqlKind) ? "+" : "-"); + call.operand(1).unparse(writer, leftPrec, rightPrec); + writer.endList(frame); + //Only two parameters are present normally + //Checking parameter count to prevent errors + if (call.getOperandList().size() > 2) { + call.operand(2).unparse(writer, leftPrec, rightPrec); + } + } + + /** Converts an interval qualifier to a SQL string. The default implementation + * returns strings such as + * <code>INTERVAL '1 2:3:4' DAY(4) TO SECOND(4)</code>. */ + public void unparseSqlIntervalQualifier(SqlWriter writer, + SqlIntervalQualifier qualifier, RelDataTypeSystem typeSystem) { + final String start = qualifier.timeUnitRange.startUnit.name(); + final int fractionalSecondPrecision = + qualifier.getFractionalSecondPrecision(typeSystem); + final int startPrecision = qualifier.getStartPrecision(typeSystem); + if (qualifier.timeUnitRange.startUnit == TimeUnit.SECOND) { + if (!qualifier.useDefaultFractionalSecondPrecision()) { + final SqlWriter.Frame frame = writer.startFunCall(start); + writer.print(startPrecision); + writer.sep(",", true); + writer.print(qualifier.getFractionalSecondPrecision(typeSystem)); + writer.endList(frame); + } else if (!qualifier.useDefaultStartPrecision()) { + final SqlWriter.Frame frame = writer.startFunCall(start); + writer.print(startPrecision); + writer.endList(frame); + } else { + writer.keyword(start); + } + } else { + if (!qualifier.useDefaultStartPrecision()) { + final SqlWriter.Frame frame = writer.startFunCall(start); + writer.print(startPrecision); + writer.endList(frame); + } else { + writer.keyword(start); + } + + if (null != qualifier.timeUnitRange.endUnit) { + writer.keyword("TO"); + final String end = qualifier.timeUnitRange.endUnit.name(); + if ((TimeUnit.SECOND == qualifier.timeUnitRange.endUnit) + && (!qualifier.useDefaultFractionalSecondPrecision())) { + final SqlWriter.Frame frame = writer.startFunCall(end); + writer.print(fractionalSecondPrecision); + writer.endList(frame); + } else { + writer.keyword(end); + } + } + } + } + + /** Converts an interval literal to a SQL string. The default implementation + * returns strings such as + * <code>INTERVAL '1 2:3:4' DAY(4) TO SECOND(4)</code>. */ + public void unparseSqlIntervalLiteral(SqlWriter writer, + SqlIntervalLiteral literal, int leftPrec, int rightPrec) { + SqlIntervalLiteral.IntervalValue interval = + (SqlIntervalLiteral.IntervalValue) literal.getValue(); + writer.keyword("INTERVAL"); + if (interval.getSign() == -1) { + writer.print("-"); + } + writer.literal("'" + literal.getValue().toString() + "'"); + unparseSqlIntervalQualifier(writer, interval.getIntervalQualifier(), + RelDataTypeSystem.DEFAULT); + } + /** * Returns whether the string contains any characters outside the * comfortable 7-bit ASCII range (32 through 127). http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/SqlIntervalLiteral.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlIntervalLiteral.java b/core/src/main/java/org/apache/calcite/sql/SqlIntervalLiteral.java index 246e673..ebd51d8 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlIntervalLiteral.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlIntervalLiteral.java @@ -72,13 +72,7 @@ public class SqlIntervalLiteral extends SqlLiteral { SqlWriter writer, int leftPrec, int rightPrec) { - IntervalValue interval = (IntervalValue) value; - writer.keyword("INTERVAL"); - if (interval.getSign() == -1) { - writer.print("-"); - } - writer.literal("'" + value.toString() + "'"); - writer.keyword(interval.intervalQualifier.toString()); + writer.getDialect().unparseSqlIntervalLiteral(writer, this, leftPrec, rightPrec); } @SuppressWarnings("deprecation") http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java index 9e27f56..096b68c 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java @@ -202,7 +202,8 @@ public class SqlIntervalQualifier extends SqlNode { return startPrecision; } - private boolean useDefaultStartPrecision() { + /** Returns {@code true} if start precision is not specified. */ + public boolean useDefaultStartPrecision() { return startPrecision == RelDataType.PRECISION_NOT_SPECIFIED; } @@ -250,7 +251,8 @@ public class SqlIntervalQualifier extends SqlNode { } } - private boolean useDefaultFractionalSecondPrecision() { + /** Returns {@code true} if fractional second precision is not specified. */ + public boolean useDefaultFractionalSecondPrecision() { return fractionalSecondPrecision == RelDataType.PRECISION_NOT_SPECIFIED; } @@ -305,50 +307,8 @@ public class SqlIntervalQualifier extends SqlNode { SqlWriter writer, int leftPrec, int rightPrec) { - unparse(RelDataTypeSystem.DEFAULT, writer); - } - - public void unparse(RelDataTypeSystem typeSystem, SqlWriter writer) { - final String start = timeUnitRange.startUnit.name(); - final int fractionalSecondPrecision = - getFractionalSecondPrecision(typeSystem); - final int startPrecision = getStartPrecision(typeSystem); - if (timeUnitRange.startUnit == TimeUnit.SECOND) { - if (!useDefaultFractionalSecondPrecision()) { - final SqlWriter.Frame frame = writer.startFunCall(start); - writer.print(startPrecision); - writer.sep(",", true); - writer.print(getFractionalSecondPrecision(typeSystem)); - writer.endList(frame); - } else if (!useDefaultStartPrecision()) { - final SqlWriter.Frame frame = writer.startFunCall(start); - writer.print(startPrecision); - writer.endList(frame); - } else { - writer.keyword(start); - } - } else { - if (!useDefaultStartPrecision()) { - final SqlWriter.Frame frame = writer.startFunCall(start); - writer.print(startPrecision); - writer.endList(frame); - } else { - writer.keyword(start); - } - - if (null != timeUnitRange.endUnit) { - writer.keyword("TO"); - final String end = timeUnitRange.endUnit.name(); - if ((TimeUnit.SECOND == timeUnitRange.endUnit) - && (!useDefaultFractionalSecondPrecision())) { - final SqlWriter.Frame frame = writer.startFunCall(end); - writer.print(fractionalSecondPrecision); - writer.endList(frame); - } else { - writer.keyword(end); - } - } - } + writer.getDialect() + .unparseSqlIntervalQualifier(writer, this, RelDataTypeSystem.DEFAULT); } /** http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java index b0e6fe4..df965dc 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/Db2SqlDialect.java @@ -16,7 +16,11 @@ */ package org.apache.calcite.sql.dialect; +import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.sql.SqlDialect; +import org.apache.calcite.sql.SqlIntervalLiteral; +import org.apache.calcite.sql.SqlIntervalQualifier; +import org.apache.calcite.sql.SqlWriter; /** * A <code>SqlDialect</code> implementation for the IBM DB2 database. @@ -38,6 +42,56 @@ public class Db2SqlDialect extends SqlDialect { @Override public boolean hasImplicitTableAlias() { return false; } + + @Override public void unparseSqlIntervalQualifier(SqlWriter writer, + SqlIntervalQualifier qualifier, RelDataTypeSystem typeSystem) { + + // DB2 supported qualifiers. Singular form of these keywords are also acceptable. + // YEAR/YEARS + // MONTH/MONTHS + // DAY/DAYS + // HOUR/HOURS + // MINUTE/MINUTES + // SECOND/SECONDS + + switch (qualifier.timeUnitRange) { + case YEAR: + case MONTH: + case DAY: + case HOUR: + case MINUTE: + case SECOND: + case MICROSECOND: + final String timeUnit = qualifier.timeUnitRange.startUnit.name(); + writer.keyword(timeUnit); + break; + default: + throw new AssertionError("Unsupported type: " + qualifier.timeUnitRange); + } + + if (null != qualifier.timeUnitRange.endUnit) { + throw new AssertionError("Unsupported end unit: " + + qualifier.timeUnitRange.endUnit); + } + } + + @Override public void unparseSqlIntervalLiteral(SqlWriter writer, + SqlIntervalLiteral literal, int leftPrec, int rightPrec) { + // A duration is a positive or negative number representing an interval of time. + // If one operand is a date, the other labeled duration of YEARS, MONTHS, or DAYS. + // If one operand is a time, the other must be labeled duration of HOURS, MINUTES, or SECONDS. + // If one operand is a timestamp, the other operand can be any of teh duration. + + SqlIntervalLiteral.IntervalValue interval = + (SqlIntervalLiteral.IntervalValue) literal.getValue(); + if (interval.getSign() == -1) { + writer.print("-"); + } + writer.literal(literal.getValue().toString()); + unparseSqlIntervalQualifier(writer, interval.getIntervalQualifier(), + RelDataTypeSystem.DEFAULT); + } + } // End Db2SqlDialect.java http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java index 3ee35e1..22881de 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java @@ -17,18 +17,24 @@ package org.apache.calcite.sql.dialect; import org.apache.calcite.avatica.util.TimeUnitRange; +import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.sql.SqlAbstractDateTimeLiteral; 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.SqlIntervalLiteral; +import org.apache.calcite.sql.SqlIntervalQualifier; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlLiteral; +import org.apache.calcite.sql.SqlNode; 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; + + /** * A <code>SqlDialect</code> implementation for the Microsoft SQL Server * database. @@ -121,6 +127,68 @@ public class MssqlSqlDialect extends SqlDialect { } } + @Override public void unparseSqlDatetimeArithmetic(SqlWriter writer, + SqlCall call, SqlKind sqlKind, int leftPrec, int rightPrec) { + + final SqlWriter.Frame frame = writer.startFunCall("DATEADD"); + SqlNode operand = call.operand(1); + if (operand instanceof SqlIntervalLiteral) { + //There is no DATESUB method available, so change the sign. + unparseSqlIntervalLiteralMssql( + writer, (SqlIntervalLiteral) operand, sqlKind == SqlKind.MINUS ? -1 : 1); + } else { + operand.unparse(writer, leftPrec, rightPrec); + } + writer.sep(",", true); + + call.operand(0).unparse(writer, leftPrec, rightPrec); + writer.endList(frame); + } + + @Override public void unparseSqlIntervalQualifier(SqlWriter writer, + SqlIntervalQualifier qualifier, RelDataTypeSystem typeSystem) { + switch (qualifier.timeUnitRange) { + case YEAR: + case QUARTER: + case MONTH: + case WEEK: + case DAY: + case HOUR: + case MINUTE: + case SECOND: + case MILLISECOND: + case MICROSECOND: + final String timeUnit = qualifier.timeUnitRange.startUnit.name(); + writer.keyword(timeUnit); + break; + default: + throw new AssertionError("Unsupported type: " + qualifier.timeUnitRange); + } + + if (null != qualifier.timeUnitRange.endUnit) { + throw new AssertionError("End unit is not supported now: " + + qualifier.timeUnitRange.endUnit); + } + } + + @Override public void unparseSqlIntervalLiteral( + SqlWriter writer, SqlIntervalLiteral literal, int leftPrec, int rightPrec) { + unparseSqlIntervalLiteralMssql(writer, literal, 1); + } + + private void unparseSqlIntervalLiteralMssql( + SqlWriter writer, SqlIntervalLiteral literal, int sign) { + SqlIntervalLiteral.IntervalValue interval + = (SqlIntervalLiteral.IntervalValue) literal.getValue(); + unparseSqlIntervalQualifier(writer, interval.getIntervalQualifier(), + RelDataTypeSystem.DEFAULT); + writer.sep(",", true); + if (interval.getSign() * sign == -1) { + writer.print("-"); + } + writer.literal(literal.getValue().toString()); + } + private void unparseFloorWithUnit(SqlWriter writer, SqlCall call, int charLen, String offset) { writer.print("CONVERT"); http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java index 34740c5..bc05165 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java @@ -16,9 +16,11 @@ */ package org.apache.calcite.sql.dialect; +import org.apache.calcite.avatica.util.TimeUnit; import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.config.NullCollation; import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlDataTypeSpec; @@ -26,6 +28,7 @@ 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.SqlIntervalQualifier; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlNode; @@ -210,6 +213,63 @@ public class MysqlSqlDialect extends SqlDialect { writer.print("'" + format + "'"); writer.endList(frame); } + + + @Override public void unparseSqlIntervalQualifier(SqlWriter writer, + SqlIntervalQualifier qualifier, RelDataTypeSystem typeSystem) { + + // Unit Value | Expected Format + // --------------------+------------------------------------------- + // MICROSECOND | MICROSECONDS + // SECOND | SECONDS + // MINUTE | MINUTES + // HOUR | HOURS + // DAY | DAYS + // WEEK | WEEKS + // MONTH | MONTHS + // QUARTER | QUARTERS + // YEAR | YEARS + // MINUTE_SECOND | 'MINUTES:SECONDS' + // HOUR_MINUTE | 'HOURS:MINUTES' + // DAY_HOUR | 'DAYS HOURS' + // YEAR_MONTH | 'YEARS-MONTHS' + // MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' + // HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' + // SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' + // DAY_MINUTE | 'DAYS HOURS:MINUTES' + // DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' + // DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' + // HOUR_SECOND | 'HOURS:MINUTES:SECONDS' + + if (!qualifier.useDefaultFractionalSecondPrecision()) { + throw new AssertionError("Fractional second precision is not supported now "); + } + + final String start = validate(qualifier.timeUnitRange.startUnit).name(); + if (qualifier.timeUnitRange.startUnit == TimeUnit.SECOND + || qualifier.timeUnitRange.endUnit == null) { + writer.keyword(start); + } else { + writer.keyword(start + "_" + qualifier.timeUnitRange.endUnit.name()); + } + } + + private TimeUnit validate(TimeUnit timeUnit) { + switch (timeUnit) { + case MICROSECOND: + case SECOND: + case MINUTE: + case HOUR: + case DAY: + case WEEK: + case MONTH: + case QUARTER: + case YEAR: + return timeUnit; + default: + throw new AssertionError(" Time unit " + timeUnit + "is not supported now."); + } + } } // End MysqlSqlDialect.java http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimePlusOperator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimePlusOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimePlusOperator.java new file mode 100644 index 0000000..c8f2baf --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimePlusOperator.java @@ -0,0 +1,85 @@ +/* + * 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.sql.fun; + +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.sql.SqlOperatorBinding; +import org.apache.calcite.sql.SqlSpecialOperator; +import org.apache.calcite.sql.SqlSyntax; +import org.apache.calcite.sql.SqlWriter; +import org.apache.calcite.sql.type.InferTypes; +import org.apache.calcite.sql.type.IntervalSqlType; +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.validate.SqlMonotonicity; + +/** + * Operator that adds an INTERVAL to a DATETIME. + */ +public class SqlDatetimePlusOperator extends SqlSpecialOperator { + //~ Constructors ----------------------------------------------------------- + + SqlDatetimePlusOperator() { + super("+", SqlKind.PLUS, 40, true, ReturnTypes.ARG2_NULLABLE, + InferTypes.FIRST_KNOWN, OperandTypes.MINUS_DATE_OPERATOR); + } + + //~ Methods ---------------------------------------------------------------- + + @Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) { + final RelDataTypeFactory typeFactory = opBinding.getTypeFactory(); + final RelDataType leftType = opBinding.getOperandType(0); + final IntervalSqlType unitType = + (IntervalSqlType) opBinding.getOperandType(1); + switch (unitType.getIntervalQualifier().getStartUnit()) { + case HOUR: + case MINUTE: + case SECOND: + case MILLISECOND: + case MICROSECOND: + return typeFactory.createTypeWithNullability( + typeFactory.createSqlType(SqlTypeName.TIMESTAMP), + leftType.isNullable() || unitType.isNullable()); + default: + return leftType; + } + } + + + public SqlSyntax getSyntax() { + return SqlSyntax.SPECIAL; + } + + public void unparse( + SqlWriter writer, + SqlCall call, + int leftPrec, + int rightPrec) { + writer.getDialect().unparseSqlDatetimeArithmetic( + writer, call, SqlKind.PLUS, leftPrec, rightPrec); + } + + @Override public SqlMonotonicity getMonotonicity(SqlOperatorBinding call) { + return SqlStdOperatorTable.PLUS.getMonotonicity(call); + } +} + +// End SqlDatetimePlusOperator.java http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java index fd7e772..1b78c12 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatetimeSubtractionOperator.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.sql.fun; + + import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlOperatorBinding; @@ -49,8 +51,7 @@ public class SqlDatetimeSubtractionOperator extends SqlSpecialOperator { 40, true, ReturnTypes.ARG2_NULLABLE, - InferTypes.FIRST_KNOWN, - OperandTypes.MINUS_DATE_OPERATOR); + InferTypes.FIRST_KNOWN, OperandTypes.MINUS_DATE_OPERATOR); } //~ Methods ---------------------------------------------------------------- @@ -64,12 +65,8 @@ public class SqlDatetimeSubtractionOperator extends SqlSpecialOperator { SqlCall call, int leftPrec, int rightPrec) { - final SqlWriter.Frame frame = writer.startList("(", ")"); - call.operand(0).unparse(writer, leftPrec, rightPrec); - writer.sep("-"); - call.operand(1).unparse(writer, leftPrec, rightPrec); - writer.endList(frame); - call.operand(2).unparse(writer, leftPrec, rightPrec); + writer.getDialect().unparseSqlDatetimeArithmetic( + writer, call, SqlKind.MINUS, leftPrec, rightPrec); } @Override public SqlMonotonicity getMonotonicity(SqlOperatorBinding call) { http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java index cfff50f..6281c45 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java @@ -17,8 +17,6 @@ package org.apache.calcite.sql.fun; import org.apache.calcite.avatica.util.TimeUnit; -import org.apache.calcite.rel.type.RelDataType; -import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlAsOperator; import org.apache.calcite.sql.SqlBasicCall; @@ -36,7 +34,6 @@ import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlNumericLiteral; import org.apache.calcite.sql.SqlOperandCountRange; import org.apache.calcite.sql.SqlOperator; -import org.apache.calcite.sql.SqlOperatorBinding; import org.apache.calcite.sql.SqlOverOperator; import org.apache.calcite.sql.SqlPostfixOperator; import org.apache.calcite.sql.SqlPrefixOperator; @@ -51,7 +48,6 @@ import org.apache.calcite.sql.SqlValuesOperator; import org.apache.calcite.sql.SqlWindow; import org.apache.calcite.sql.SqlWriter; import org.apache.calcite.sql.type.InferTypes; -import org.apache.calcite.sql.type.IntervalSqlType; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlOperandCountRanges; @@ -535,28 +531,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { * Infix datetime plus operator, '<code>DATETIME + INTERVAL</code>'. */ public static final SqlSpecialOperator DATETIME_PLUS = - new SqlSpecialOperator("DATETIME_PLUS", SqlKind.PLUS, 40, true, null, - InferTypes.FIRST_KNOWN, OperandTypes.PLUS_OPERATOR) { - @Override public RelDataType - inferReturnType(SqlOperatorBinding opBinding) { - final RelDataTypeFactory typeFactory = opBinding.getTypeFactory(); - final RelDataType leftType = opBinding.getOperandType(0); - final IntervalSqlType unitType = - (IntervalSqlType) opBinding.getOperandType(1); - switch (unitType.getIntervalQualifier().getStartUnit()) { - case HOUR: - case MINUTE: - case SECOND: - case MILLISECOND: - case MICROSECOND: - return typeFactory.createTypeWithNullability( - typeFactory.createSqlType(SqlTypeName.TIMESTAMP), - leftType.isNullable() || unitType.isNullable()); - default: - return leftType; - } - } - }; + new SqlDatetimePlusOperator(); /** * Multiset {@code MEMBER OF}, which returns whether a element belongs to a http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java index 5ef22fb..c619bb1 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java +++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java @@ -480,7 +480,7 @@ public abstract class OperandTypes { // TODO: compatibility check OperandTypes.or(NUMERIC_NUMERIC, INTERVAL_SAME_SAME, DATETIME_INTERVAL); - public static final SqlSingleOperandTypeChecker MINUS_DATE_OPERATOR = + public static final FamilyOperandTypeChecker MINUS_DATE_OPERATOR = new FamilyOperandTypeChecker( ImmutableList.of(SqlTypeFamily.DATETIME, SqlTypeFamily.DATETIME, SqlTypeFamily.DATETIME_INTERVAL), http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/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 afec0c2..88f7886 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 @@ -1099,6 +1099,98 @@ public class RelToSqlConverterTest { .ok(expected); } + @Test public void testUnparseSqlIntervalQualifierDb2() { + String queryDatePlus = "select * from \"employee\" where \"hire_date\" + " + + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; + String expectedDatePlus = "SELECT *\n" + + "FROM foodmart.employee AS employee\n" + + "WHERE (employee.hire_date + 19800 SECOND)" + + " > TIMESTAMP '2005-10-17 00:00:00'"; + + sql(queryDatePlus) + .withDb2() + .ok(expectedDatePlus); + + String queryDateMinus = "select * from \"employee\" where \"hire_date\" - " + + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; + String expectedDateMinus = "SELECT *\n" + + "FROM foodmart.employee AS employee\n" + + "WHERE (employee.hire_date - 19800 SECOND)" + + " > TIMESTAMP '2005-10-17 00:00:00'"; + + sql(queryDateMinus) + .withDb2() + .ok(expectedDateMinus); + } + + @Test public void testUnparseSqlIntervalQualifierMySql() { + final String sql0 = "select * from \"employee\" where \"hire_date\" - " + + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; + final String expect0 = "SELECT *\n" + + "FROM `foodmart`.`employee`\n" + + "WHERE (`hire_date` - INTERVAL '19800' SECOND)" + + " > TIMESTAMP '2005-10-17 00:00:00'"; + sql(sql0).withMysql().ok(expect0); + + final String sql1 = "select * from \"employee\" where \"hire_date\" + " + + "INTERVAL '10' HOUR > TIMESTAMP '2005-10-17 00:00:00' "; + final String expect1 = "SELECT *\n" + + "FROM `foodmart`.`employee`\n" + + "WHERE (`hire_date` + INTERVAL '10' HOUR)" + + " > TIMESTAMP '2005-10-17 00:00:00'"; + sql(sql1).withMysql().ok(expect1); + + final String sql2 = "select * from \"employee\" where \"hire_date\" + " + + "INTERVAL '1-2' year to month > TIMESTAMP '2005-10-17 00:00:00' "; + final String expect2 = "SELECT *\n" + + "FROM `foodmart`.`employee`\n" + + "WHERE (`hire_date` + INTERVAL '1-2' YEAR_MONTH)" + + " > TIMESTAMP '2005-10-17 00:00:00'"; + sql(sql2).withMysql().ok(expect2); + + final String sql3 = "select * from \"employee\" " + + "where \"hire_date\" + INTERVAL '39:12' MINUTE TO SECOND" + + " > TIMESTAMP '2005-10-17 00:00:00' "; + final String expect3 = "SELECT *\n" + + "FROM `foodmart`.`employee`\n" + + "WHERE (`hire_date` + INTERVAL '39:12' MINUTE_SECOND)" + + " > TIMESTAMP '2005-10-17 00:00:00'"; + sql(sql3).withMysql().ok(expect3); + } + + @Test public void testUnparseSqlIntervalQualifierMsSql() { + String queryDatePlus = "select * from \"employee\" where \"hire_date\" +" + + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; + String expectedDatePlus = "SELECT *\n" + + "FROM [foodmart].[employee]\n" + + "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'"; + + sql(queryDatePlus) + .withMssql() + .ok(expectedDatePlus); + + String queryDateMinus = "select * from \"employee\" where \"hire_date\" -" + + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; + String expectedDateMinus = "SELECT *\n" + + "FROM [foodmart].[employee]\n" + + "WHERE DATEADD(SECOND, -19800, [hire_date]) > '2005-10-17 00:00:00'"; + + sql(queryDateMinus) + .withMssql() + .ok(expectedDateMinus); + + String queryDateMinusNegate = "select * from \"employee\" " + + "where \"hire_date\" -INTERVAL '-19800' SECOND(5)" + + " > TIMESTAMP '2005-10-17 00:00:00' "; + String expectedDateMinusNegate = "SELECT *\n" + + "FROM [foodmart].[employee]\n" + + "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'"; + + sql(queryDateMinusNegate) + .withMssql() + .ok(expectedDateMinusNegate); + } + @Test public void testFloorMysqlWeek() { String query = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\""; String expected = "SELECT STR_TO_DATE(DATE_FORMAT(`hire_date` , '%x%v-1'), '%x%v-%w')\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/5bf7b9a4/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 82b7686..32c50e2 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -8548,9 +8548,9 @@ public class SqlValidatorTest extends SqlValidatorTestCase { + "|| left\n" + "\n" + "+ left\n" + + "+ -\n" + "- left\n" + "- -\n" - + "DATETIME_PLUS -\n" + "EXISTS pre\n" + "\n" + "< ALL left\n"
