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 2016/03/08 07:13:25 UTC
[5/5] calcite git commit: Further to [CALCITE-1124],
add implementation of TIMESTAMPADD, TIMESTAMPDIFF
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 <jh...@apache.org>
Authored: Mon Mar 7 21:23:34 2016 -0800
Committer: Julian Hyde <jh...@apache.org>
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