Further to [CALCITE-1124], add implementation of TIMESTAMPADD, TIMESTAMPDIFF
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/4ac82a30 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/4ac82a30 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/4ac82a30 Branch: refs/heads/master Commit: 4ac82a30b8ee7ae1afcd83c6f3ef687761536f2e Parents: 0b9ea98 Author: Julian Hyde <[email protected]> Authored: Mon Mar 7 21:23:34 2016 -0800 Committer: Julian Hyde <[email protected]> Committed: Mon Mar 7 21:26:49 2016 -0800 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 224 +++++++------- .../calcite/adapter/enumerable/RexImpTable.java | 10 +- .../apache/calcite/sql/SqlJdbcFunctionCall.java | 294 ++++++++----------- .../java/org/apache/calcite/sql/SqlKind.java | 11 + .../apache/calcite/sql/TimestampInterval.java | 47 --- .../calcite/sql/fun/SqlStdOperatorTable.java | 88 +++--- .../sql2rel/StandardConvertletTable.java | 72 ++++- .../calcite/sql/parser/SqlParserTest.java | 18 ++ .../calcite/sql/test/SqlOperatorBaseTest.java | 64 ++-- .../org/apache/calcite/sql/test/SqlTests.java | 90 +----- .../apache/calcite/test/SqlValidatorTest.java | 7 +- site/_docs/reference.md | 37 +-- site/community/index.md | 1 + 13 files changed, 452 insertions(+), 511 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/main/codegen/templates/Parser.jj ---------------------------------------------------------------------- diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 01c35bb..c1df4cb 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -82,7 +82,6 @@ import org.apache.calcite.sql.SqlUtil; import org.apache.calcite.sql.SqlWindow; import org.apache.calcite.sql.SqlWith; import org.apache.calcite.sql.SqlWithItem; -import org.apache.calcite.sql.TimestampInterval; import org.apache.calcite.sql.fun.SqlCase; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.fun.SqlTrimFunction; @@ -3439,60 +3438,29 @@ TimeUnit TimeUnit() : } } -SqlLiteral TimestampInterval() : -{ -SqlLiteral.SqlSymbol symbol = null; -} +TimeUnit TimestampInterval() : +{} { - ( - ( <FRAC_SECOND> | <MICROSECOND> | <SQL_TSI_FRAC_SECOND> | <SQL_TSI_MICROSECOND> ) - { - symbol = TimestampInterval.MICROSECOND; - } - | - ( <SECOND> | <SQL_TSI_SECOND> ) - { - symbol = TimestampInterval.SECOND; - } - | - ( <MINUTE> | <SQL_TSI_MINUTE> ) - { - symbol = TimestampInterval.MINUTE; - } - | - ( <HOUR> | <SQL_TSI_HOUR> ) - { - symbol = TimestampInterval.HOUR; - } - | - ( <DAY> | <SQL_TSI_DAY> ) - { - symbol = TimestampInterval.DAY; - } - | - (<WEEK> | <SQL_TSI_WEEK> ) - { - symbol = TimestampInterval.WEEK; - } - | - ( <MONTH> | <SQL_TSI_MONTH> ) - { - symbol = TimestampInterval.MONTH; - } - | - ( <QUARTER> | <SQL_TSI_QUARTER> ) - { - symbol = TimestampInterval.QUARTER; - } - | - ( <YEAR> | <SQL_TSI_YEAR> ) - { - symbol = TimestampInterval.YEAR; - } - ) - { - return SqlLiteral.createSymbol(symbol, getPos()); - } + <FRAC_SECOND> { return TimeUnit.MICROSECOND; } +| <MICROSECOND> { return TimeUnit.MICROSECOND; } +| <SQL_TSI_FRAC_SECOND> { return TimeUnit.MICROSECOND; } +| <SQL_TSI_MICROSECOND> { return TimeUnit.MICROSECOND; } +| <SECOND> { return TimeUnit.SECOND; } +| <SQL_TSI_SECOND> { return TimeUnit.SECOND; } +| <MINUTE> { return TimeUnit.MINUTE; } +| <SQL_TSI_MINUTE> { return TimeUnit.MINUTE; } +| <HOUR> { return TimeUnit.HOUR; } +| <SQL_TSI_HOUR> { return TimeUnit.HOUR; } +| <DAY> { return TimeUnit.DAY; } +| <SQL_TSI_DAY> { return TimeUnit.DAY; } +| <WEEK> { return TimeUnit.WEEK; } +| <SQL_TSI_WEEK> { return TimeUnit.WEEK; } +| <MONTH> { return TimeUnit.MONTH; } +| <SQL_TSI_MONTH> { return TimeUnit.MONTH; } +| <QUARTER> { return TimeUnit.QUARTER; } +| <SQL_TSI_QUARTER> { return TimeUnit.QUARTER; } +| <YEAR> { return TimeUnit.YEAR; } +| <SQL_TSI_YEAR> { return TimeUnit.YEAR; } } @@ -3962,6 +3930,8 @@ SqlNode BuiltinFunctionCall() : SqlParserPos starPos; SqlParserPos namePos; SqlDataTypeSpec dt; + TimeUnit interval; + SqlNode node; } { //~ FUNCTIONS WITH SPECIAL SYNTAX --------------------------------------- @@ -4202,56 +4172,68 @@ SqlNode BuiltinFunctionCall() : } ) | - ( - <TIMESTAMPADD> - { - pos = getPos(); - SqlLiteral interval; - } - <LPAREN> - interval = TimestampInterval() - { args = startList(interval); } - <COMMA> - e = Expression(ExprContext.ACCEPT_SUBQUERY) - { args.add(e); } - <COMMA> - e = Expression(ExprContext.ACCEPT_SUBQUERY) - { args.add(e); } - <RPAREN> - { - return SqlStdOperatorTable.TIMESTAMP_ADD.createCall( - pos, SqlParserUtil.toNodeArray(args)); - } - ) + node = TimestampAddFunctionCall() { return node; } | - ( - <TIMESTAMPDIFF> - { - pos = getPos(); - SqlLiteral interval; - } - <LPAREN> - interval = TimestampInterval() - { args = startList(interval); } - <COMMA> - e = Expression(ExprContext.ACCEPT_SUBQUERY) - { args.add(e); } - <COMMA> - e = Expression(ExprContext.ACCEPT_SUBQUERY) - { args.add(e); } - <RPAREN> - { - return SqlStdOperatorTable.TIMESTAMP_DIFF.createCall( - pos, SqlParserUtil.toNodeArray(args)); - } - ) + node = TimestampDiffFunctionCall() { return node; } | - { - SqlNode node; + node = ExtendedBuiltinFunctionCall() { return node; } +} + +/** + * Parses a call to TIMESTAMPADD. + */ +SqlCall TimestampAddFunctionCall() : +{ + List<SqlNode> args; + SqlNode e; + SqlParserPos pos; + TimeUnit interval; + SqlNode node; +} +{ + <TIMESTAMPADD> { + pos = getPos(); } - node = ExtendedBuiltinFunctionCall() - { - return node; + <LPAREN> + interval = TimestampInterval() { + args = startList(SqlLiteral.createSymbol(interval, getPos())); + } + <COMMA> + e = Expression(ExprContext.ACCEPT_SUBQUERY) { args.add(e); } + <COMMA> + e = Expression(ExprContext.ACCEPT_SUBQUERY) { args.add(e); } + <RPAREN> { + return SqlStdOperatorTable.TIMESTAMP_ADD.createCall( + pos.plus(getPos()), SqlParserUtil.toNodeArray(args)); + } +} + +/** + * Parses a call to TIMESTAMPDIFF. + */ +SqlCall TimestampDiffFunctionCall() : +{ + List<SqlNode> args; + SqlNode e; + SqlParserPos pos; + TimeUnit interval; + SqlNode node; +} +{ + <TIMESTAMPDIFF> { + pos = getPos(); + } + <LPAREN> + interval = TimestampInterval() { + args = startList(SqlLiteral.createSymbol(interval, getPos())); + } + <COMMA> + e = Expression(ExprContext.ACCEPT_SUBQUERY) { args.add(e); } + <COMMA> + e = Expression(ExprContext.ACCEPT_SUBQUERY) { args.add(e); } + <RPAREN> { + return SqlStdOperatorTable.TIMESTAMP_DIFF.createCall( + pos.plus(getPos()), SqlParserUtil.toNodeArray(args)); } } @@ -4529,25 +4511,37 @@ SqlNode JdbcFunctionCall() : String name; SqlIdentifier id; SqlNodeList args; + SqlCall call; SqlParserPos pos; SqlParserPos starPos; } { + <LBRACE_FN> + { + pos = getPos(); + } ( - <LBRACE_FN> - { - pos = getPos(); + LOOKAHEAD(1) + call = TimestampAddFunctionCall() { + name = call.getOperator().getName(); + args = new SqlNodeList(call.getOperandList(), getPos()); + } + | + call = TimestampDiffFunctionCall() { + name = call.getOperator().getName(); + args = new SqlNodeList(call.getOperandList(), getPos()); } + | ( // INSERT is a reserved word, but we need to handle {fn insert} <INSERT> { name = unquotedIdentifier(); } - | + | // For cases like {fn power(1,2)} and {fn lower('a')} id = ReservedFunctionName() { name = id.getSimple(); } - | + | // For cases like {fn substring('foo', 1,2)} name = NonReservedJdbcFunctionName() - | + | name = Identifier() ) ( @@ -4556,16 +4550,16 @@ SqlNode JdbcFunctionCall() : args = new SqlNodeList(starPos); args.add(SqlIdentifier.star(starPos)); } - | LOOKAHEAD(2) <LPAREN> <RPAREN> - { args = new SqlNodeList(pos); } - | args = ParenthesizedQueryOrCommaList(ExprContext.ACCEPT_SUBQUERY) + | + LOOKAHEAD(2) <LPAREN> <RPAREN> { args = new SqlNodeList(pos); } + | + args = ParenthesizedQueryOrCommaList(ExprContext.ACCEPT_SUBQUERY) ) - <RBRACE> - { - return new SqlJdbcFunctionCall(name).createCall( - pos.plus(getPos()), SqlParserUtil.toNodeArray(args)); - } ) + <RBRACE> { + return new SqlJdbcFunctionCall(name).createCall( + pos.plus(getPos()), SqlParserUtil.toNodeArray(args)); + } } /** http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java index 2633490..e03f6b8 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java @@ -142,6 +142,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MAP_VALUE_CONSTRUCT import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MAX; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MIN; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MINUS; +import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MINUS_DATE; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MOD; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.MULTIPLY; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NEXT_VALUE; @@ -245,6 +246,8 @@ public class RexImpTable { // datetime defineImplementor(DATETIME_PLUS, NullPolicy.STRICT, new DatetimeArithmeticImplementor(), false); + defineImplementor(MINUS_DATE, NullPolicy.STRICT, + new DatetimeArithmeticImplementor(), false); defineMethod(EXTRACT_DATE, BuiltInMethod.UNIX_DATE_EXTRACT.method, NullPolicy.STRICT); defineImplementor(FLOOR, NullPolicy.STRICT, @@ -1930,7 +1933,12 @@ public class RexImpTable { trop1 = Expressions.convert_(trop1, int.class); break; } - return Expressions.add(trop0, trop1); + switch (call.getKind()) { + case MINUS: + return Expressions.subtract(trop0, trop1); + default: + return Expressions.add(trop0, trop1); + } } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java index f6849b7..77bb478 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java @@ -23,9 +23,10 @@ import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.validate.SqlValidator; import org.apache.calcite.sql.validate.SqlValidatorScope; -import org.apache.calcite.util.Util; -import java.util.HashMap; +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableMap; + import java.util.Map; import static org.apache.calcite.util.Static.RESOURCE; @@ -435,13 +436,13 @@ public class SqlJdbcFunctionCall extends SqlFunction { public SqlCall getLookupCall() { if (null == lookupCall) { lookupCall = - lookupMakeCallObj.createCall(thisOperands, SqlParserPos.ZERO); + lookupMakeCallObj.createCall(SqlParserPos.ZERO, thisOperands); } return lookupCall; } public String getAllowedSignatures(String name) { - return lookupMakeCallObj.operator.getAllowedSignatures(name); + return lookupMakeCallObj.getOperator().getAllowedSignatures(name); } public RelDataType deriveType( @@ -470,37 +471,25 @@ public class SqlJdbcFunctionCall extends SqlFunction { RESOURCE.functionUndefined(getName())); } - if (!lookupMakeCallObj.checkNumberOfArg(opBinding.getOperandCount())) { + final String message = lookupMakeCallObj.isValidArgCount(callBinding); + if (message != null) { throw callBinding.newValidationError( RESOURCE.wrongNumberOfParam(getName(), thisOperands.length, - getArgCountMismatchMsg())); + message)); } - if (!lookupMakeCallObj.operator.checkOperandTypes( - new SqlCallBinding( - callBinding.getValidator(), - callBinding.getScope(), - getLookupCall()), - false)) { + final SqlCall newCall = getLookupCall(); + final SqlCallBinding newBinding = + new SqlCallBinding(callBinding.getValidator(), callBinding.getScope(), + newCall); + + final SqlOperator operator = lookupMakeCallObj.getOperator(); + if (!operator.checkOperandTypes(newBinding, false)) { throw callBinding.newValidationSignatureError(); } - return lookupMakeCallObj.operator.validateOperands( - callBinding.getValidator(), - callBinding.getScope(), - getLookupCall()); - } - private String getArgCountMismatchMsg() { - StringBuilder ret = new StringBuilder(); - int[] possible = lookupMakeCallObj.getPossibleArgCounts(); - for (int i = 0; i < possible.length; i++) { - if (i > 0) { - ret.append(" or "); - } - ret.append(possible[i]); - } - ret.append(" parameter(s)"); - return ret.toString(); + return operator.validateOperands(callBinding.getValidator(), + callBinding.getScope(), newCall); } public void unparse( @@ -549,59 +538,84 @@ public class SqlJdbcFunctionCall extends SqlFunction { //~ Inner Classes ---------------------------------------------------------- - /** - * Represent a Strategy Object to create a {@link SqlCall} by providing the - * feature of reording, adding/dropping operands. - */ - private static class MakeCall { - final SqlOperator operator; - final int[] order; - + /** Converts a call to a JDBC function to a call to a regular function. */ + private interface MakeCall { /** - * List of the possible numbers of operands this function can take. + * Creates and return a {@link SqlCall}. If the MakeCall strategy object + * was created with a reording specified the call will be created with + * the operands reordered, otherwise no change of ordering is applied + * + * @param operands Operands */ - final int[] argCounts; + SqlCall createCall(SqlParserPos pos, SqlNode... operands); + + SqlOperator getOperator(); + + String isValidArgCount(SqlCallBinding binding); + } - private MakeCall( - SqlOperator operator, - int argCount) { + /** Converter that calls a built-in function with the same arguments. */ + public static class SimpleMakeCall implements SqlJdbcFunctionCall.MakeCall { + final SqlOperator operator; + + public SimpleMakeCall(SqlOperator operator) { this.operator = operator; - this.order = null; - this.argCounts = new int[]{argCount}; } + public SqlOperator getOperator() { + return operator; + } + + public SqlCall createCall(SqlParserPos pos, SqlNode... operands) { + return operator.createCall(pos, operands); + } + + public String isValidArgCount(SqlCallBinding binding) { + return null; // any number of arguments is valid + } + } + + /** Implementation of {@link MakeCall} that can re-order or ignore operands. */ + private static class PermutingMakeCall extends SimpleMakeCall { + final int[] order; + /** * Creates a MakeCall strategy object with reordering of operands. * * <p>The reordering is specified by an int array where the value of * element at position <code>i</code> indicates to which element in a * new SqlNode[] array the operand goes. - * - * @param operator Operator + * @param operator Operator * @param order Order - * @pre order != null - * @pre order[i] < order.length - * @pre order.length > 0 - * @pre argCounts == order.length */ - MakeCall(SqlOperator operator, int argCount, int[] order) { - assert order != null && order.length > 0; + PermutingMakeCall(SqlOperator operator, int[] order) { + super(operator); + this.order = Preconditions.checkNotNull(order); + } - // Currently operation overloading when reordering is necessary is - // NOT implemented - Util.pre(argCount == order.length, "argCounts==order.length"); - this.operator = operator; - this.order = order; - this.argCounts = new int[]{order.length}; + @Override public SqlCall createCall(SqlParserPos pos, + SqlNode... operands) { + return super.createCall(pos, reorder(operands)); + } - // sanity checking ... - for (int anOrder : order) { - assert anOrder < order.length; + @Override public String isValidArgCount(SqlCallBinding binding) { + if (order.length == binding.getOperandCount()) { + return null; // operand count is valid + } else { + return getArgCountMismatchMsg(order.length); } } - final int[] getPossibleArgCounts() { - return this.argCounts; + private String getArgCountMismatchMsg(int... possible) { + StringBuilder ret = new StringBuilder(); + for (int i = 0; i < possible.length; i++) { + if (i > 0) { + ret.append(" or "); + } + ret.append(possible[i]); + } + ret.append(" parameter(s)"); + return ret.toString(); } /** @@ -620,37 +634,6 @@ public class SqlJdbcFunctionCall extends SqlFunction { } return newOrder; } - - /** - * Creates and return a {@link SqlCall}. If the MakeCall strategy object - * was created with a reording specified the call will be created with - * the operands reordered, otherwise no change of ordering is applied - * - * @param operands Operands - */ - SqlCall createCall( - SqlNode[] operands, - SqlParserPos pos) { - if (null == order) { - return operator.createCall(pos, operands); - } - return operator.createCall(pos, reorder(operands)); - } - - /** - * Returns false if number of arguments are unexpected, otherwise true. - * This function is supposed to be called with an {@link SqlNode} array - * of operands direct from the oven, e.g no reording or adding/dropping - * of operands...else it would make much sense to have this methods - */ - boolean checkNumberOfArg(int length) { - for (int argCount : argCounts) { - if (argCount == length) { - return true; - } - } - return false; - } } /** @@ -663,99 +646,62 @@ public class SqlJdbcFunctionCall extends SqlFunction { */ static final JdbcToInternalLookupTable INSTANCE = new JdbcToInternalLookupTable(); - private final Map<String, MakeCall> map = new HashMap<String, MakeCall>(); + + private final Map<String, MakeCall> map; private JdbcToInternalLookupTable() { // A table of all functions can be found at // http://java.sun.com/products/jdbc/driverdevs.html // which is also provided in the javadoc for this class. // See also SqlOperatorTests.testJdbcFn, which contains the list. - map.put( - "ABS", - new MakeCall(SqlStdOperatorTable.ABS, 1)); - map.put( - "EXP", - new MakeCall(SqlStdOperatorTable.EXP, 1)); - map.put( - "LOG", - new MakeCall(SqlStdOperatorTable.LN, 1)); - map.put( - "LOG10", - new MakeCall(SqlStdOperatorTable.LOG10, 1)); - map.put( - "MOD", - new MakeCall(SqlStdOperatorTable.MOD, 2)); - map.put( - "POWER", - new MakeCall(SqlStdOperatorTable.POWER, 2)); - - map.put( - "CONCAT", - new MakeCall(SqlStdOperatorTable.CONCAT, 2)); - map.put( - "INSERT", - new MakeCall( - SqlStdOperatorTable.OVERLAY, - 4, - new int[]{0, 2, 3, 1})); - map.put( - "LCASE", - new MakeCall(SqlStdOperatorTable.LOWER, 1)); - map.put( - "LENGTH", - new MakeCall(SqlStdOperatorTable.CHARACTER_LENGTH, 1)); - map.put( - "LOCATE", - new MakeCall(SqlStdOperatorTable.POSITION, 2)); - map.put( - "LTRIM", - new MakeCall(SqlStdOperatorTable.TRIM, 1) { - @Override SqlCall createCall( - SqlNode[] operands, SqlParserPos pos) { + ImmutableMap.Builder<String, MakeCall> map = ImmutableMap.builder(); + map.put("ABS", simple(SqlStdOperatorTable.ABS)); + map.put("EXP", simple(SqlStdOperatorTable.EXP)); + map.put("LOG", simple(SqlStdOperatorTable.LN)); + map.put("LOG10", simple(SqlStdOperatorTable.LOG10)); + map.put("MOD", simple(SqlStdOperatorTable.MOD)); + map.put("POWER", simple(SqlStdOperatorTable.POWER)); + map.put("CONCAT", simple(SqlStdOperatorTable.CONCAT)); + map.put("INSERT", + new PermutingMakeCall(SqlStdOperatorTable.OVERLAY, new int[]{0, 2, 3, 1})); + map.put("LCASE", simple(SqlStdOperatorTable.LOWER)); + map.put("LENGTH", simple(SqlStdOperatorTable.CHARACTER_LENGTH)); + map.put("LOCATE", simple(SqlStdOperatorTable.POSITION)); + map.put("LTRIM", + new SimpleMakeCall(SqlStdOperatorTable.TRIM) { + @Override public SqlCall createCall(SqlParserPos pos, + SqlNode... operands) { assert 1 == operands.length; - return super.createCall( - new SqlNode[]{ - SqlTrimFunction.Flag.LEADING.symbol(SqlParserPos.ZERO), - SqlLiteral.createCharString(" ", null), - operands[0] - }, - pos); + return super.createCall(pos, + SqlTrimFunction.Flag.LEADING.symbol(SqlParserPos.ZERO), + SqlLiteral.createCharString(" ", null), + operands[0]); } }); - map.put( - "QUARTER", - new MakeCall(SqlStdOperatorTable.QUARTER, 1)); - map.put( - "RTRIM", - new MakeCall(SqlStdOperatorTable.TRIM, 1) { - @Override SqlCall createCall( - SqlNode[] operands, SqlParserPos pos) { + map.put("QUARTER", simple(SqlStdOperatorTable.QUARTER)); + map.put("RTRIM", + new SimpleMakeCall(SqlStdOperatorTable.TRIM) { + @Override public SqlCall createCall(SqlParserPos pos, + SqlNode... operands) { assert 1 == operands.length; - return super.createCall( - new SqlNode[] { - SqlTrimFunction.Flag.TRAILING.symbol(SqlParserPos.ZERO), - SqlLiteral.createCharString(" ", null), - operands[0] - }, - pos); + return super.createCall(pos, + SqlTrimFunction.Flag.TRAILING.symbol(SqlParserPos.ZERO), + SqlLiteral.createCharString(" ", null), + operands[0]); } }); - map.put( - "SUBSTRING", - new MakeCall(SqlStdOperatorTable.SUBSTRING, 3)); - map.put( - "UCASE", - new MakeCall(SqlStdOperatorTable.UPPER, 1)); - - map.put( - "CURDATE", - new MakeCall(SqlStdOperatorTable.CURRENT_DATE, 0)); - map.put( - "CURTIME", - new MakeCall(SqlStdOperatorTable.LOCALTIME, 0)); - map.put( - "NOW", - new MakeCall(SqlStdOperatorTable.CURRENT_TIMESTAMP, 0)); + map.put("SUBSTRING", simple(SqlStdOperatorTable.SUBSTRING)); + map.put("UCASE", simple(SqlStdOperatorTable.UPPER)); + map.put("CURDATE", simple(SqlStdOperatorTable.CURRENT_DATE)); + map.put("CURTIME", simple(SqlStdOperatorTable.LOCALTIME)); + map.put("NOW", simple(SqlStdOperatorTable.CURRENT_TIMESTAMP)); + map.put("TIMESTAMPADD", simple(SqlStdOperatorTable.TIMESTAMP_ADD)); + map.put("TIMESTAMPDIFF", simple(SqlStdOperatorTable.TIMESTAMP_DIFF)); + this.map = map.build(); + } + + private MakeCall simple(SqlOperator operator) { + return new SimpleMakeCall(operator); } /** http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/main/java/org/apache/calcite/sql/SqlKind.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java index 7f34162..07d2bca 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -361,6 +361,16 @@ public enum SqlKind { */ LEAST, + /** + * The "TIMESTAMP_ADD" function (ODBC, SQL Server, MySQL). + */ + TIMESTAMP_ADD, + + /** + * The "TIMESTAMP_DIFF" function (ODBC, SQL Server, MySQL). + */ + TIMESTAMP_DIFF, + // prefix operators /** @@ -858,6 +868,7 @@ public enum SqlKind { EnumSet.of(AS, ARGUMENT_ASSIGNMENT, DEFAULT, DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, SELECT, JOIN, OTHER_FUNCTION, CAST, TRIM, FLOOR, CEIL, + TIMESTAMP_ADD, TIMESTAMP_DIFF, LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY, NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE, VALUES, WITH, WITH_ITEM), http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/main/java/org/apache/calcite/sql/TimestampInterval.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/TimestampInterval.java b/core/src/main/java/org/apache/calcite/sql/TimestampInterval.java deleted file mode 100644 index d586d4a..0000000 --- a/core/src/main/java/org/apache/calcite/sql/TimestampInterval.java +++ /dev/null @@ -1,47 +0,0 @@ -/* - * 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; - -import org.apache.calcite.sql.parser.SqlParserPos; - -/** - * Enumerates the timestamp intervals. - */ -public enum TimestampInterval implements SqlLiteral.SqlSymbol { - - MICROSECOND, - SECOND, - MINUTE, - HOUR, - DAY, - WEEK, - MONTH, - QUARTER, - YEAR; - - /** - * Creates a parse-tree node representing an occurrence of this - * condition type keyword at a particular position in the parsed - * text. - */ - public SqlLiteral symbol(SqlParserPos pos) { - return SqlLiteral.createSymbol(this, pos); - } - -} - -// End TimestampInterval.java http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/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 98d2e85..c075363 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 @@ -1316,66 +1316,66 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { new SqlCurrentDateFunction(); /** - * <p>Timestamp modifying or calculating difference functions. - * As first parameter take timestamp interval. - * </p> - * - * <p>Interval can one of the following literals:<br> - * <i>FRAC_SECOND, MICROSECOND, SQL_TSI_FRAC_SECOND, SQL_TSI_MICROSECOND</i><br> - * <i>SECOND, SQL_TSI_SECOND</i><br> - * <i>MINUTE, SQL_TSI_MINUTE</i><br> - * <i>HOUR, SQL_TSI_HOUR</i><br> - * <i>DAY, SQL_TSI_DAY</i><br> - * <i>WEEK, SQL_TSI_WEEK</i><br> - * <i>MONTH, SQL_TSI_MONTH</i><br> - * <i>QUARTER, SQL_TSI_QUARTER</i><br> - * <i>YEAR, SQL_TSI_YEAR</i><br> - * </p> - */ - - /** - * <p>The SQL <code>TIMESTAMP_ADD</code> function. - * Adds interval to timestamp.</p> + * <p>The <code>TIMESTAMPADD</code> function, which adds an interval to a + * timestamp. * * <p>The SQL syntax is * * <blockquote> - * <code>TIMESTAMP_ADD(<i>timestamp interval</i>,<i>quantity</i>,<i>timestamp</i>)</code> - * </blockquote><br> + * <code>TIMESTAMPADD(<i>timestamp interval</i>, <i>quantity</i>, <i>timestamp</i>)</code> + * </blockquote> * - * Returns modified timestamp.</p> + * <p>The interval time unit can one of the following literals:<ul> + * <li>MICROSECOND (and synonyms SQL_TSI_MICROSECOND, FRAC_SECOND, + * SQL_TSI_FRAC_SECOND) + * <li>SECOND (and synonym SQL_TSI_SECOND) + * <li>MINUTE (and synonym SQL_TSI_MINUTE) + * <li>HOUR (and synonym SQL_TSI_HOUR) + * <li>DAY (and synonym SQL_TSI_DAY) + * <li>WEEK (and synonym SQL_TSI_WEEK) + * <li>MONTH (and synonym SQL_TSI_MONTH) + * <li>QUARTER (and synonym SQL_TSI_QUARTER) + * <li>YEAR (and synonym SQL_TSI_YEAR) + * </ul> + * + * <p>Returns modified timestamp. */ public static final SqlFunction TIMESTAMP_ADD = - new SqlFunction( - "TIMESTAMPADD", - SqlKind.OTHER_FUNCTION, - ReturnTypes.ARG2, + new SqlFunction("TIMESTAMPADD", SqlKind.TIMESTAMP_ADD, ReturnTypes.ARG2, null, - OperandTypes.family( - SqlTypeFamily.ANY, SqlTypeFamily.INTEGER, SqlTypeFamily.DATETIME), - SqlFunctionCategory.TIMEDATE); + OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.INTEGER, + SqlTypeFamily.DATETIME), SqlFunctionCategory.TIMEDATE); /** - * <p>The SQL <code>TIMESTAMP_DIFF</code> function. - * Calculates difference between two timestamps.</p> + * <p>The <code>TIMESTAMPDIFF</code> function, which calculates the difference + * between two timestamps. * * <p>The SQL syntax is * * <blockquote> - * <code>TIMESTAMP_DIFF(<i>timestamp interval</i>,<i>timestamp</i>,<i>timestamp</i>)</code> - * </blockquote><br> + * <code>TIMESTAMPDIFF(<i>timestamp interval</i>, <i>timestamp</i>, <i>timestamp</i>)</code> + * </blockquote> * - * Returns difference between two timestamps in indicated timestamp interval.</p> + * <p>The interval time unit can one of the following literals:<ul> + * <li>MICROSECOND (and synonyms SQL_TSI_MICROSECOND, FRAC_SECOND, + * SQL_TSI_FRAC_SECOND) + * <li>SECOND (and synonym SQL_TSI_SECOND) + * <li>MINUTE (and synonym SQL_TSI_MINUTE) + * <li>HOUR (and synonym SQL_TSI_HOUR) + * <li>DAY (and synonym SQL_TSI_DAY) + * <li>WEEK (and synonym SQL_TSI_WEEK) + * <li>MONTH (and synonym SQL_TSI_MONTH) + * <li>QUARTER (and synonym SQL_TSI_QUARTER) + * <li>YEAR (and synonym SQL_TSI_YEAR) + * </ul> + * + * <p>Returns difference between two timestamps in indicated timestamp interval. */ - public static final SqlFunction TIMESTAMP_DIFF = new SqlFunction( - "TIMESTAMPDIFF", - SqlKind.OTHER_FUNCTION, - ReturnTypes.INTEGER_NULLABLE, - null, - OperandTypes.family( - SqlTypeFamily.ANY, SqlTypeFamily.DATETIME, SqlTypeFamily.DATETIME), - SqlFunctionCategory.TIMEDATE); - + public static final SqlFunction TIMESTAMP_DIFF = + new SqlFunction("TIMESTAMPDIFF", SqlKind.TIMESTAMP_DIFF, + ReturnTypes.INTEGER_NULLABLE, null, + OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.DATETIME, + SqlTypeFamily.DATETIME), SqlFunctionCategory.TIMEDATE); /** * Use of the <code>IN_FENNEL</code> operator forces the argument to be http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java index 5d1957f..44d6fe5 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -285,10 +285,13 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { SqlStdOperatorTable.VAR_SAMP, new AvgVarianceConvertlet(SqlKind.VAR_SAMP)); - registerOp( - SqlStdOperatorTable.FLOOR, new FloorCeilConvertlet(true)); - registerOp( - SqlStdOperatorTable.CEIL, new FloorCeilConvertlet(false)); + final SqlRexConvertlet floorCeilConvertlet = new FloorCeilConvertlet(); + registerOp(SqlStdOperatorTable.FLOOR, floorCeilConvertlet); + registerOp(SqlStdOperatorTable.CEIL, floorCeilConvertlet); + + registerOp(SqlStdOperatorTable.TIMESTAMP_ADD, + new TimestampAddConvertlet()); + registerOp(SqlStdOperatorTable.TIMESTAMP_DIFF, new TimestampDiffConvertlet()); // Convert "element(<expr>)" to "$element_slice(<expr>)", if the // expression is a multiset of scalars. @@ -503,10 +506,8 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { return cx.getRexBuilder().makeCast(type, arg); } - protected RexNode convertFloorCeil( - SqlRexContext cx, - SqlCall call, - boolean floor) { + protected RexNode convertFloorCeil(SqlRexContext cx, SqlCall call) { + final boolean floor = call.getKind() == SqlKind.FLOOR; // Rewrite floor, ceil of interval if (call.operandCount() == 1 && call.operand(0) instanceof SqlIntervalLiteral) { @@ -671,6 +672,19 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { if (val.equals(BigDecimal.ONE)) { return res; } + // If val is between 0 and 1, rather than divide by val, multiply by its + // reciprocal. For example, rather than divide by 0.001 multiply by 1000. + if (val.compareTo(BigDecimal.ONE) < 0 + && val.signum() == 1) { + try { + final BigDecimal reciprocal = + BigDecimal.ONE.divide(val, BigDecimal.ROUND_UNNECESSARY); + return rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, res, + rexBuilder.makeExactLiteral(reciprocal)); + } catch (ArithmeticException e) { + // ignore - reciprocal is not an integer + } + } return rexBuilder.makeCall(SqlStdOperatorTable.DIVIDE_INTEGER, res, rexBuilder.makeExactLiteral(val)); } @@ -1323,14 +1337,48 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { /** Convertlet that handles {@code FLOOR} and {@code CEIL} functions. */ private class FloorCeilConvertlet implements SqlRexConvertlet { - private final boolean floor; + public RexNode convertCall(SqlRexContext cx, SqlCall call) { + return convertFloorCeil(cx, call); + } + } - public FloorCeilConvertlet(boolean floor) { - this.floor = floor; + /** Convertlet that handles the {@code TIMESTAMPADD} function. */ + private class TimestampAddConvertlet implements SqlRexConvertlet { + public RexNode convertCall(SqlRexContext cx, SqlCall call) { + // TIMESTAMPADD(unit, count, timestamp) + // => timestamp + count * INTERVAL '1' UNIT + final RexBuilder rexBuilder = cx.getRexBuilder(); + final SqlLiteral unitLiteral = call.operand(0); + final TimeUnit unit = unitLiteral.symbolValue(TimeUnit.class); + return rexBuilder.makeCall(SqlStdOperatorTable.DATETIME_PLUS, + cx.convertExpression(call.operand(2)), + rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, + rexBuilder.makeIntervalLiteral(unit.multiplier, + new SqlIntervalQualifier(unit, null, + unitLiteral.getParserPosition())), + cx.convertExpression(call.operand(1)))); } + } + /** Convertlet that handles the {@code TIMESTAMPDIFF} function. */ + private class TimestampDiffConvertlet implements SqlRexConvertlet { public RexNode convertCall(SqlRexContext cx, SqlCall call) { - return convertFloorCeil(cx, call, floor); + // TIMESTAMPDIFF(unit, t1, t2) + // => (t1 - t2) UNIT + final RexBuilder rexBuilder = cx.getRexBuilder(); + final SqlLiteral unitLiteral = call.operand(0); + final TimeUnit unit = unitLiteral.symbolValue(TimeUnit.class); + final RelDataType intType = + cx.getTypeFactory().createSqlType(SqlTypeName.INTEGER); + final SqlIntervalQualifier qualifier = + new SqlIntervalQualifier(unit, null, SqlParserPos.ZERO); + return divide(cx.getRexBuilder(), + rexBuilder.makeCast(intType, + rexBuilder.makeCall(SqlStdOperatorTable.MINUS_DATE, + cx.convertExpression(call.operand(1)), + cx.convertExpression(call.operand(2)), + cx.getRexBuilder().makeIntervalLiteral(qualifier))), + unit.multiplier); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java index 858e8a0..0d7d614 100644 --- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -5566,6 +5566,24 @@ public class SqlParserTest { "(?s).*Was expecting one of.*"); } + @Test public void testTimestampAdd() { + final String sql = "select * from t\n" + + "where timestampadd(sql_tsi_month, 5, hiredate) < curdate"; + final String expected = "SELECT *\n" + + "FROM `T`\n" + + "WHERE (TIMESTAMPADD(MONTH, 5, `HIREDATE`) < `CURDATE`)"; + sql(sql).ok(expected); + } + + @Test public void testTimestampDiff() { + final String sql = "select * from t\n" + + "where timestampdiff(frac_second, 5, hiredate) < curdate"; + final String expected = "SELECT *\n" + + "FROM `T`\n" + + "WHERE (TIMESTAMPDIFF(MICROSECOND, 5, `HIREDATE`) < `CURDATE`)"; + sql(sql).ok(expected); + } + @Test public void testUnnest() { check( "select*from unnest(x)", http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 48311dd..1c5ec79 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -1727,18 +1727,12 @@ public abstract class SqlOperatorBaseTest { if (false) { tester.checkScalar("{fn SECOND(time)}", null, ""); } - if (false) { - tester.checkScalar( - "{fn TIMESTAMPADD(interval, count, timestamp)}", - null, - ""); - } - if (false) { - tester.checkScalar( - "{fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)}", - null, - ""); - } + tester.checkScalar("{fn TIMESTAMPADD(HOUR, 5," + + " TIMESTAMP '2014-03-29 12:34:56')}", + "2014-03-29 17:34:56", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar("{fn TIMESTAMPDIFF(HOUR," + + " TIMESTAMP '2014-03-29 12:34:56'," + + " TIMESTAMP '2014-03-29 12:34:56')}", "0", "INTEGER NOT NULL"); if (false) { tester.checkScalar("{fn WEEK(date)}", null, ""); } @@ -4599,18 +4593,48 @@ public abstract class SqlOperatorBaseTest { "floor(cast(null as interval year))"); } - @Test public void testTimestampAddAdnDiff() { - if (!enable) { - return; - } + @Test public void testTimestampAdd() { + tester.setFor(SqlStdOperatorTable.TIMESTAMP_ADD); tester.checkScalar( - "timestampadd(MINUTE, 2, timestamp '2016-02-24 12:42:25')", + "timestampadd(SQL_TSI_SECOND, 2, timestamp '2016-02-24 12:42:25')", "2016-02-24 12:42:27", "TIMESTAMP(0) NOT NULL"); tester.checkScalar( - "timestampdiff(YEAR, " - + "timestamp '2014-02-24 12:42:25', " - + "timestamp '2016-02-24 12:42:25')", + "timestampadd(MINUTE, 2, timestamp '2016-02-24 12:42:25')", + "2016-02-24 12:44:25", + "TIMESTAMP(0) NOT NULL"); + tester.checkScalar( + "timestampadd(HOUR, -2000, timestamp '2016-02-24 12:42:25')", + "2015-12-03 04:42:25", + "TIMESTAMP(0) NOT NULL"); + if (!INTERVAL) { + return; + } + tester.checkNull("timestampadd(HOUR, CAST(NULL AS INTEGER)," + + " timestamp '2016-02-24 12:42:25')"); + tester.checkNull( + "timestampadd(HOUR, -200, CAST(NULL AS TIMESTAMP))"); + tester.checkScalar( + "timestampadd(MONTH, 3, timestamp '2016-02-24 12:42:25')", + "2016-05-24 12:42:25", "TIMESTAMP(0) NOT NULL"); + } + + @Test public void testTimestampDiff() { + tester.setFor(SqlStdOperatorTable.TIMESTAMP_DIFF); + tester.checkScalar("timestampdiff(HOUR, " + + "timestamp '2016-02-24 12:42:25', " + + "timestamp '2016-02-24 15:42:25')", + "-3", "INTEGER NOT NULL"); + tester.checkScalar("timestampdiff(MICROSECOND, " + + "timestamp '2016-02-24 12:42:25', " + + "timestamp '2016-02-24 12:42:20')", + "5000000", "INTEGER NOT NULL"); + if (!INTERVAL) { + return; + } + tester.checkScalar("timestampdiff(YEAR, " + + "timestamp '2014-02-24 12:42:25', " + + "timestamp '2016-02-24 12:42:25')", "2", "INTEGER NOT NULL"); } http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/core/src/test/java/org/apache/calcite/sql/test/SqlTests.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlTests.java b/core/src/test/java/org/apache/calcite/sql/test/SqlTests.java index 4f15f7d..d5889fb 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlTests.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlTests.java @@ -22,10 +22,8 @@ import org.apache.calcite.sql.type.SqlTypeName; import java.sql.ResultSet; import java.sql.Types; -import java.util.ArrayList; import java.util.Collection; import java.util.HashSet; -import java.util.List; import java.util.Set; import java.util.regex.Pattern; @@ -135,7 +133,7 @@ public abstract class SqlTests { public static void compareResultSet( ResultSet resultSet, Set<String> refSet) throws Exception { - Set<String> actualSet = new HashSet<String>(); + Set<String> actualSet = new HashSet<>(); final int columnType = resultSet.getMetaData().getColumnType(1); final ColumnMetaData.Rep rep = rep(columnType); while (resultSet.next()) { @@ -151,7 +149,13 @@ public abstract class SqlTests { case SHORT: case INTEGER: case LONG: - final long l = Long.parseLong(s0); + long l; + try { + l = Long.parseLong(s0); + } catch (NumberFormatException e) { + // Large integers come out in scientific format, say "5E+06" + l = (long) Double.parseDouble(s0); + } assertThat(resultSet.getByte(1), equalTo((byte) l)); assertThat(resultSet.getShort(1), equalTo((short) l)); assertThat(resultSet.getInt(1), equalTo((int) l)); @@ -256,78 +260,6 @@ public abstract class SqlTests { } } - /** - * Compares the first column of a result set against a String-valued - * reference set, taking order into account. - * - * @param resultSet Result set - * @param refList Expected results - * @throws Exception . - */ - public static void compareResultList( - ResultSet resultSet, - List<String> refList) throws Exception { - List<String> actualSet = new ArrayList<String>(); - while (resultSet.next()) { - String s = resultSet.getString(1); - actualSet.add(s); - } - resultSet.close(); - assertEquals(refList, actualSet); - } - - /** - * Compares the columns of a result set against several String-valued - * reference lists, taking order into account. - * - * @param resultSet Result set - * @param refLists vararg of List<String>. The first list is compared - * to the first column, the second list to the second column - * and so on - */ - public static void compareResultLists( - ResultSet resultSet, - List<String>... refLists) throws Exception { - int numExpectedColumns = refLists.length; - - assertTrue(numExpectedColumns > 0); - - assertTrue( - resultSet.getMetaData().getColumnCount() >= numExpectedColumns); - - int numExpectedRows = -1; - - List<List<String>> actualLists = new ArrayList<List<String>>(); - for (int i = 0; i < numExpectedColumns; i++) { - actualLists.add(new ArrayList<String>()); - - if (i == 0) { - numExpectedRows = refLists[i].size(); - } else { - assertEquals( - "num rows differ across ref lists", - numExpectedRows, - refLists[i].size()); - } - } - - while (resultSet.next()) { - for (int i = 0; i < numExpectedColumns; i++) { - String s = resultSet.getString(i + 1); - - actualLists.get(i).add(s); - } - } - resultSet.close(); - - for (int i = 0; i < numExpectedColumns; i++) { - assertEquals( - "column mismatch in column " + (i + 1), - refLists[i], - actualLists.get(i)); - } - } - //~ Inner Classes ---------------------------------------------------------- /** @@ -382,11 +314,13 @@ public abstract class SqlTests { assertTrue(result instanceof Number); return new ApproximateResultChecker((Number) result, delta); } else { - Set<String> refSet = new HashSet<String>(); + Set<String> refSet = new HashSet<>(); if (result == null) { refSet.add(null); } else if (result instanceof Collection) { - refSet.addAll((Collection<String>) result); + //noinspection unchecked + final Collection<String> collection = (Collection<String>) result; + refSet.addAll(collection); } else { refSet.add(result.toString()); } http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/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 c8ba8e9..a56237c 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -1133,9 +1133,10 @@ public class SqlValidatorTest extends SqlValidatorTestCase { checkWholeExpFails( "{fn log10('1')}", "(?s).*Cannot apply.*fn LOG10..<CHAR.1.>.*"); - checkWholeExpFails( - "{fn log10(1,1)}", - "(?s).*Encountered .fn LOG10. with 2 parameter.s.; was expecting 1 parameter.s.*"); + final String expected = "Cannot apply '\\{fn LOG10\\}' to arguments of" + + " type '\\{fn LOG10\\}\\(<INTEGER>, <INTEGER>\\)'\\. " + + "Supported form\\(s\\): '\\{fn LOG10\\}\\(<NUMERIC>\\)'"; + checkWholeExpFails("{fn log10(1,1)}", expected); checkWholeExpFails( "{fn fn(1)}", "(?s).*Function '.fn FN.' is not defined.*"); http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 8785bd5..af05ad2 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -979,12 +979,15 @@ See also: UNNEST relational operator converts a collection to a relation. | Operator syntax | Description |:------------------------------ |:----------- +| {fn ABS(numeric)} | Returns the absolute value of *numeric* +| {fn EXP(numeric)} | Returns *e* raised to the power of *numeric* +| {fn LOG(numeric)} | Returns the natural logarithm (base *e*) of *numeric* | {fn LOG10(numeric)} | Returns the base-10 logarithm of *numeric* +| {fn MOD(numeric1, numeric2)} | Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative | {fn POWER(numeric1, numeric2)} | Returns *numeric1* raised to the power of *numeric2* Not implemented: -* {fn ABS(numeric)} - Returns the absolute value of *numeric* * {fn ACOS(numeric)} - Returns the arc cosine of *numeric* * {fn ASIN(numeric)} - Returns the arc sine of *numeric* * {fn ATAN(numeric)} - Returns the arc tangent of *numeric* @@ -993,10 +996,7 @@ Not implemented: * {fn COS(numeric)} - Returns the cosine of *numeric* * {fn COT(numeric)} * {fn DEGREES(numeric)} - Converts *numeric* from radians to degrees -* {fn EXP(numeric)} - Returns *e* raised to the power of *numeric* * {fn FLOOR(numeric)} - Rounds *numeric* down, and returns the largest number that is less than or equal to *numeric* -* {fn LOG(numeric)} - Returns the natural logarithm (base *e*) of *numeric* -* {fn MOD(numeric1, numeric2)} - Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative * {fn PI()} - Returns a value that is closer than any other value to *pi* * {fn RADIANS(numeric)} - Converts *numeric* from degrees to radians * {fn RAND(numeric)} @@ -1011,35 +1011,42 @@ Not implemented: | Operator syntax | Description |:--------------- |:----------- +| {fn CONCAT(character, character)} | Returns the concatenation of character strings | {fn LOCATE(string1, string2)} | Returns the position in *string2* of the first occurrence of *string1*. Searches from the beginning of the second CharacterExpression, unless the startIndex parameter is specified. | {fn INSERT(string1, start, length, string2)} | Inserts *string2* into a slot in *string1* | {fn LCASE(string)} | Returns a string in which all alphabetic characters in *string* have been converted to lower case +| {fn LENGTH(string)} | Returns the number of characters in a string +| {fn LOCATE(string1, string2 [, integer])} | Returns the position in *string2* of the first occurrence of *string1*. Searches from the beginning of *string2*, unless *integer* is specified. +| {fn LTRIM(string)} | Returns *string* with leading space characters removed +| {fn RTRIM(string)} | Returns *string* with trailing space characters removed +| {fn SUBSTRING(string, offset, length)} | Returns a character string that consists of *length* characters from *string* starting at the *offset* position +| {fn UCASE(string)} | Returns a string in which all alphabetic characters in *string* have been converted to upper case Not implemented: * {fn ASCII(string)} - Convert a single-character string to the corresponding ASCII code, an integer between 0 and 255 * {fn CHAR(string)} -* {fn CONCAT(character, character)} - Returns the concatenation of character strings * {fn DIFFERENCE(string, string)} * {fn LEFT(string, integer)} -* {fn LENGTH(string)} -* {fn LOCATE(string1, string2 [, integer])} - Returns the position in *string2* of the first occurrence of *string1*. Searches from the beginning of *string2*, unless *integer* is specified. -* {fn LTRIM(string)} * {fn REPEAT(string, integer)} * {fn REPLACE(string, string, string)} * {fn RIGHT(string, integer)} -* {fn RTRIM(string)} * {fn SOUNDEX(string)} * {fn SPACE(integer)} -* {fn SUBSTRING(string, integer, integer)} -* {fn UCASE(string)} - Returns a string in which all alphabetic characters in *string* have been converted to upper case #### Date/time +| Operator syntax | Description +|:--------------- |:----------- +| {fn CURDATE()} | Equivalent to `CURRENT_DATE` +| {fn CURTIME()} | Equivalent to `LOCALTIME` +| {fn NOW()} | Equivalent to `LOCALTIMESTAMP` +| {fn QUARTER(date)} | Equivalent to `EXTRACT(QUARTER FROM date)`. Returns an integer between 1 and 4. +| {fn TIMESTAMPADD(interval, count, timestamp)} | Adds an interval to a timestamp +| {fn TIMESTAMPDIFF(interval, timestamp, timestamp)} | Subtracts two timestamps + Not implemented: -* {fn CURDATE()} -* {fn CURTIME()} * {fn DAYNAME(date)} * {fn DAYOFMONTH(date)} * {fn DAYOFWEEK(date)} @@ -1048,11 +1055,7 @@ Not implemented: * {fn MINUTE(time)} * {fn MONTH(date)} * {fn MONTHNAME(date)} -* {fn NOW()} -* {fn QUARTER(date)} * {fn SECOND(time)} -* {fn TIMESTAMPADD(interval, count, timestamp)} -* {fn TIMESTAMPDIFF(interval, timestamp, timestamp)} * {fn WEEK(date)} * {fn YEAR(date)} http://git-wip-us.apache.org/repos/asf/calcite/blob/4ac82a30/site/community/index.md ---------------------------------------------------------------------- diff --git a/site/community/index.md b/site/community/index.md index 82aa4ce..ceacb76 100644 --- a/site/community/index.md +++ b/site/community/index.md @@ -26,6 +26,7 @@ limitations under the License. # Upcoming talks +* 2016/03/30 <a href="http://conferences.oreilly.com/strata/hadoop-big-data-ca/public/schedule/detail/48180";>Strata + Hadoop World</a>, San Jose (developer showcase) * 2016/04/13 <a href="http://hadoopsummit.org/dublin/agenda/";>Hadoop Summit</a>, Dublin * 2016/04/26 <a href="http://kafka-summit.org/schedule/";>Kafka Summit</a>, San Francisco * 2016/05/10 <a href="http://events.linuxfoundation.org/events/apache-big-data-north-america/program/schedule";>ApacheCon Big Data North America</a>, Vancouver
