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/06/05 22:22:52 UTC
[11/12] calcite git commit: [CALCITE-1177] Extend list of supported
time units in EXTRACT, CEIL and FLOOR functions (Venki Korukanti)
[CALCITE-1177] Extend list of supported time units in EXTRACT, CEIL and FLOOR functions (Venki Korukanti)
Close apache/calcite#232
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/97945652
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/97945652
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/97945652
Branch: refs/heads/master
Commit: 97945652197cbc80ce5c4ed05d3fc18ee0061e75
Parents: 80b05c2
Author: vkorukanti <ve...@dremio.com>
Authored: Tue Mar 29 17:41:18 2016 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Sat Jun 4 21:35:29 2016 -0700
----------------------------------------------------------------------
core/src/main/codegen/templates/Parser.jj | 53 +--
.../sql2rel/StandardConvertletTable.java | 82 ++++-
.../calcite/sql/parser/SqlParserTest.java | 121 +++++-
.../calcite/sql/test/SqlOperatorBaseTest.java | 364 +++++++++++++++++++
site/_docs/reference.md | 8 +-
5 files changed, 593 insertions(+), 35 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/97945652/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 9b1bdd0..bad32b0 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -3489,33 +3489,26 @@ SqlIntervalQualifier IntervalQualifier() :
}
}
+/**
+ * Parses time unit for EXTRACT, CEIL and FLOOR functions.
+ */
TimeUnit TimeUnit() :
{}
{
- <YEAR>
- {
- return TimeUnit.YEAR;
- }
- | <MONTH>
- {
- return TimeUnit.MONTH;
- }
- | <DAY>
- {
- return TimeUnit.DAY;
- }
- | <HOUR>
- {
- return TimeUnit.HOUR;
- }
- | <MINUTE>
- {
- return TimeUnit.MINUTE;
- }
- | <SECOND>
- {
- return TimeUnit.SECOND;
- }
+ <SECOND> { return TimeUnit.SECOND; }
+| <MINUTE> { return TimeUnit.MINUTE; }
+| <HOUR> { return TimeUnit.HOUR; }
+| <DAY> { return TimeUnit.DAY; }
+| <DOW> { return TimeUnit.DOW; }
+| <DOY> { return TimeUnit.DOY; }
+| <WEEK> { return TimeUnit.WEEK; }
+| <MONTH> { return TimeUnit.MONTH; }
+| <QUARTER> { return TimeUnit.QUARTER; }
+| <YEAR> { return TimeUnit.YEAR; }
+| <EPOCH> { return TimeUnit.EPOCH; }
+| <DECADE> { return TimeUnit.DECADE; }
+| <CENTURY> { return TimeUnit.CENTURY; }
+| <MILLENNIUM> { return TimeUnit.MILLENNIUM; }
}
TimeUnit TimestampInterval() :
@@ -4856,6 +4849,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < CATALOG_NAME: "CATALOG_NAME" >
| < CEIL: "CEIL" >
| < CEILING: "CEILING" >
+ | < CENTURY: "CENTURY" >
| < CHAIN: "CHAIN" >
| < CHAR: "CHAR" >
| < CHAR_LENGTH: "CHAR_LENGTH" >
@@ -4929,6 +4923,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < DAY: "DAY" >
| < DEALLOCATE: "DEALLOCATE" >
| < DEC: "DEC" >
+ | < DECADE: "DECADE" >
| < DECIMAL: "DECIMAL" >
| < DECLARE: "DECLARE" >
| < DEFAULT_KW: "DEFAULT" >
@@ -4955,6 +4950,8 @@ SqlPostfixOperator PostfixRowOperator() :
| < DISTINCT: "DISTINCT" >
| < DOMAIN: "DOMAIN" >
| < DOUBLE: "DOUBLE" >
+ | < DOW: "DOW" >
+ | < DOY: "DOY" >
| < DROP: "DROP" >
| < DYNAMIC: "DYNAMIC" >
| < DYNAMIC_FUNCTION: "DYNAMIC_FUNCTION" >
@@ -4964,6 +4961,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < ELSE: "ELSE" >
| < END: "END" >
| < END_EXEC: "END-EXEC" >
+ | < EPOCH: "EPOCH" >
| < EQUALS: "EQUALS" >
| < ESCAPE: "ESCAPE" >
| < EVERY: "EVERY" >
@@ -5076,6 +5074,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < MESSAGE_TEXT: "MESSAGE_TEXT" >
| < METHOD: "METHOD" >
| < MICROSECOND: "MICROSECOND" >
+ | < MILLENNIUM: "MILLENNIUM" >
| < MIN: "MIN" >
| < MINUTE: "MINUTE" >
| < MINVALUE: "MINVALUE" >
@@ -5397,6 +5396,7 @@ String CommonNonReservedKeyWord() :
| <CASCADE>
| <CATALOG>
| <CATALOG_NAME>
+ | <CENTURY>
| <CHAIN>
| <CHARACTER_SET_CATALOG>
| <CHARACTER_SET_NAME>
@@ -5428,6 +5428,7 @@ String CommonNonReservedKeyWord() :
| <DATABASE>
| <DATETIME_INTERVAL_CODE>
| <DATETIME_INTERVAL_PRECISION>
+ | <DECADE>
| <DEFAULTS>
| <DEFERRABLE>
| <DEFERRED>
@@ -5442,8 +5443,11 @@ String CommonNonReservedKeyWord() :
| <DIAGNOSTICS>
| <DISPATCH>
| <DOMAIN>
+ | <DOW>
+ | <DOY>
| <DYNAMIC_FUNCTION>
| <DYNAMIC_FUNCTION_CODE>
+ | <EPOCH>
| <EQUALS>
| <EXCEPTION>
| <EXCLUDE>
@@ -5490,6 +5494,7 @@ String CommonNonReservedKeyWord() :
| <MESSAGE_LENGTH>
| <MESSAGE_OCTET_LENGTH>
| <MESSAGE_TEXT>
+ | <MILLENNIUM>
| <MINVALUE>
| <MORE_KW>
| <MUMPS>
http://git-wip-us.apache.org/repos/asf/calcite/blob/97945652/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 7fbc5f7..643d1c5 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -563,6 +563,11 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
return convertFunction(cx, (SqlFunction) call.getOperator(), call);
}
+ /**
+ * Converts a call to the {@code EXTRACT} function.
+ *
+ * <p>Called automatically via reflection.
+ */
public RexNode convertExtract(
SqlRexContext cx,
SqlExtractFunction op,
@@ -602,10 +607,78 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
default:
throw new AssertionError("unexpected " + sqlTypeName);
}
+ break;
+ case MILLENNIUM:
+ case CENTURY:
+ case DECADE:
+ switch (sqlTypeName) {
+ case TIMESTAMP:
+ res = divide(rexBuilder, res, TimeUnit.DAY.multiplier);
+ // fall through
+ case DATE:
+ res = rexBuilder.makeCall(resType, SqlStdOperatorTable.EXTRACT_DATE,
+ ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.YEAR), res));
+ return divide(rexBuilder, res, unit.multiplier.divide(TimeUnit.YEAR.multiplier));
+ }
+ break;
+ case QUARTER:
+ switch (sqlTypeName) {
+ case TIMESTAMP:
+ res = divide(rexBuilder, res, TimeUnit.DAY.multiplier);
+ // fall through
+ case DATE:
+ res = rexBuilder.makeCall(resType, SqlStdOperatorTable.EXTRACT_DATE,
+ ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MONTH), res));
+ res = rexBuilder.makeCall(SqlStdOperatorTable.MINUS, res,
+ rexBuilder.makeExactLiteral(BigDecimal.ONE));
+ res = divide(rexBuilder, res, unit.multiplier);
+ return rexBuilder.makeCall(SqlStdOperatorTable.PLUS, res,
+ rexBuilder.makeExactLiteral(BigDecimal.ONE));
+ }
+ break;
+ case EPOCH:
+ switch (sqlTypeName) {
+ case DATE:
+ // convert to milliseconds
+ res = rexBuilder.makeCall(resType, SqlStdOperatorTable.MULTIPLY,
+ ImmutableList.of(res, rexBuilder.makeExactLiteral(TimeUnit.DAY.multiplier)));
+ // fall through
+ case TIMESTAMP:
+ // convert to seconds
+ return divide(rexBuilder, res, TimeUnit.SECOND.multiplier);
+ case INTERVAL_DAY_TIME:
+ case INTERVAL_YEAR_MONTH:
+ // no convertlet conversion, pass it as extract
+ return convertFunction(cx, (SqlFunction) call.getOperator(), call);
+ }
+ break;
+ case DOW:
+ case DOY:
+ case WEEK:
+ switch (sqlTypeName) {
+ case INTERVAL_DAY_TIME: // fall through
+ case INTERVAL_YEAR_MONTH:
+ // TODO: is this check better to do in validation phase?
+ // Currently there is parameter on TimeUnit to identify these type of units.
+ throw new IllegalArgumentException("Extract " + unit + " from "
+ + sqlTypeName + " type data is not supported");
+ case TIMESTAMP: // fall through
+ case DATE:
+ // no convertlet conversion, pass it as extract
+ return convertFunction(cx, (SqlFunction) call.getOperator(), call);
+ }
}
res = mod(rexBuilder, resType, res, getFactor(unit));
+ if (unit == TimeUnit.QUARTER) {
+ res = rexBuilder.makeCall(SqlStdOperatorTable.MINUS, res,
+ rexBuilder.makeExactLiteral(BigDecimal.ONE));
+ }
res = divide(rexBuilder, res, unit.multiplier);
+ if (unit == TimeUnit.QUARTER) {
+ res = rexBuilder.makeCall(SqlStdOperatorTable.PLUS, res,
+ rexBuilder.makeExactLiteral(BigDecimal.ONE));
+ }
return res;
}
@@ -650,10 +723,15 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
return TimeUnit.HOUR.multiplier;
case SECOND:
return TimeUnit.MINUTE.multiplier;
- case YEAR:
- return BigDecimal.ONE;
case MONTH:
return TimeUnit.YEAR.multiplier;
+ case QUARTER:
+ return TimeUnit.YEAR.multiplier;
+ case YEAR:
+ case DECADE:
+ case CENTURY:
+ case MILLENNIUM:
+ return BigDecimal.ONE;
default:
throw Util.unexpected(unit);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/97945652/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 a66f12a..b745389 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
@@ -963,16 +963,106 @@ public class SqlParserTest {
@Test public void testFloor() {
checkExp("floor(1.5)", "FLOOR(1.5)");
checkExp("floor(x)", "FLOOR(`X`)");
+
+ checkExp("floor(x to second)", "FLOOR(`X` TO SECOND)");
+ checkExp("floor(x to epoch)", "FLOOR(`X` TO EPOCH)");
+ checkExp("floor(x to minute)", "FLOOR(`X` TO MINUTE)");
checkExp("floor(x to hour)", "FLOOR(`X` TO HOUR)");
+ checkExp("floor(x to day)", "FLOOR(`X` TO DAY)");
+ checkExp("floor(x to dow)", "FLOOR(`X` TO DOW)");
+ checkExp("floor(x to doy)", "FLOOR(`X` TO DOY)");
+ checkExp("floor(x to week)", "FLOOR(`X` TO WEEK)");
+ checkExp("floor(x to month)", "FLOOR(`X` TO MONTH)");
+ checkExp("floor(x to quarter)", "FLOOR(`X` TO QUARTER)");
+ checkExp("floor(x to year)", "FLOOR(`X` TO YEAR)");
+ checkExp("floor(x to decade)", "FLOOR(`X` TO DECADE)");
+ checkExp("floor(x to century)", "FLOOR(`X` TO CENTURY)");
+ checkExp("floor(x to millennium)", "FLOOR(`X` TO MILLENNIUM)");
+
+ checkExp("floor(x + interval '1:20' minute to second)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND))");
+ checkExp("floor(x + interval '1:20' minute to second to second)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO SECOND)");
+ checkExp("floor(x + interval '1:20' minute to second to epoch)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO EPOCH)");
+ checkExp("floor(x + interval '1:20' hour to minute)",
+ "FLOOR((`X` + INTERVAL '1:20' HOUR TO MINUTE))");
+ checkExp("floor(x + interval '1:20' hour to minute to minute)",
+ "FLOOR((`X` + INTERVAL '1:20' HOUR TO MINUTE) TO MINUTE)");
+ checkExp("floor(x + interval '1:20' minute to second to hour)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO HOUR)");
+ checkExp("floor(x + interval '1:20' minute to second to day)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DAY)");
+ checkExp("floor(x + interval '1:20' minute to second to dow)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DOW)");
+ checkExp("floor(x + interval '1:20' minute to second to doy)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DOY)");
+ checkExp("floor(x + interval '1:20' minute to second to week)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO WEEK)");
+ checkExp("floor(x + interval '1:20' minute to second to month)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO MONTH)");
+ checkExp("floor(x + interval '1:20' minute to second to quarter)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO QUARTER)");
+ checkExp("floor(x + interval '1:20' minute to second to year)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO YEAR)");
+ checkExp("floor(x + interval '1:20' minute to second to decade)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DECADE)");
+ checkExp("floor(x + interval '1:20' minute to second to century)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO CENTURY)");
+ checkExp("floor(x + interval '1:20' minute to second to millennium)",
+ "FLOOR((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO MILLENNIUM)");
+ }
+
+ @Test public void testCeil() {
+ checkExp("ceil(3453.2)", "CEIL(3453.2)");
+ checkExp("ceil(x)", "CEIL(`X`)");
+ checkExp("ceil(x to second)", "CEIL(`X` TO SECOND)");
+ checkExp("ceil(x to epoch)", "CEIL(`X` TO EPOCH)");
+ checkExp("ceil(x to minute)", "CEIL(`X` TO MINUTE)");
checkExp("ceil(x to hour)", "CEIL(`X` TO HOUR)");
- checkExp("ceil(x + interval '1' minute to second)",
- "CEIL((`X` + INTERVAL '1' MINUTE TO SECOND))");
- checkExp("ceil((x + interval '1' minute) to second)",
- "CEIL((`X` + INTERVAL '1' MINUTE) TO SECOND)");
- checkExp("ceil(x + (interval '1:23' minute to second))",
- "CEIL((`X` + INTERVAL '1:23' MINUTE TO SECOND))");
- checkExp("ceil(x + interval '1:23' minute to second to second)",
- "CEIL((`X` + INTERVAL '1:23' MINUTE TO SECOND) TO SECOND)");
+ checkExp("ceil(x to day)", "CEIL(`X` TO DAY)");
+ checkExp("ceil(x to dow)", "CEIL(`X` TO DOW)");
+ checkExp("ceil(x to doy)", "CEIL(`X` TO DOY)");
+ checkExp("ceil(x to week)", "CEIL(`X` TO WEEK)");
+ checkExp("ceil(x to month)", "CEIL(`X` TO MONTH)");
+ checkExp("ceil(x to quarter)", "CEIL(`X` TO QUARTER)");
+ checkExp("ceil(x to year)", "CEIL(`X` TO YEAR)");
+ checkExp("ceil(x to decade)", "CEIL(`X` TO DECADE)");
+ checkExp("ceil(x to century)", "CEIL(`X` TO CENTURY)");
+ checkExp("ceil(x to millennium)", "CEIL(`X` TO MILLENNIUM)");
+
+ checkExp("ceil(x + interval '1:20' minute to second)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND))");
+ checkExp("ceil(x + interval '1:20' minute to second to second)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO SECOND)");
+ checkExp("ceil(x + interval '1:20' minute to second to epoch)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO EPOCH)");
+ checkExp("ceil(x + interval '1:20' hour to minute)",
+ "CEIL((`X` + INTERVAL '1:20' HOUR TO MINUTE))");
+ checkExp("ceil(x + interval '1:20' hour to minute to minute)",
+ "CEIL((`X` + INTERVAL '1:20' HOUR TO MINUTE) TO MINUTE)");
+ checkExp("ceil(x + interval '1:20' minute to second to hour)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO HOUR)");
+ checkExp("ceil(x + interval '1:20' minute to second to day)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DAY)");
+ checkExp("ceil(x + interval '1:20' minute to second to dow)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DOW)");
+ checkExp("ceil(x + interval '1:20' minute to second to doy)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DOY)");
+ checkExp("ceil(x + interval '1:20' minute to second to week)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO WEEK)");
+ checkExp("ceil(x + interval '1:20' minute to second to month)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO MONTH)");
+ checkExp("ceil(x + interval '1:20' minute to second to quarter)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO QUARTER)");
+ checkExp("ceil(x + interval '1:20' minute to second to year)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO YEAR)");
+ checkExp("ceil(x + interval '1:20' minute to second to decade)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO DECADE)");
+ checkExp("ceil(x + interval '1:20' minute to second to century)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO CENTURY)");
+ checkExp("ceil(x + interval '1:20' minute to second to millennium)",
+ "CEIL((`X` + INTERVAL '1:20' MINUTE TO SECOND) TO MILLENNIUM)");
}
@Test public void testCast() {
@@ -5967,6 +6057,13 @@ public class SqlParserTest {
checkExpFails("interval '1' second(3,1) ^to^ second", ANY);
checkExpFails("interval '1' second(2,3) ^to^ second", ANY);
checkExpFails("interval '1' second(2,2) ^to^ second(3)", ANY);
+
+ // Invalid units
+ checkExpFails("INTERVAL '2' ^MILLENNIUM^", ANY);
+ checkExpFails("INTERVAL '1-2' ^MILLENNIUM^ TO CENTURY", ANY);
+ checkExpFails("INTERVAL '10' ^CENTURY^", ANY);
+ checkExpFails("INTERVAL '10' ^DECADE^", ANY);
+ checkExpFails("INTERVAL '4' ^QUARTER^", ANY);
}
@Test public void testMiscIntervalQualifier() {
@@ -6028,6 +6125,14 @@ public class SqlParserTest {
checkExp("extract(hour from x)", "EXTRACT(HOUR FROM `X`)");
checkExp("extract(minute from x)", "EXTRACT(MINUTE FROM `X`)");
checkExp("extract(second from x)", "EXTRACT(SECOND FROM `X`)");
+ checkExp("extract(dow from x)", "EXTRACT(DOW FROM `X`)");
+ checkExp("extract(doy from x)", "EXTRACT(DOY FROM `X`)");
+ checkExp("extract(week from x)", "EXTRACT(WEEK FROM `X`)");
+ checkExp("extract(epoch from x)", "EXTRACT(EPOCH FROM `X`)");
+ checkExp("extract(quarter from x)", "EXTRACT(QUARTER FROM `X`)");
+ checkExp("extract(decade from x)", "EXTRACT(DECADE FROM `X`)");
+ checkExp("extract(century from x)", "EXTRACT(CENTURY FROM `X`)");
+ checkExp("extract(millennium from x)", "EXTRACT(MILLENNIUM FROM `X`)");
checkExpFails(
"extract(day ^to^ second from x)",
http://git-wip-us.apache.org/repos/asf/calcite/blob/97945652/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 a9ab66f..030e2f3 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
@@ -161,6 +161,14 @@ public abstract class SqlOperatorBaseTest {
public static final String BAD_DATETIME_MESSAGE =
Bug.FNL3_FIXED ? null : "(?s).*";
+ // Error messages when an invalid time unit is given as
+ // input to extract for a particular input type.
+ public static final String INVALID_EXTRACT_UNIT_CONVERTLET_ERROR =
+ "Extract.*from.*type data is not supported";
+
+ public static final String INVALID_EXTRACT_UNIT_VALIDATION_ERROR =
+ "Cannot apply 'EXTRACT' to arguments of type .*'\n.*";
+
public static final String LITERAL_OUT_OF_RANGE_MESSAGE =
"(?s).*Numeric literal.*out of range.*";
@@ -4370,6 +4378,330 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("quarter(cast(null as date))");
}
+ @Test public void testExtractIntervalYearMonth() {
+ tester.setFor(
+ SqlStdOperatorTable.EXTRACT,
+ VM_FENNEL,
+ VM_JAVA);
+
+ if (TODO) {
+ // Not supported, fails in type validation because the extract
+ // unit is not YearMonth interval type.
+
+ tester.checkScalar(
+ "extract(epoch from interval '4-2' year to month)",
+ // number of seconds elapsed since timestamp
+ // '1970-01-01 00:00:00' + input interval
+ "131328000",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(second from interval '4-2' year to month)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(minute from interval '4-2' year to month)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(hour from interval '4-2' year to month)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(day from interval '4-2' year to month)",
+ "0",
+ "BIGINT NOT NULL");
+ }
+
+ // Postgres doesn't support DOW, DOY and WEEK on INTERVAL YEAR MONTH type.
+ // SQL standard doesn't have extract units for DOW, DOY and WEEK.
+ tester.checkFails("^extract(doy from interval '4-2' year to month)^",
+ INVALID_EXTRACT_UNIT_VALIDATION_ERROR, false);
+ tester.checkFails("^extract(dow from interval '4-2' year to month)^",
+ INVALID_EXTRACT_UNIT_VALIDATION_ERROR, false);
+ tester.checkFails("^extract(week from interval '4-2' year to month)^",
+ INVALID_EXTRACT_UNIT_VALIDATION_ERROR, false);
+
+ tester.checkScalar(
+ "extract(month from interval '4-2' year to month)",
+ "2",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(quarter from interval '4-2' year to month)",
+ "1",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(year from interval '4-2' year to month)",
+ "4",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(decade from interval '426-3' year(3) to month)",
+ "42",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(century from interval '426-3' year(3) to month)",
+ "4",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(millennium from interval '2005-3' year(4) to month)",
+ "2",
+ "BIGINT NOT NULL");
+ }
+
+ @Test public void testExtractIntervalDayTime() {
+ tester.setFor(
+ SqlStdOperatorTable.EXTRACT,
+ VM_FENNEL,
+ VM_JAVA);
+
+ if (TODO) {
+ // Not implemented in operator test
+ tester.checkScalar(
+ "extract(epoch from interval '2 3:4:5.678' day to second)",
+ // number of seconds elapsed since timestamp
+ // '1970-01-01 00:00:00' + input interval
+ "183845.678",
+ "BIGINT NOT NULL");
+ }
+
+ tester.checkScalar(
+ "extract(second from interval '2 3:4:5.678' day to second)",
+ "5",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(minute from interval '2 3:4:5.678' day to second)",
+ "4",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(hour from interval '2 3:4:5.678' day to second)",
+ "3",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(day from interval '2 3:4:5.678' day to second)",
+ "2",
+ "BIGINT NOT NULL");
+
+ // Postgres doesn't support DOW, DOY and WEEK on INTERVAL DAY TIME type.
+ // SQL standard doesn't have extract units for DOW, DOY and WEEK.
+ tester.checkFails("extract(doy from interval '2 3:4:5.678' day to second)",
+ INVALID_EXTRACT_UNIT_CONVERTLET_ERROR, true);
+ tester.checkFails("extract(dow from interval '2 3:4:5.678' day to second)",
+ INVALID_EXTRACT_UNIT_CONVERTLET_ERROR, true);
+ tester.checkFails("extract(week from interval '2 3:4:5.678' day to second)",
+ INVALID_EXTRACT_UNIT_CONVERTLET_ERROR, true);
+
+ if (TODO) {
+ // Not supported, fails in type validation because
+ // the extract unit is YearMonth interval type unit.
+ tester.checkScalar(
+ "extract(month from interval '2 3:4:5.678' day to second)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(quarter from interval '2 3:4:5.678' day to second)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(year from interval '2 3:4:5.678' day to second)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(decade from interval '2 3:4:5.678' day to second)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(century from interval '2 3:4:5.678' day to second)",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(millennium from interval '2 3:4:5.678' day to second)",
+ "0",
+ "BIGINT NOT NULL");
+ }
+ }
+
+ @Test public void testExtractDate() {
+ tester.setFor(
+ SqlStdOperatorTable.EXTRACT,
+ VM_FENNEL,
+ VM_JAVA);
+
+ tester.checkScalar(
+ "extract(epoch from date '2008-2-23')",
+ "1203724800", // number of seconds elapsed since timestamp
+ // '1970-01-01 00:00:00' for given date
+ "BIGINT NOT NULL");
+
+ if (TODO) {
+ // Looks like there is a bug in current execution code which returns 13
+ // instead of 0
+ tester.checkScalar(
+ "extract(second from date '2008-2-23')",
+ "0",
+ "BIGINT NOT NULL");
+ }
+
+ tester.checkScalar(
+ "extract(minute from date '2008-2-23')",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(hour from date '2008-2-23')",
+ "0",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(day from date '2008-2-23')",
+ "23",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(month from date '2008-2-23')",
+ "2",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(quarter from date '2008-4-23')",
+ "2",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(year from date '2008-2-23')",
+ "2008",
+ "BIGINT NOT NULL");
+
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "extract(doy from date '2008-2-23')",
+ "cannot translate call EXTRACT.*",
+ true);
+
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "extract(dow from date '2008-2-23')",
+ "cannot translate call EXTRACT.*",
+ true);
+
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "extract(week from date '2008-2-23')",
+ "cannot translate call EXTRACT.*",
+ true);
+
+ tester.checkScalar(
+ "extract(decade from date '2008-2-23')",
+ "200",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(century from date '2008-2-23')",
+ "20",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(millennium from date '2008-2-23')",
+ "2",
+ "BIGINT NOT NULL");
+ }
+
+ @Test public void testExtractTimestamp() {
+ tester.setFor(
+ SqlStdOperatorTable.EXTRACT,
+ VM_FENNEL,
+ VM_JAVA);
+
+ tester.checkScalar(
+ "extract(epoch from timestamp '2008-2-23 12:34:56')",
+ "1203770096", // number of seconds elapsed since timestamp
+ // '1970-01-01 00:00:00' for given date
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(second from timestamp '2008-2-23 12:34:56')",
+ "56",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(minute from timestamp '2008-2-23 12:34:56')",
+ "34",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(hour from timestamp '2008-2-23 12:34:56')",
+ "12",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(day from timestamp '2008-2-23 12:34:56')",
+ "23",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(month from timestamp '2008-2-23 12:34:56')",
+ "2",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(quarter from timestamp '2008-7-23 12:34:56')",
+ "3",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(year from timestamp '2008-2-23 12:34:56')",
+ "2008",
+ "BIGINT NOT NULL");
+
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "extract(doy from timestamp '2008-2-23 12:34:56')",
+ "cannot translate call EXTRACT.*",
+ true);
+
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "extract(dow from timestamp '2008-2-23 12:34:56')",
+ "cannot translate call EXTRACT.*",
+ true);
+
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "extract(week from timestamp '2008-2-23 12:34:56')",
+ "cannot translate call EXTRACT.*",
+ true);
+
+ tester.checkScalar(
+ "extract(decade from timestamp '2008-2-23 12:34:56')",
+ "200",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(century from timestamp '2008-2-23 12:34:56')",
+ "20",
+ "BIGINT NOT NULL");
+
+ tester.checkScalar(
+ "extract(millennium from timestamp '2008-2-23 12:34:56')",
+ "2",
+ "BIGINT NOT NULL");
+ }
+
@Test public void testExtractFunc() {
tester.setFor(
SqlStdOperatorTable.EXTRACT,
@@ -4656,6 +4988,38 @@ public abstract class SqlOperatorBaseTest {
"floor(interval '-5-1' year to month)",
"-6-00",
"INTERVAL YEAR TO MONTH NOT NULL");
+ tester.checkScalar(
+ "floor(interval '-6.3' second to second)",
+ "-7.000000",
+ "INTERVAL SECOND NOT NULL");
+ tester.checkScalar(
+ "floor(interval '6-3' minute to second to minute)",
+ "-7-0",
+ "INTERVAL MINUTE TO SECOND NOT NULL");
+ tester.checkScalar(
+ "floor(interval '6-3' hour to minute to hour)",
+ "7-0",
+ "INTERVAL HOUR TO MINUTE NOT NULL");
+ tester.checkScalar(
+ "floor(interval '6 3' day to hour to day)",
+ "7 00",
+ "INTERVAL DAY TO HOUR NOT NULL");
+ tester.checkScalar(
+ "floor(interval '102-7' year to month to month)",
+ "102-07",
+ "INTERVAL YEAR TO MONTH NOT NULL");
+ tester.checkScalar(
+ "floor(interval '102-7' year to month to quarter)",
+ "102-10",
+ "INTERVAL YEAR TO MONTH NOT NULL");
+ tester.checkScalar(
+ "floor(interval '102-1' year to month to century)",
+ "201",
+ "INTERVAL YEAR TO MONTH NOT NULL");
+ tester.checkScalar(
+ "floor(interval '1004-1' year to month to millennium)",
+ "2001-00",
+ "INTERVAL YEAR TO MONTH NOT NULL");
tester.checkNull(
"floor(cast(null as interval year))");
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/97945652/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index a6ce287..15fd34c 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -260,6 +260,7 @@ CATALOG,
CATALOG_NAME,
**CEIL**,
**CEILING**,
+CENTURY,
CHAIN,
**CHAR**,
**CHARACTER**,
@@ -333,6 +334,7 @@ DATETIME_INTERVAL_PRECISION,
**DAY**,
**DEALLOCATE**,
**DEC**,
+DECADE,
**DECIMAL**,
**DECLARE**,
**DEFAULT**,
@@ -359,6 +361,8 @@ DISPATCH,
**DISTINCT**,
DOMAIN,
**DOUBLE**,
+DOW,
+DOY,
**DROP**,
**DYNAMIC**,
DYNAMIC_FUNCTION,
@@ -368,6 +372,7 @@ DYNAMIC_FUNCTION_CODE,
**ELSE**,
**END**,
**END-EXEC**,
+EPOCH,
EQUALS,
**ESCAPE**,
**EVERY**,
@@ -480,6 +485,7 @@ MESSAGE_OCTET_LENGTH,
MESSAGE_TEXT,
**METHOD**,
MICROSECOND,
+MILLENNIUM,
**MIN**,
**MINUTE**,
MINVALUE,
@@ -802,7 +808,7 @@ Where:
{% highlight sql %}
timeUnit:
- YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
+ MILLENNIUM | CENTURY | DECADE | YEAR | QUARTER | MONTH | WEEK | DOY | DOW | DAY | HOUR | MINUTE | SECOND | EPOCH
{% endhighlight %}
Note: