This is an automated email from the ASF dual-hosted git repository.
gian pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new 0099940808 Add TIME_IN_INTERVAL SQL operator. (#12662)
0099940808 is described below
commit 0099940808946812523afa5b5caa68bf52569b75
Author: Gian Merlino <[email protected]>
AuthorDate: Tue Jun 21 13:05:37 2022 -0700
Add TIME_IN_INTERVAL SQL operator. (#12662)
* Add TIME_IN_INTERVAL SQL operator.
The operator is implemented as a convertlet rather than an
OperatorConversion, because this allows it to be equivalent to using
the >= and < operators directly.
* SqlParserPos cannot be null here.
* Remove unused import.
* Doc updates.
* Add words to dictionary.
---
docs/configuration/index.md | 2 +-
docs/querying/sql-scalar.md | 32 ++++-
docs/tutorials/tutorial-query.md | 8 +-
.../tutorial/wikipedia-top-pages-sql.json | 2 +-
.../planner/CurrentTimestampSqlFunction.java | 52 -------
.../sql/calcite/planner/DruidOperatorTable.java | 1 +
.../druid/sql/calcite/planner/PlannerFactory.java | 1 +
...rrentTimestampAndFriendsConvertletFactory.java} | 125 +++++++----------
.../planner/convertlet/DruidConvertletFactory.java | 36 +++++
.../planner/convertlet/DruidConvertletTable.java | 115 ++++++++++++++++
.../TimeInIntervalConvertletFactory.java | 153 +++++++++++++++++++++
.../apache/druid/sql/calcite/CalciteQueryTest.java | 79 +++++++++++
website/.spelling | 2 +
13 files changed, 469 insertions(+), 139 deletions(-)
diff --git a/docs/configuration/index.md b/docs/configuration/index.md
index 59b773eeed..bc591a4ebb 100644
--- a/docs/configuration/index.md
+++ b/docs/configuration/index.md
@@ -277,7 +277,7 @@ For native JSON request, the `sql_query` field is empty.
Example
For SQL query request, the `native_query` field is empty. Example
```
-2019-01-14T10:00:00.000Z 127.0.0.1
{"sqlQuery/time":100,"sqlQuery/bytes":600,"success":true,"identity":"user1"}
{"query":"SELECT page, COUNT(*) AS Edits FROM wikiticker WHERE __time BETWEEN
TIMESTAMP '2015-09-12 00:00:00' AND TIMESTAMP '2015-09-13 00:00:00' GROUP BY
page ORDER BY Edits DESC LIMIT
10","context":{"sqlQueryId":"c9d035a0-5ffd-4a79-a865-3ffdadbb5fdd","nativeQueryIds":"[490978e4-f5c7-4cf6-b174-346e63cf8863]"}}
+2019-01-14T10:00:00.000Z 127.0.0.1
{"sqlQuery/time":100,"sqlQuery/bytes":600,"success":true,"identity":"user1"}
{"query":"SELECT page, COUNT(*) AS Edits FROM wikiticker WHERE
TIME_IN_INTERVAL(\"__time\", '2015-09-12/2015-09-13') GROUP BY page ORDER BY
Edits DESC LIMIT
10","context":{"sqlQueryId":"c9d035a0-5ffd-4a79-a865-3ffdadbb5fdd","nativeQueryIds":"[490978e4-f5c7-4cf6-b174-346e63cf8863]"}}
```
#### Emitter request logging
diff --git a/docs/querying/sql-scalar.md b/docs/querying/sql-scalar.md
index 8915b3f5d6..bb3744e160 100644
--- a/docs/querying/sql-scalar.md
+++ b/docs/querying/sql-scalar.md
@@ -123,11 +123,15 @@ String functions accept strings, and return a type
appropriate to the function.
## Date and time functions
-Time functions can be used with Druid's `__time` column, with any column
storing millisecond timestamps through use
-of the `MILLIS_TO_TIMESTAMP` function, or with any column storing string
timestamps through use of the `TIME_PARSE`
-function. By default, time operations use the UTC time zone. You can change
the time zone by setting the connection
-context parameter "sqlTimeZone" to the name of another time zone, like
"America/Los_Angeles", or to an offset like
-"-08:00". If you need to mix multiple time zones in the same query, or if you
need to use a time zone other than
+Time functions can be used with:
+
+- Druid's primary timestamp column, `__time`;
+- Numeric values representing milliseconds since the epoch, through the
MILLIS_TO_TIMESTAMP function; and
+- String timestamps, through the TIME_PARSE function.
+
+By default, time operations use the UTC time zone. You can change the time
zone by setting the connection
+context parameter `sqlTimeZone` to the name of another time zone, like
`America/Los_Angeles`, or to an offset like
+`-08:00`. If you need to mix multiple time zones in the same query, or if you
need to use a time zone other than
the connection time zone, some functions also accept time zones as parameters.
These parameters always take precedence
over the connection time zone.
@@ -135,6 +139,21 @@ Literal timestamps in the connection time zone can be
written using `TIMESTAMP '
simplest way to write literal timestamps in other time zones is to use
TIME_PARSE, like
`TIME_PARSE('2000-02-01 00:00:00', NULL, 'America/Los_Angeles')`.
+The best ways to filter based on time are by using ISO8601 intervals, like
+`TIME_IN_INTERVAL(__time, '2000-01-01/2000-02-01')`, or by using literal
timestamps with the `>=` and `<` operators, like
+`__time >= TIMESTAMP '2000-01-01 00:00:00' AND __time < TIMESTAMP '2000-02-01
00:00:00'`.
+
+Druid supports the standard SQL BETWEEN operator, but we recommend avoiding it
for time filters. BETWEEN is inclusive
+of its upper bound, which makes it awkward to write time filters correctly.
For example, the equivalent of
+`TIME_IN_INTERVAL(__time, '2000-01-01/2000-02-01')` is
+`__time BETWEEN TIMESTAMP '2000-01-01 00:00:00' AND TIMESTAMP '2000-01-31
23:59:59.999'`.
+
+Druid processes timestamps internally as longs (64-bit integers) representing
milliseconds since the epoch. Therefore,
+time functions perform best when used with the primary timestamp column, or
with timestamps stored in long columns as
+milliseconds and accessed with MILLIS_TO_TIMESTAMP. Other timestamp
representations, include string timestamps and
+POSIX timestamps (seconds since the epoch) require query-time conversion to
Druid's internal form, which adds additional
+overhead.
+
|Function|Notes|
|--------|-----|
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
@@ -146,7 +165,8 @@ simplest way to write literal timestamps in other time
zones is to use TIME_PARS
|`TIME_EXTRACT(timestamp_expr, [unit, [timezone]])`|Extracts a time part from
`expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY
(day of month), DOW (day of week), DOY (day of year), WEEK (week of [week
year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12),
QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time
zone name like "America/Los_Angeles" or offset like "-08:00". This function is
similar to `EXTRACT` but is mo [...]
|`TIME_PARSE(string_expr, [pattern, [timezone]])`|Parses a string into a
timestamp using a given [Joda DateTimeFormat
pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html),
or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The
time zone, if provided, should be a time zone name like "America/Los_Angeles"
or offset like "-08:00", and will be used as the time zone for strings that do
not include a time zone offset. Pattern and time z [...]
|`TIME_FORMAT(timestamp_expr, [pattern, [timezone]])`|Formats a timestamp as a
string with a given [Joda DateTimeFormat
pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html),
or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The
time zone, if provided, should be a time zone name like "America/Los_Angeles"
or offset like "-08:00". Pattern and time zone must be literals.|
-|`MILLIS_TO_TIMESTAMP(millis_expr)`|Converts a number of milliseconds since
the epoch into a timestamp.|
+|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is
contained within a particular interval. The interval must be a literal string
containing any ISO8601 interval, such as `'2001-01-01/P1D'` or
`'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval
is inclusive and the end instant is exclusive.|
+|`MILLIS_TO_TIMESTAMP(millis_expr)`|Converts a number of milliseconds since
the epoch (1970-01-01 00:00:00 UTC) into a timestamp.|
|`TIMESTAMP_TO_MILLIS(timestamp_expr)`|Converts a timestamp into a number of
milliseconds since the epoch.|
|`EXTRACT(unit FROM timestamp_expr)`|Extracts a time part from `expr`,
returning it as a number. Unit can be EPOCH, MICROSECOND, MILLISECOND, SECOND,
MINUTE, HOUR, DAY (day of month), DOW (day of week), ISODOW (ISO day of week),
DOY (day of year), WEEK (week of year), MONTH, QUARTER, YEAR, ISOYEAR, DECADE,
CENTURY or MILLENNIUM. Units must be provided unquoted, like `EXTRACT(HOUR FROM
__time)`.|
|`FLOOR(timestamp_expr TO unit)`|Rounds down a timestamp, returning it as a
new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or
YEAR.|
diff --git a/docs/tutorials/tutorial-query.md b/docs/tutorials/tutorial-query.md
index 27e8165aca..9be94e175f 100644
--- a/docs/tutorials/tutorial-query.md
+++ b/docs/tutorials/tutorial-query.md
@@ -159,7 +159,7 @@ Here is a collection of queries to try out:
```sql
SELECT FLOOR(__time to HOUR) AS HourTime, SUM(deleted) AS LinesDeleted
-FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2015-09-12 00:00:00' AND
TIMESTAMP '2015-09-13 00:00:00'
+FROM wikipedia WHERE TIME_IN_INTERVAL("__time", '2015-09-12/2015-09-13')
GROUP BY 1
```
@@ -169,7 +169,7 @@ GROUP BY 1
```sql
SELECT channel, page, SUM(added)
-FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2015-09-12 00:00:00' AND
TIMESTAMP '2015-09-13 00:00:00'
+FROM wikipedia WHERE TIME_IN_INTERVAL("__time", '2015-09-12/2015-09-13')
GROUP BY channel, page
ORDER BY SUM(added) DESC
```
@@ -194,7 +194,7 @@ dsql>
To submit the query, paste it to the `dsql` prompt and press enter:
```bash
-dsql> SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE "__time" BETWEEN
TIMESTAMP '2015-09-12 00:00:00' AND TIMESTAMP '2015-09-13 00:00:00' GROUP BY
page ORDER BY Edits DESC LIMIT 10;
+dsql> SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE
TIME_IN_INTERVAL("__time", '2015-09-12/2015-09-13') GROUP BY page ORDER BY
Edits DESC LIMIT 10;
┌──────────────────────────────────────────────────────────┬───────┐
│ page │ Edits │
├──────────────────────────────────────────────────────────┼───────┤
@@ -220,7 +220,7 @@ You can submit native queries [directly to the Druid Broker
over HTTP](../queryi
```json
{
- "query": "SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE \"__time\"
BETWEEN TIMESTAMP '2015-09-12 00:00:00' AND TIMESTAMP '2015-09-13 00:00:00'
GROUP BY page ORDER BY Edits DESC LIMIT 10"
+ "query": "SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE
TIME_IN_INTERVAL(\"__time\", '2015-09-12/2015-09-13') GROUP BY page ORDER BY
Edits DESC LIMIT 10"
}
```
diff --git a/examples/quickstart/tutorial/wikipedia-top-pages-sql.json
b/examples/quickstart/tutorial/wikipedia-top-pages-sql.json
index b1b0fdcd5a..a0e7ae5ad9 100644
--- a/examples/quickstart/tutorial/wikipedia-top-pages-sql.json
+++ b/examples/quickstart/tutorial/wikipedia-top-pages-sql.json
@@ -1,3 +1,3 @@
{
- "query":"SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE \"__time\"
BETWEEN TIMESTAMP '2015-09-12 00:00:00' AND TIMESTAMP '2015-09-13 00:00:00'
GROUP BY page ORDER BY Edits DESC LIMIT 10"
+ "query":"SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE
TIME_IN_INTERVAL(\"__time\", '2015-09-12/2015-09-13') GROUP BY page ORDER BY
Edits DESC LIMIT 10"
}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/CurrentTimestampSqlFunction.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/CurrentTimestampSqlFunction.java
deleted file mode 100644
index 409d8dd355..0000000000
---
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/CurrentTimestampSqlFunction.java
+++ /dev/null
@@ -1,52 +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.druid.sql.calcite.planner;
-
-import org.apache.calcite.rel.type.RelDataType;
-import org.apache.calcite.sql.SqlOperatorBinding;
-import org.apache.calcite.sql.fun.SqlAbstractTimeFunction;
-import org.apache.calcite.sql.type.SqlTypeName;
-
-/**
- * Used for functions like CURRENT_TIMESTAMP and LOCALTIME.
- *
- * Similar to {@link SqlAbstractTimeFunction}, but default precision is
- * {@link DruidTypeSystem#DEFAULT_TIMESTAMP_PRECISION} instead of 0.
- */
-public class CurrentTimestampSqlFunction extends SqlAbstractTimeFunction
-{
- private final SqlTypeName typeName;
-
- public CurrentTimestampSqlFunction(final String name, final SqlTypeName
typeName)
- {
- super(name, typeName);
- this.typeName = typeName;
- }
-
- @Override
- public RelDataType inferReturnType(SqlOperatorBinding opBinding)
- {
- if (opBinding.getOperandCount() == 0) {
- return opBinding.getTypeFactory().createSqlType(typeName,
DruidTypeSystem.DEFAULT_TIMESTAMP_PRECISION);
- } else {
- return super.inferReturnType(opBinding);
- }
- }
-}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
index 8af685feb3..018abc7a65 100644
---
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
@@ -115,6 +115,7 @@ import
org.apache.druid.sql.calcite.expression.builtin.TimeShiftOperatorConversi
import
org.apache.druid.sql.calcite.expression.builtin.TimestampToMillisOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.TrimOperatorConversion;
import
org.apache.druid.sql.calcite.expression.builtin.TruncateOperatorConversion;
+import org.apache.druid.sql.calcite.planner.convertlet.DruidConvertletTable;
import javax.annotation.Nullable;
import java.util.ArrayList;
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/PlannerFactory.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/PlannerFactory.java
index bf2b96fa34..f1d09abb36 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/planner/PlannerFactory.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/planner/PlannerFactory.java
@@ -44,6 +44,7 @@ import org.apache.druid.server.security.Access;
import org.apache.druid.server.security.AuthorizerMapper;
import org.apache.druid.server.security.NoopEscalator;
import org.apache.druid.sql.calcite.parser.DruidSqlParserImplFactory;
+import org.apache.druid.sql.calcite.planner.convertlet.DruidConvertletTable;
import org.apache.druid.sql.calcite.run.QueryMakerFactory;
import org.apache.druid.sql.calcite.schema.DruidSchemaCatalog;
import org.apache.druid.sql.calcite.schema.DruidSchemaName;
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidConvertletTable.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/CurrentTimestampAndFriendsConvertletFactory.java
similarity index 52%
rename from
sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidConvertletTable.java
rename to
sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/CurrentTimestampAndFriendsConvertletFactory.java
index 28047a33a6..837d0b0ae2 100644
---
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidConvertletTable.java
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/CurrentTimestampAndFriendsConvertletFactory.java
@@ -17,34 +17,32 @@
* under the License.
*/
-package org.apache.druid.sql.calcite.planner;
+package org.apache.druid.sql.calcite.planner.convertlet;
import com.google.common.collect.ImmutableList;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlFunction;
-import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlOperator;
-import org.apache.calcite.sql.fun.SqlLibraryOperators;
+import org.apache.calcite.sql.SqlOperatorBinding;
+import org.apache.calcite.sql.fun.SqlAbstractTimeFunction;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql2rel.SqlRexContext;
import org.apache.calcite.sql2rel.SqlRexConvertlet;
-import org.apache.calcite.sql2rel.SqlRexConvertletTable;
-import org.apache.calcite.sql2rel.StandardConvertletTable;
import org.apache.druid.java.util.common.ISE;
+import org.apache.druid.sql.calcite.planner.Calcites;
+import org.apache.druid.sql.calcite.planner.DruidTypeSystem;
+import org.apache.druid.sql.calcite.planner.PlannerContext;
-import java.util.ArrayList;
-import java.util.HashMap;
import java.util.List;
-import java.util.Map;
-public class DruidConvertletTable implements SqlRexConvertletTable
+public class CurrentTimestampAndFriendsConvertletFactory implements
DruidConvertletFactory
{
- // Apply a convertlet that doesn't do anything other than a "dumb" call
translation.
- private static final SqlRexConvertlet BYPASS_CONVERTLET =
StandardConvertletTable.INSTANCE::convertCall;
+ public static final CurrentTimestampAndFriendsConvertletFactory INSTANCE =
+ new CurrentTimestampAndFriendsConvertletFactory();
/**
* Use instead of {@link SqlStdOperatorTable#CURRENT_TIMESTAMP} to get the
proper default precision.
@@ -58,85 +56,37 @@ public class DruidConvertletTable implements
SqlRexConvertletTable
private static final SqlFunction LOCALTIMESTAMP =
new CurrentTimestampSqlFunction("LOCALTIMESTAMP", SqlTypeName.TIMESTAMP);
- private static final List<SqlOperator> CURRENT_TIME_CONVERTLET_OPERATORS =
+ private static final List<SqlOperator> SQL_OPERATORS =
ImmutableList.<SqlOperator>builder()
- .add(CURRENT_TIMESTAMP)
- .add(SqlStdOperatorTable.CURRENT_TIME)
- .add(SqlStdOperatorTable.CURRENT_DATE)
- .add(LOCALTIMESTAMP)
- .add(SqlStdOperatorTable.LOCALTIME)
- .build();
-
- // Operators we don't have standard conversions for, but which can be
converted into ones that do by
- // Calcite's StandardConvertletTable.
- private static final List<SqlOperator> STANDARD_CONVERTLET_OPERATORS =
- ImmutableList.<SqlOperator>builder()
- .add(SqlStdOperatorTable.ROW)
- .add(SqlStdOperatorTable.NOT_IN)
- .add(SqlStdOperatorTable.NOT_LIKE)
- .add(SqlStdOperatorTable.BETWEEN)
- .add(SqlStdOperatorTable.NOT_BETWEEN)
- .add(SqlStdOperatorTable.SYMMETRIC_BETWEEN)
- .add(SqlStdOperatorTable.SYMMETRIC_NOT_BETWEEN)
- .add(SqlStdOperatorTable.ITEM)
- .add(SqlStdOperatorTable.TIMESTAMP_ADD)
- .add(SqlStdOperatorTable.TIMESTAMP_DIFF)
- .add(SqlStdOperatorTable.UNION)
- .add(SqlStdOperatorTable.UNION_ALL)
- .add(SqlStdOperatorTable.NULLIF)
- .add(SqlStdOperatorTable.COALESCE)
- .add(SqlLibraryOperators.NVL)
- .build();
-
- private final Map<SqlOperator, SqlRexConvertlet> table;
-
- public DruidConvertletTable(final PlannerContext plannerContext)
+ .add(CURRENT_TIMESTAMP)
+ .add(SqlStdOperatorTable.CURRENT_TIME)
+ .add(SqlStdOperatorTable.CURRENT_DATE)
+ .add(LOCALTIMESTAMP)
+ .add(SqlStdOperatorTable.LOCALTIME)
+ .build();
+
+ private CurrentTimestampAndFriendsConvertletFactory()
{
- this.table = createConvertletMap(plannerContext);
+ // Singleton.
}
@Override
- public SqlRexConvertlet get(SqlCall call)
+ public SqlRexConvertlet createConvertlet(PlannerContext plannerContext)
{
- if (call.getKind() == SqlKind.EXTRACT &&
call.getOperandList().get(1).getKind() != SqlKind.LITERAL) {
- // Avoid using the standard convertlet for EXTRACT(TIMEUNIT FROM col),
since we want to handle it directly
- // in ExtractOperationConversion.
- return BYPASS_CONVERTLET;
- } else {
- final SqlRexConvertlet convertlet = table.get(call.getOperator());
- return convertlet != null ? convertlet :
StandardConvertletTable.INSTANCE.get(call);
- }
- }
-
- public static List<SqlOperator> knownOperators()
- {
- final ArrayList<SqlOperator> retVal = new ArrayList<>(
- CURRENT_TIME_CONVERTLET_OPERATORS.size() +
STANDARD_CONVERTLET_OPERATORS.size()
- );
-
- retVal.addAll(CURRENT_TIME_CONVERTLET_OPERATORS);
- retVal.addAll(STANDARD_CONVERTLET_OPERATORS);
-
- return retVal;
+ return new CurrentTimestampAndFriendsConvertlet(plannerContext);
}
- private static Map<SqlOperator, SqlRexConvertlet> createConvertletMap(final
PlannerContext plannerContext)
+ @Override
+ public List<SqlOperator> operators()
{
- final SqlRexConvertlet currentTimestampAndFriends = new
CurrentTimestampAndFriendsConvertlet(plannerContext);
- final Map<SqlOperator, SqlRexConvertlet> table = new HashMap<>();
-
- for (SqlOperator operator : CURRENT_TIME_CONVERTLET_OPERATORS) {
- table.put(operator, currentTimestampAndFriends);
- }
-
- return table;
+ return SQL_OPERATORS;
}
private static class CurrentTimestampAndFriendsConvertlet implements
SqlRexConvertlet
{
private final PlannerContext plannerContext;
- public CurrentTimestampAndFriendsConvertlet(final PlannerContext
plannerContext)
+ private CurrentTimestampAndFriendsConvertlet(PlannerContext plannerContext)
{
this.plannerContext = plannerContext;
}
@@ -177,4 +127,29 @@ public class DruidConvertletTable implements
SqlRexConvertletTable
}
}
}
+
+ /**
+ * Similar to {@link SqlAbstractTimeFunction}, but default precision is
+ * {@link DruidTypeSystem#DEFAULT_TIMESTAMP_PRECISION} instead of 0.
+ */
+ private static class CurrentTimestampSqlFunction extends
SqlAbstractTimeFunction
+ {
+ private final SqlTypeName typeName;
+
+ public CurrentTimestampSqlFunction(final String name, final SqlTypeName
typeName)
+ {
+ super(name, typeName);
+ this.typeName = typeName;
+ }
+
+ @Override
+ public RelDataType inferReturnType(SqlOperatorBinding opBinding)
+ {
+ if (opBinding.getOperandCount() == 0) {
+ return opBinding.getTypeFactory().createSqlType(typeName,
DruidTypeSystem.DEFAULT_TIMESTAMP_PRECISION);
+ } else {
+ return super.inferReturnType(opBinding);
+ }
+ }
+ }
}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/DruidConvertletFactory.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/DruidConvertletFactory.java
new file mode 100644
index 0000000000..c9dd5404a1
--- /dev/null
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/DruidConvertletFactory.java
@@ -0,0 +1,36 @@
+/*
+ * 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.druid.sql.calcite.planner.convertlet;
+
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql2rel.SqlRexConvertlet;
+import org.apache.druid.sql.calcite.planner.PlannerContext;
+
+import java.util.List;
+
+public interface DruidConvertletFactory
+{
+ SqlRexConvertlet createConvertlet(PlannerContext plannerContext);
+
+ /**
+ * Operators that this convertlet can handle.
+ */
+ List<SqlOperator> operators();
+}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/DruidConvertletTable.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/DruidConvertletTable.java
new file mode 100644
index 0000000000..3f5652d9aa
--- /dev/null
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/DruidConvertletTable.java
@@ -0,0 +1,115 @@
+/*
+ * 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.druid.sql.calcite.planner.convertlet;
+
+import com.google.common.collect.ImmutableList;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql2rel.SqlRexConvertlet;
+import org.apache.calcite.sql2rel.SqlRexConvertletTable;
+import org.apache.calcite.sql2rel.StandardConvertletTable;
+import org.apache.druid.sql.calcite.planner.PlannerContext;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class DruidConvertletTable implements SqlRexConvertletTable
+{
+ // Apply a convertlet that doesn't do anything other than a "dumb" call
translation.
+ private static final SqlRexConvertlet BYPASS_CONVERTLET =
StandardConvertletTable.INSTANCE::convertCall;
+
+ private static final List<DruidConvertletFactory> DRUID_CONVERTLET_FACTORIES
=
+ ImmutableList.<DruidConvertletFactory>builder()
+ .add(CurrentTimestampAndFriendsConvertletFactory.INSTANCE)
+ .add(TimeInIntervalConvertletFactory.INSTANCE)
+ .build();
+
+ // Operators we don't have standard conversions for, but which can be
converted into ones that do by
+ // Calcite's StandardConvertletTable.
+ private static final List<SqlOperator> STANDARD_CONVERTLET_OPERATORS =
+ ImmutableList.<SqlOperator>builder()
+ .add(SqlStdOperatorTable.ROW)
+ .add(SqlStdOperatorTable.NOT_IN)
+ .add(SqlStdOperatorTable.NOT_LIKE)
+ .add(SqlStdOperatorTable.BETWEEN)
+ .add(SqlStdOperatorTable.NOT_BETWEEN)
+ .add(SqlStdOperatorTable.SYMMETRIC_BETWEEN)
+ .add(SqlStdOperatorTable.SYMMETRIC_NOT_BETWEEN)
+ .add(SqlStdOperatorTable.ITEM)
+ .add(SqlStdOperatorTable.TIMESTAMP_ADD)
+ .add(SqlStdOperatorTable.TIMESTAMP_DIFF)
+ .add(SqlStdOperatorTable.UNION)
+ .add(SqlStdOperatorTable.UNION_ALL)
+ .add(SqlStdOperatorTable.NULLIF)
+ .add(SqlStdOperatorTable.COALESCE)
+ .add(SqlLibraryOperators.NVL)
+ .build();
+
+ private final Map<SqlOperator, SqlRexConvertlet> table;
+
+ public DruidConvertletTable(final PlannerContext plannerContext)
+ {
+ this.table = createConvertletMap(plannerContext);
+ }
+
+ @Override
+ public SqlRexConvertlet get(SqlCall call)
+ {
+ if (call.getKind() == SqlKind.EXTRACT &&
call.getOperandList().get(1).getKind() != SqlKind.LITERAL) {
+ // Avoid using the standard convertlet for EXTRACT(TIMEUNIT FROM col),
since we want to handle it directly
+ // in ExtractOperationConversion.
+ return BYPASS_CONVERTLET;
+ } else {
+ final SqlRexConvertlet convertlet = table.get(call.getOperator());
+ return convertlet != null ? convertlet :
StandardConvertletTable.INSTANCE.get(call);
+ }
+ }
+
+ public static List<SqlOperator> knownOperators()
+ {
+ final ArrayList<SqlOperator> retVal = new
ArrayList<>(STANDARD_CONVERTLET_OPERATORS);
+
+ for (final DruidConvertletFactory convertletFactory :
DRUID_CONVERTLET_FACTORIES) {
+ retVal.addAll(convertletFactory.operators());
+ }
+
+ return retVal;
+ }
+
+ private static Map<SqlOperator, SqlRexConvertlet> createConvertletMap(final
PlannerContext plannerContext)
+ {
+ final Map<SqlOperator, SqlRexConvertlet> table = new HashMap<>();
+
+ for (DruidConvertletFactory convertletFactory :
DRUID_CONVERTLET_FACTORIES) {
+ final SqlRexConvertlet convertlet =
convertletFactory.createConvertlet(plannerContext);
+
+ for (final SqlOperator operator : convertletFactory.operators()) {
+ table.put(operator, convertlet);
+ }
+ }
+
+ return table;
+ }
+}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/TimeInIntervalConvertletFactory.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/TimeInIntervalConvertletFactory.java
new file mode 100644
index 0000000000..5201d1e8e6
--- /dev/null
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/convertlet/TimeInIntervalConvertletFactory.java
@@ -0,0 +1,153 @@
+/*
+ * 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.druid.sql.calcite.planner.convertlet;
+
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexLiteral;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.SqlTypeFamily;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql2rel.SqlRexContext;
+import org.apache.calcite.sql2rel.SqlRexConvertlet;
+import org.apache.calcite.util.Static;
+import org.apache.druid.java.util.common.IAE;
+import org.apache.druid.sql.calcite.expression.OperatorConversions;
+import org.apache.druid.sql.calcite.planner.Calcites;
+import org.apache.druid.sql.calcite.planner.DruidTypeSystem;
+import org.apache.druid.sql.calcite.planner.PlannerContext;
+import org.joda.time.DateTimeZone;
+import org.joda.time.Interval;
+import org.joda.time.chrono.ISOChronology;
+
+import java.util.Collections;
+import java.util.List;
+
+public class TimeInIntervalConvertletFactory implements DruidConvertletFactory
+{
+ public static final TimeInIntervalConvertletFactory INSTANCE = new
TimeInIntervalConvertletFactory();
+
+ private static final String NAME = "TIME_IN_INTERVAL";
+
+ private static final SqlOperator OPERATOR = OperatorConversions
+ .operatorBuilder(NAME)
+ .operandTypeChecker(
+ OperandTypes.sequence(
+ NAME + "(<TIMESTAMP>, <LITERAL ISO8601 INTERVAL>)",
+ OperandTypes.family(SqlTypeFamily.TIMESTAMP),
+ OperandTypes.and(OperandTypes.family(SqlTypeFamily.CHARACTER),
OperandTypes.LITERAL)
+ )
+ )
+ .returnTypeNonNull(SqlTypeName.BOOLEAN)
+ .functionCategory(SqlFunctionCategory.TIMEDATE)
+ .build();
+
+ private TimeInIntervalConvertletFactory()
+ {
+ // Singleton.
+ }
+
+ @Override
+ public SqlRexConvertlet createConvertlet(PlannerContext plannerContext)
+ {
+ return new TimeInIntervalConvertlet(plannerContext.getTimeZone());
+ }
+
+ @Override
+ public List<SqlOperator> operators()
+ {
+ return Collections.singletonList(OPERATOR);
+ }
+
+ private static Interval intervalFromStringArgument(
+ final SqlParserPos parserPos,
+ final String intervalString,
+ final DateTimeZone sessionTimeZone
+ )
+ {
+ try {
+ return new Interval(intervalString,
ISOChronology.getInstance(sessionTimeZone));
+ }
+ catch (IllegalArgumentException e) {
+ final RuntimeException ex =
+ new IAE("Function '%s' second argument is not a valid ISO8601
interval: %s", NAME, e.getMessage());
+
+ throw Static.RESOURCE.validatorContext(
+ parserPos.getLineNum(),
+ parserPos.getColumnNum(),
+ parserPos.getEndLineNum(),
+ parserPos.getEndColumnNum()
+ ).ex(ex);
+ }
+ }
+
+ private static class TimeInIntervalConvertlet implements SqlRexConvertlet
+ {
+ private final DateTimeZone sessionTimeZone;
+
+ private TimeInIntervalConvertlet(final DateTimeZone sessionTimeZone)
+ {
+ this.sessionTimeZone = sessionTimeZone;
+ }
+
+ @Override
+ public RexNode convertCall(final SqlRexContext cx, final SqlCall call)
+ {
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ final RexNode timeOperand =
cx.convertExpression(call.getOperandList().get(0));
+ final RexNode intervalOperand =
cx.convertExpression(call.getOperandList().get(1));
+
+ final Interval interval = intervalFromStringArgument(
+ call.getParserPosition(),
+ RexLiteral.stringValue(intervalOperand),
+ sessionTimeZone
+ );
+
+ final RexNode lowerBound = rexBuilder.makeCall(
+ SqlStdOperatorTable.GREATER_THAN_OR_EQUAL,
+ timeOperand,
+ Calcites.jodaToCalciteTimestampLiteral(
+ rexBuilder,
+ interval.getStart(),
+ sessionTimeZone,
+ DruidTypeSystem.DEFAULT_TIMESTAMP_PRECISION
+ )
+ );
+
+ final RexNode upperBound = rexBuilder.makeCall(
+ SqlStdOperatorTable.LESS_THAN,
+ timeOperand,
+ Calcites.jodaToCalciteTimestampLiteral(
+ rexBuilder,
+ interval.getEnd(),
+ sessionTimeZone,
+ DruidTypeSystem.DEFAULT_TIMESTAMP_PRECISION
+ )
+ );
+
+ return rexBuilder.makeCall(SqlStdOperatorTable.AND, lowerBound,
upperBound);
+ }
+ }
+}
diff --git
a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
index f14a79e598..7aef54503f 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
@@ -24,6 +24,7 @@ import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableSet;
import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.runtime.CalciteContextException;
import org.apache.druid.common.config.NullHandling;
import org.apache.druid.java.util.common.DateTimes;
import org.apache.druid.java.util.common.HumanReadableBytes;
@@ -115,6 +116,7 @@ import org.apache.druid.sql.calcite.planner.PlannerConfig;
import org.apache.druid.sql.calcite.planner.PlannerContext;
import org.apache.druid.sql.calcite.rel.CannotBuildQueryException;
import org.apache.druid.sql.calcite.util.CalciteTests;
+import org.hamcrest.CoreMatchers;
import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.Interval;
@@ -122,6 +124,7 @@ import org.joda.time.Period;
import org.junit.Assert;
import org.junit.Ignore;
import org.junit.Test;
+import org.junit.internal.matchers.ThrowableMessageMatcher;
import java.util.ArrayList;
import java.util.Arrays;
@@ -5555,6 +5558,82 @@ public class CalciteQueryTest extends
BaseCalciteQueryTest
);
}
+ @Test
+ public void testCountStarWithTimeInIntervalFilter() throws Exception
+ {
+ testQuery(
+ "SELECT COUNT(*) FROM druid.foo "
+ + "WHERE TIME_IN_INTERVAL(__time, '2000-01-01/P1Y') "
+ + "AND TIME_IN_INTERVAL(CURRENT_TIMESTAMP, '2000/3000') -- Optimized
away: always true",
+ ImmutableList.of(
+ Druids.newTimeseriesQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+
.intervals(querySegmentSpec(Intervals.of("2000-01-01/2001-01-01")))
+ .granularity(Granularities.ALL)
+ .aggregators(aggregators(new CountAggregatorFactory("a0")))
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{3L}
+ )
+ );
+ }
+
+ @Test
+ public void testCountStarWithTimeInIntervalFilterLosAngeles() throws
Exception
+ {
+ testQuery(
+ "SELECT COUNT(*) FROM druid.foo "
+ + "WHERE TIME_IN_INTERVAL(__time, '2000-01-01/P1Y')",
+ QUERY_CONTEXT_LOS_ANGELES,
+ ImmutableList.of(
+ Druids.newTimeseriesQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+
.intervals(querySegmentSpec(Intervals.of("2000-01-01T08:00:00/2001-01-01T08:00:00")))
+ .granularity(Granularities.ALL)
+ .aggregators(aggregators(new CountAggregatorFactory("a0")))
+ .context(QUERY_CONTEXT_LOS_ANGELES)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{3L}
+ )
+ );
+ }
+
+ @Test
+ public void testCountStarWithTimeInIntervalFilterInvalidInterval() throws
Exception
+ {
+ testQueryThrows(
+ "SELECT COUNT(*) FROM druid.foo "
+ + "WHERE TIME_IN_INTERVAL(__time, '2000-01-01/X')",
+ expected -> {
+
expected.expect(CoreMatchers.instanceOf(CalciteContextException.class));
+
expected.expect(ThrowableMessageMatcher.hasMessage(CoreMatchers.containsString(
+ "From line 1, column 38 to line 1, column 77: "
+ + "Function 'TIME_IN_INTERVAL' second argument is not a valid
ISO8601 interval: "
+ + "Invalid format: \"X\"")));
+ }
+ );
+ }
+
+ @Test
+ public void testCountStarWithTimeInIntervalFilterNonLiteral() throws
Exception
+ {
+ testQueryThrows(
+ "SELECT COUNT(*) FROM druid.foo "
+ + "WHERE TIME_IN_INTERVAL(__time, dim1)",
+ expected -> {
+ expected.expect(CoreMatchers.instanceOf(SqlPlanningException.class));
+
expected.expect(ThrowableMessageMatcher.hasMessage(CoreMatchers.containsString(
+ "From line 1, column 38 to line 1, column 67: "
+ + "Cannot apply 'TIME_IN_INTERVAL' to arguments of type
'TIME_IN_INTERVAL(<TIMESTAMP(3)>, <VARCHAR>)'. "
+ + "Supported form(s): TIME_IN_INTERVAL(<TIMESTAMP>, <LITERAL
ISO8601 INTERVAL>)")));
+ }
+ );
+ }
+
@Test
public void testCountStarWithBetweenTimeFilterUsingMilliseconds() throws
Exception
{
diff --git a/website/.spelling b/website/.spelling
index 44b7fc3349..c9de140f80 100644
--- a/website/.spelling
+++ b/website/.spelling
@@ -495,11 +495,13 @@ ISOYEAR
IS_NULLABLE
JDBC_TYPE
MIDDLE_MANAGER
+MILLIS_TO_TIMESTAMP
NULLable
NUMERIC_PRECISION
NUMERIC_PRECISION_RADIX
NUMERIC_SCALE
ORDINAL_POSITION
+POSIX
PT1M
PT5M
SCHEMA_NAME
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
