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 2023/04/28 06:21:13 UTC
[calcite] branch main updated (dd8fa24a6a -> 3326475c76)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a change to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
from dd8fa24a6a [CALCITE-5671] Add option to disable SSL certificate validation to ES adapter
new bbc2cec5e8 [CALCITE-5543] Add functions PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP (enabled in BigQuery library)
new 07a29b6071 [CALCITE-4698] Result type of datetime '+' operators and TIMESTAMPADD function should be the same as the operand type
new 3326475c76 [CALCITE-5614] Serialize Sarg values to and from JSON
The 3 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
babel/src/test/resources/sql/big-query.iq | 163 ++++++++------
.../calcite/adapter/enumerable/RexImpTable.java | 16 +-
.../calcite/rel/externalize/RelEnumTypes.java | 2 +
.../apache/calcite/rel/externalize/RelJson.java | 237 ++++++++++++++++++++-
.../calcite/rel/externalize/RelJsonReader.java | 11 +
.../calcite/rel/type/RelDataTypeFactoryImpl.java | 27 ++-
.../java/org/apache/calcite/rex/RexBuilder.java | 9 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 90 +++++++-
.../java/org/apache/calcite/sql/SqlCollation.java | 11 +-
.../calcite/sql/fun/SqlLibraryOperators.java | 43 ++++
.../calcite/sql/fun/SqlTimestampAddFunction.java | 47 ++--
.../org/apache/calcite/sql/type/ReturnTypes.java | 7 +
.../calcite/sql/type/SqlTypeFactoryImpl.java | 9 +-
.../java/org/apache/calcite/util/DateString.java | 8 +-
.../java/org/apache/calcite/util/JsonBuilder.java | 2 +-
.../java/org/apache/calcite/util/NlsString.java | 18 +-
.../java/org/apache/calcite/util/RangeSets.java | 110 ++++++++--
.../java/org/apache/calcite/util/TimeString.java | 6 +-
.../apache/calcite/util/format/FormatElement.java | 18 +-
.../calcite/util/format/FormatElementEnum.java | 197 +++++++++--------
.../apache/calcite/util/format/FormatModels.java | 28 ++-
.../org/apache/calcite/plan/RelWriterTest.java | 95 ++++++++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 32 +++
.../java/org/apache/calcite/util/RangeSetTest.java | 57 +++++
.../calcite/util/format/FormatElementEnumTest.java | 5 +-
site/_docs/reference.md | 4 +
.../apache/calcite/sql/test/ResultCheckers.java | 15 ++
.../org/apache/calcite/test/SqlOperatorTest.java | 152 ++++++++++++-
28 files changed, 1167 insertions(+), 252 deletions(-)
[calcite] 03/03: [CALCITE-5614] Serialize Sarg values to and from JSON
Posted by jh...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 3326475c766267d521330006cc80730c4e456191
Author: Oliver Lee <ol...@google.com>
AuthorDate: Tue Mar 14 18:04:47 2023 -0700
[CALCITE-5614] Serialize Sarg values to and from JSON
Close apache/calcite#3140
Co-authored-by: Oliver Lee <ol...@google.com>
Co-authored-by: Julian Hyde <jh...@apache.org>
---
.../calcite/rel/externalize/RelEnumTypes.java | 2 +
.../apache/calcite/rel/externalize/RelJson.java | 237 ++++++++++++++++++++-
.../calcite/rel/externalize/RelJsonReader.java | 11 +
.../java/org/apache/calcite/rex/RexBuilder.java | 9 +-
.../java/org/apache/calcite/sql/SqlCollation.java | 11 +-
.../java/org/apache/calcite/util/DateString.java | 8 +-
.../java/org/apache/calcite/util/JsonBuilder.java | 2 +-
.../java/org/apache/calcite/util/NlsString.java | 18 +-
.../java/org/apache/calcite/util/RangeSets.java | 110 ++++++++--
.../java/org/apache/calcite/util/TimeString.java | 6 +-
.../org/apache/calcite/plan/RelWriterTest.java | 95 ++++++++-
.../java/org/apache/calcite/util/RangeSetTest.java | 57 +++++
12 files changed, 526 insertions(+), 40 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java b/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java
index 97181d035d..b4ad2d1b90 100644
--- a/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java
+++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java
@@ -18,6 +18,7 @@ package org.apache.calcite.rel.externalize;
import org.apache.calcite.avatica.util.TimeUnitRange;
import org.apache.calcite.rel.core.TableModify;
+import org.apache.calcite.rex.RexUnknownAs;
import org.apache.calcite.sql.JoinConditionType;
import org.apache.calcite.sql.JoinType;
import org.apache.calcite.sql.SqlExplain;
@@ -66,6 +67,7 @@ public abstract class RelEnumTypes {
ImmutableMap.builder();
register(enumByName, JoinConditionType.class);
register(enumByName, JoinType.class);
+ register(enumByName, RexUnknownAs.class);
register(enumByName, SqlExplain.Depth.class);
register(enumByName, SqlExplainFormat.class);
register(enumByName, SqlExplainLevel.class);
diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
index c3f8a7c2ca..0987f1f288 100644
--- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
+++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
@@ -17,6 +17,7 @@
package org.apache.calcite.rel.externalize;
import org.apache.calcite.avatica.AvaticaUtils;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.avatica.util.TimeUnit;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptTable;
@@ -62,13 +63,25 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlNameMatchers;
+import org.apache.calcite.util.DateString;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.JsonBuilder;
+import org.apache.calcite.util.NlsString;
+import org.apache.calcite.util.RangeSets;
+import org.apache.calcite.util.Sarg;
+import org.apache.calcite.util.TimeString;
import org.apache.calcite.util.Util;
+import com.fasterxml.jackson.core.JsonProcessingException;
+import com.fasterxml.jackson.databind.DeserializationFeature;
+import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableRangeSet;
+import com.google.common.collect.Range;
+import com.google.common.collect.RangeSet;
+import org.checkerframework.checker.nullness.qual.NonNull;
import org.checkerframework.checker.nullness.qual.Nullable;
import org.checkerframework.checker.nullness.qual.PolyNull;
@@ -93,6 +106,14 @@ import static java.util.Objects.requireNonNull;
* into JSON format.
*/
public class RelJson {
+ private static final ObjectMapper OBJECT_MAPPER =
+ new ObjectMapper()
+ .configure(DeserializationFeature.USE_BIG_DECIMAL_FOR_FLOATS, true);
+
+ private static final List<Class> VALUE_CLASSES =
+ ImmutableList.of(NlsString.class, BigDecimal.class, ByteString.class,
+ Boolean.class, DateString.class, TimeString.class);
+
private final Map<String, Constructor> constructorMap = new HashMap<>();
private final @Nullable JsonBuilder jsonBuilder;
private final InputTranslator inputTranslator;
@@ -422,6 +443,7 @@ public class RelJson {
return map;
}
+ @SuppressWarnings({"BetaApi", "UnstableApiUsage"}) // RangeSet GA in Guava 32
public @Nullable Object toJson(@Nullable Object value) {
if (value == null
|| value instanceof Number
@@ -460,12 +482,47 @@ public class RelJson {
return toJson((RelDataTypeField) value);
} else if (value instanceof RelDistribution) {
return toJson((RelDistribution) value);
+ } else if (value instanceof Sarg) {
+ //noinspection unchecked,rawtypes
+ return toJson((Sarg) value);
+ } else if (value instanceof RangeSet) {
+ //noinspection unchecked,rawtypes
+ return toJson((RangeSet) value);
+ } else if (value instanceof Range) {
+ //noinspection rawtypes,unchecked
+ return toJson((Range) value);
} else {
throw new UnsupportedOperationException("type not serializable: "
+ value + " (type " + value.getClass().getCanonicalName() + ")");
}
}
+ public <C extends Comparable<C>> Object toJson(Sarg<C> node) {
+ final Map<String, @Nullable Object> map = jsonBuilder().map();
+ map.put("rangeSet", toJson(node.rangeSet));
+ map.put("nullAs", RelEnumTypes.fromEnum(node.nullAs));
+ return map;
+ }
+
+ @SuppressWarnings({"BetaApi", "UnstableApiUsage"}) // RangeSet GA in Guava 32
+ public <C extends Comparable<C>> List<List<String>> toJson(
+ RangeSet<C> rangeSet) {
+ final List<List<String>> list = new ArrayList<>();
+ try {
+ RangeSets.forEach(rangeSet,
+ RangeToJsonConverter.<C>instance().andThen(list::add));
+ } catch (Exception e) {
+ throw new RuntimeException("Failed to serialize RangeSet: ", e);
+ }
+ return list;
+ }
+
+ /** Serializes a {@link Range} that can be deserialized using
+ * {@link RelJson#rangeFromJson(List)}. */
+ public <C extends Comparable<C>> List<String> toJson(Range<C> range) {
+ return RangeSets.map(range, RangeToJsonConverter.instance());
+ }
+
private Object toJson(RelDataType node) {
final Map<String, @Nullable Object> map = jsonBuilder().map();
if (node.isStruct()) {
@@ -517,7 +574,7 @@ public class RelJson {
return node.getId();
}
- private Object toJson(RexNode node) {
+ public Object toJson(RexNode node) {
final Map<String, @Nullable Object> map;
switch (node.getKind()) {
case FIELD_ACCESS:
@@ -530,7 +587,11 @@ public class RelJson {
final RexLiteral literal = (RexLiteral) node;
final Object value = literal.getValue3();
map = jsonBuilder().map();
- map.put("literal", RelEnumTypes.fromEnum(value));
+ //noinspection rawtypes
+ map.put("literal",
+ value instanceof Enum
+ ? RelEnumTypes.fromEnum((Enum) value)
+ : toJson(value));
map.put("type", toJson(node.getType()));
return map;
case INPUT_REF:
@@ -657,7 +718,8 @@ public class RelJson {
final RexBuilder rexBuilder = cluster.getRexBuilder();
if (o == null) {
return null;
- } else if (o instanceof Map) {
+ // Support JSON deserializing of non-default Map classes such as gson LinkedHashMap
+ } else if (Map.class.isAssignableFrom(o.getClass())) {
final Map<String, @Nullable Object> map = (Map) o;
final RelDataTypeFactory typeFactory = cluster.getTypeFactory();
if (map.containsKey("op")) {
@@ -746,11 +808,26 @@ public class RelJson {
return toRex(relInput, literal);
}
final RelDataType type = toType(typeFactory, get(map, "type"));
+ if (literal instanceof Map
+ && ((Map<?, ?>) literal).containsKey("rangeSet")) {
+ Sarg sarg = sargFromJson((Map) literal);
+ return rexBuilder.makeSearchArgumentLiteral(sarg, type);
+ }
if (type.getSqlTypeName() == SqlTypeName.SYMBOL) {
literal = RelEnumTypes.toEnum((String) literal);
}
return rexBuilder.makeLiteral(literal, type);
}
+ if (map.containsKey("sargLiteral")) {
+ Object sargObject = map.get("sargLiteral");
+ if (sargObject == null) {
+ final RelDataType type = toType(typeFactory, get(map, "type"));
+ return rexBuilder.makeNullLiteral(type);
+ }
+ final RelDataType type = toType(typeFactory, get(map, "type"));
+ Sarg sarg = sargFromJson((Map) sargObject);
+ return rexBuilder.makeSearchArgumentLiteral(sarg, type);
+ }
throw new UnsupportedOperationException("cannot convert to rex " + o);
} else if (o instanceof Boolean) {
return rexBuilder.makeLiteral((Boolean) o);
@@ -770,8 +847,91 @@ public class RelJson {
}
}
- private void addRexFieldCollationList(
- List<RexFieldCollation> list,
+ /** Converts a JSON object to a {@code Sarg}.
+ *
+ * <p>For example,
+ * {@code {rangeSet: [["[", 0, 5, "]"], ["[", 10, "-", ")"]],
+ * nullAs: "UNKNOWN"}} represents the range x ≥ 0 and x ≤ 5 or
+ * x > 10.
+ */
+ // BetaApi is no longer a concern; the Beta tag was removed in Guava 32.0
+ @SuppressWarnings({"BetaApi", "unchecked"})
+ public static <C extends Comparable<C>> Sarg<C> sargFromJson(
+ Map<String, Object> map) {
+ final String nullAs = requireNonNull((String) map.get("nullAs"), "nullAs");
+ final List<List<String>> rangeSet =
+ requireNonNull((List<List<String>>) map.get("rangeSet"), "rangeSet");
+ return Sarg.of(RelEnumTypes.toEnum(nullAs),
+ RelJson.<C>rangeSetFromJson(rangeSet));
+ }
+
+ /** Converts a JSON list to a {@link RangeSet}. */
+ @SuppressWarnings({"BetaApi", "UnstableApiUsage"}) // RangeSet GA in Guava 32
+ public static <C extends Comparable<C>> RangeSet<C> rangeSetFromJson(
+ List<List<String>> rangeSetsJson) {
+ final ImmutableRangeSet.Builder<C> builder = ImmutableRangeSet.builder();
+ try {
+ rangeSetsJson.forEach(list -> builder.add(rangeFromJson(list)));
+ } catch (Exception e) {
+ throw new RuntimeException("Error creating RangeSet from JSON: ", e);
+ }
+ return builder.build();
+ }
+
+ /** Creates a {@link Range} from a JSON object.
+ *
+ * <p>The JSON object is as serialized using {@link RelJson#toJson(Range)},
+ * e.g. {@code ["[", ")", 10, "-"]}.
+ *
+ * @see RangeToJsonConverter */
+ public static <C extends Comparable<C>> Range<C> rangeFromJson(
+ List<String> list) {
+ switch (list.get(0)) {
+ case "all":
+ return Range.all();
+ case "atLeast":
+ return Range.atLeast(rangeEndPointFromJson(list.get(1)));
+ case "atMost":
+ return Range.atMost(rangeEndPointFromJson(list.get(1)));
+ case "greaterThan":
+ return Range.greaterThan(rangeEndPointFromJson(list.get(1)));
+ case "lessThan":
+ return Range.lessThan(rangeEndPointFromJson(list.get(1)));
+ case "singleton":
+ return Range.singleton(rangeEndPointFromJson(list.get(1)));
+ case "closed":
+ return Range.closed(rangeEndPointFromJson(list.get(1)),
+ rangeEndPointFromJson(list.get(2)));
+ case "closedOpen":
+ return Range.closedOpen(rangeEndPointFromJson(list.get(1)),
+ rangeEndPointFromJson(list.get(2)));
+ case "openClosed":
+ return Range.openClosed(rangeEndPointFromJson(list.get(1)),
+ rangeEndPointFromJson(list.get(2)));
+ case "open":
+ return Range.open(rangeEndPointFromJson(list.get(1)),
+ rangeEndPointFromJson(list.get(2)));
+ default:
+ throw new AssertionError("unknown range type " + list.get(0));
+ }
+ }
+
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ private static <C extends Comparable<C>> C rangeEndPointFromJson(Object o) {
+ Exception e = null;
+ for (Class clsType : VALUE_CLASSES) {
+ try {
+ return (C) OBJECT_MAPPER.readValue((String) o, clsType);
+ } catch (JsonProcessingException ex) {
+ e = ex;
+ }
+ }
+ throw new RuntimeException(
+ "Error deserializing range endpoint (did not find compatible type): ",
+ e);
+ }
+
+ private void addRexFieldCollationList(List<RexFieldCollation> list,
RelInput relInput, @Nullable List<Map<String, Object>> order) {
if (order == null) {
return;
@@ -1005,4 +1165,71 @@ public class RelJson {
RexNode translateInput(RelJson relJson, int input,
Map<String, @Nullable Object> map, RelInput relInput);
}
+
+ /** Implementation of {@link RangeSets.Handler} that converts a {@link Range}
+ * event to a list of strings.
+ *
+ * @param <V> Range value type
+ */
+ private static class RangeToJsonConverter<V>
+ implements RangeSets.Handler<@NonNull V, List<String>> {
+ @SuppressWarnings("rawtypes")
+ private static final RangeToJsonConverter INSTANCE =
+ new RangeToJsonConverter<>();
+
+ private static <C extends Comparable<C>> RangeToJsonConverter<C> instance() {
+ //noinspection unchecked
+ return INSTANCE;
+ }
+
+ @Override public List<String> all() {
+ return ImmutableList.of("all");
+ }
+
+ @Override public List<String> atLeast(@NonNull V lower) {
+ return ImmutableList.of("atLeast", toJson(lower));
+ }
+
+ @Override public List<String> atMost(@NonNull V upper) {
+ return ImmutableList.of("atMost", toJson(upper));
+ }
+
+ @Override public List<String> greaterThan(@NonNull V lower) {
+ return ImmutableList.of("greaterThan", toJson(lower));
+ }
+
+ @Override public List<String> lessThan(@NonNull V upper) {
+ return ImmutableList.of("lessThan", toJson(upper));
+ }
+
+ @Override public List<String> singleton(@NonNull V value) {
+ return ImmutableList.of("singleton", toJson(value));
+ }
+
+ @Override public List<String> closed(@NonNull V lower, @NonNull V upper) {
+ return ImmutableList.of("closed", toJson(lower), toJson(upper));
+ }
+
+ @Override public List<String> closedOpen(@NonNull V lower,
+ @NonNull V upper) {
+ return ImmutableList.of("closedOpen", toJson(lower), toJson(upper));
+ }
+
+ @Override public List<String> openClosed(@NonNull V lower,
+ @NonNull V upper) {
+ return ImmutableList.of("openClosed", toJson(lower), toJson(upper));
+ }
+
+ @Override public List<String> open(@NonNull V lower, @NonNull V upper) {
+ return ImmutableList.of("open", toJson(lower), toJson(upper));
+ }
+
+ private static String toJson(Object o) {
+ try {
+ return OBJECT_MAPPER.writeValueAsString(o);
+ } catch (JsonProcessingException e) {
+ throw new RuntimeException("Failed to serialize Range endpoint: ", e);
+ }
+ }
+ }
}
diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java b/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java
index a98d0e8848..8b6d1e0f03 100644
--- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java
+++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java
@@ -109,6 +109,17 @@ public class RelJsonReader {
return RelJson.create().toType(typeFactory, o);
}
+ /** Converts a JSON string (such as that produced by
+ * {@link RelJson#toJson(RexNode)}) into a Calcite expression. */
+ public static RexNode readRex(RelOptCluster typeFactory, String s)
+ throws IOException {
+ final ObjectMapper mapper = new ObjectMapper();
+ Map<String, Object> o = mapper
+ .configure(DeserializationFeature.USE_BIG_DECIMAL_FOR_FLOATS, true)
+ .readValue(s, TYPE_REF);
+ return RelJson.create().toRex(typeFactory, o);
+ }
+
private void readRels(List<Map<String, Object>> jsonRels) {
for (Map<String, Object> jsonRel : jsonRels) {
readRel(jsonRel);
diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
index c19a1001c6..fda55faa0c 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
@@ -1638,6 +1638,10 @@ public class RexBuilder {
case INTEGER:
case BIGINT:
case DECIMAL:
+ if (value instanceof RexLiteral
+ && ((RexLiteral) value).getTypeName() == SqlTypeName.SARG) {
+ return (RexNode) value;
+ }
return makeExactLiteral((BigDecimal) value, type);
case FLOAT:
case REAL:
@@ -1736,10 +1740,13 @@ public class RexBuilder {
* {@link org.apache.calcite.rex.RexLiteral#valueMatchesType}.
*
* <p>Returns null if and only if {@code o} is null. */
- private static @PolyNull Object clean(@PolyNull Object o, RelDataType type) {
+ private @PolyNull Object clean(@PolyNull Object o, RelDataType type) {
if (o == null) {
return o;
}
+ if (o instanceof Sarg) {
+ return makeSearchArgumentLiteral((Sarg) o, type);
+ }
switch (type.getSqlTypeName()) {
case TINYINT:
case SMALLINT:
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlCollation.java b/core/src/main/java/org/apache/calcite/sql/SqlCollation.java
index b6576d4edc..b02035f791 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlCollation.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlCollation.java
@@ -22,6 +22,10 @@ import org.apache.calcite.util.Glossary;
import org.apache.calcite.util.SerializableCharset;
import org.apache.calcite.util.Util;
+import com.fasterxml.jackson.annotation.JsonCreator;
+import com.fasterxml.jackson.annotation.JsonIgnore;
+import com.fasterxml.jackson.annotation.JsonProperty;
+
import org.checkerframework.checker.initialization.qual.UnderInitialization;
import org.checkerframework.checker.nullness.qual.Nullable;
import org.checkerframework.dataflow.qual.Pure;
@@ -95,9 +99,10 @@ public class SqlCollation implements Serializable {
* @param collation Collation specification
* @param coercibility Coercibility
*/
+ @JsonCreator
public SqlCollation(
- String collation,
- Coercibility coercibility) {
+ @JsonProperty("collationName") String collation,
+ @JsonProperty("coercibility") Coercibility coercibility) {
this.coercibility = coercibility;
SqlParserUtil.ParsedCollation parseValues =
SqlParserUtil.parseCollation(collation);
@@ -290,6 +295,7 @@ public class SqlCollation implements Serializable {
writer.identifier(collationName, false);
}
+ @JsonIgnore
public Charset getCharset() {
return wrappedCharset.getCharset();
}
@@ -312,6 +318,7 @@ public class SqlCollation implements Serializable {
* which case {@link String#compareTo} will be used.
*/
@Pure
+ @JsonIgnore
public @Nullable Collator getCollator() {
return null;
}
diff --git a/core/src/main/java/org/apache/calcite/util/DateString.java b/core/src/main/java/org/apache/calcite/util/DateString.java
index 33e3675353..2dc19b5def 100644
--- a/core/src/main/java/org/apache/calcite/util/DateString.java
+++ b/core/src/main/java/org/apache/calcite/util/DateString.java
@@ -18,6 +18,9 @@ package org.apache.calcite.util;
import org.apache.calcite.avatica.util.DateTimeUtils;
+import com.fasterxml.jackson.annotation.JsonCreator;
+import com.fasterxml.jackson.annotation.JsonIgnore;
+import com.fasterxml.jackson.annotation.JsonProperty;
import com.google.common.base.Preconditions;
import org.checkerframework.checker.nullness.qual.Nullable;
@@ -120,12 +123,15 @@ public class DateString implements Comparable<DateString> {
}
/** Creates a DateString that is a given number of days since the epoch. */
- public static DateString fromDaysSinceEpoch(int days) {
+ @JsonCreator
+ public static DateString fromDaysSinceEpoch(
+ @JsonProperty("daysSinceEpoch") int days) {
return new DateString(DateTimeUtils.unixDateToString(days));
}
/** Returns the number of milliseconds since the epoch. Always a multiple of
* 86,400,000 (the number of milliseconds in a day). */
+ @JsonIgnore
public long getMillisSinceEpoch() {
return getDaysSinceEpoch() * DateTimeUtils.MILLIS_PER_DAY;
}
diff --git a/core/src/main/java/org/apache/calcite/util/JsonBuilder.java b/core/src/main/java/org/apache/calcite/util/JsonBuilder.java
index 68496fb161..eeb59699cf 100644
--- a/core/src/main/java/org/apache/calcite/util/JsonBuilder.java
+++ b/core/src/main/java/org/apache/calcite/util/JsonBuilder.java
@@ -130,7 +130,7 @@ public class JsonBuilder {
} else if (o instanceof String) {
appendString(buf, (String) o);
} else {
- assert o instanceof Number || o instanceof Boolean;
+ assert o instanceof Number || o instanceof Boolean : o;
buf.append(o);
}
}
diff --git a/core/src/main/java/org/apache/calcite/util/NlsString.java b/core/src/main/java/org/apache/calcite/util/NlsString.java
index 327b699fc0..cbc87b6755 100644
--- a/core/src/main/java/org/apache/calcite/util/NlsString.java
+++ b/core/src/main/java/org/apache/calcite/util/NlsString.java
@@ -23,6 +23,8 @@ import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.dialect.AnsiSqlDialect;
+import com.fasterxml.jackson.annotation.JsonCreator;
+import com.fasterxml.jackson.annotation.JsonProperty;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.LoadingCache;
@@ -42,6 +44,8 @@ import java.util.Objects;
import static org.apache.calcite.util.Static.RESOURCE;
+import static java.util.Objects.requireNonNull;
+
/**
* A string, optionally with {@link Charset character set} and
* {@link SqlCollation}. It is immutable.
@@ -72,6 +76,7 @@ public class NlsString implements Comparable<NlsString>, Cloneable {
});
private final @Nullable String stringValue;
+ @JsonProperty("valueBytes")
private final @Nullable ByteString bytesValue;
private final @Nullable String charsetName;
private final @Nullable Charset charset;
@@ -94,8 +99,8 @@ public class NlsString implements Comparable<NlsString>, Cloneable {
*/
public NlsString(ByteString bytesValue, String charsetName,
@Nullable SqlCollation collation) {
- this(null, Objects.requireNonNull(bytesValue, "bytesValue"),
- Objects.requireNonNull(charsetName, "charsetName"), collation);
+ this(null, requireNonNull(bytesValue, "bytesValue"),
+ requireNonNull(charsetName, "charsetName"), collation);
}
/**
@@ -111,9 +116,12 @@ public class NlsString implements Comparable<NlsString>, Cloneable {
* @throws RuntimeException If the given value cannot be represented in the
* given charset
*/
- public NlsString(String stringValue, @Nullable String charsetName,
- @Nullable SqlCollation collation) {
- this(Objects.requireNonNull(stringValue, "stringValue"), null, charsetName, collation);
+ @JsonCreator
+ public NlsString(@JsonProperty("value") String stringValue,
+ @JsonProperty("charsetName") @Nullable String charsetName,
+ @JsonProperty("collation") @Nullable SqlCollation collation) {
+ this(requireNonNull(stringValue, "stringValue"), null, charsetName,
+ collation);
}
/** Internal constructor; other constructors must call it. */
diff --git a/core/src/main/java/org/apache/calcite/util/RangeSets.java b/core/src/main/java/org/apache/calcite/util/RangeSets.java
index 70b81686d4..a0967e2383 100644
--- a/core/src/main/java/org/apache/calcite/util/RangeSets.java
+++ b/core/src/main/java/org/apache/calcite/util/RangeSets.java
@@ -22,11 +22,15 @@ import com.google.common.collect.Range;
import com.google.common.collect.RangeSet;
import com.google.common.collect.TreeRangeSet;
+import org.checkerframework.checker.nullness.qual.NonNull;
+
import java.util.Iterator;
import java.util.Set;
import java.util.function.BiConsumer;
import java.util.function.Function;
+import static java.util.Objects.requireNonNull;
+
/** Utilities for Guava {@link com.google.common.collect.RangeSet}. */
@SuppressWarnings({"BetaApi", "UnstableApiUsage"})
public class RangeSets {
@@ -279,39 +283,101 @@ public class RangeSets {
/** Deconstructor for {@link Range} values.
*
- * @param <C> Value type
+ * @param <V> Value type
* @param <R> Return type
*
* @see Consumer */
- public interface Handler<C extends Comparable<C>, R> {
+ public interface Handler<V, R> {
R all();
- R atLeast(C lower);
- R atMost(C upper);
- R greaterThan(C lower);
- R lessThan(C upper);
- R singleton(C value);
- R closed(C lower, C upper);
- R closedOpen(C lower, C upper);
- R openClosed(C lower, C upper);
- R open(C lower, C upper);
+ R atLeast(V lower);
+ R atMost(V upper);
+ R greaterThan(V lower);
+ R lessThan(V upper);
+ R singleton(V value);
+ R closed(V lower, V upper);
+ R closedOpen(V lower, V upper);
+ R openClosed(V lower, V upper);
+ R open(V lower, V upper);
+
+ /** Creates a Consumer that sends output to a given sink. */
+ default Consumer<V> andThen(java.util.function.Consumer<R> consumer) {
+ return new SinkConsumer<>(this, consumer);
+ }
+ }
+
+ /** Consumer that deconstructs a range to a handler then sends the resulting
+ * range to a {@link java.util.function.Consumer}.
+ *
+ * @param <V> Value type
+ * @param <R> Output element type
+ */
+ private static class SinkConsumer<V, R> implements Consumer<V> {
+ final Handler<V, R> handler;
+ final java.util.function.Consumer<R> consumer;
+
+ SinkConsumer(Handler<V, R> handler,
+ java.util.function.Consumer<R> consumer) {
+ this.handler = requireNonNull(handler, "handler");
+ this.consumer = requireNonNull(consumer, "consumer");
+ }
+
+ @Override public void all() {
+ consumer.accept(handler.all());
+ }
+
+ @Override public void atLeast(V lower) {
+ consumer.accept(handler.atLeast(lower));
+ }
+
+ @Override public void atMost(V upper) {
+ consumer.accept(handler.atMost(upper));
+ }
+
+ @Override public void greaterThan(V lower) {
+ consumer.accept(handler.greaterThan(lower));
+ }
+
+ @Override public void lessThan(V upper) {
+ consumer.accept(handler.lessThan(upper));
+ }
+
+ @Override public void singleton(V value) {
+ consumer.accept(handler.singleton(value));
+ }
+
+ @Override public void closed(V lower, V upper) {
+ consumer.accept(handler.closed(lower, upper));
+ }
+
+ @Override public void closedOpen(V lower, V upper) {
+ consumer.accept(handler.closedOpen(lower, upper));
+ }
+
+ @Override public void openClosed(V lower, V upper) {
+ consumer.accept(handler.openClosed(lower, upper));
+ }
+
+ @Override public void open(V lower, V upper) {
+ consumer.accept(handler.open(lower, upper));
+ }
}
/** Consumer of {@link Range} values.
*
- * @param <C> Value type
+ * @param <V> Value type
*
* @see Handler */
- public interface Consumer<C extends Comparable<C>> {
+ public interface Consumer<@NonNull V> {
void all();
- void atLeast(C lower);
- void atMost(C upper);
- void greaterThan(C lower);
- void lessThan(C upper);
- void singleton(C value);
- void closed(C lower, C upper);
- void closedOpen(C lower, C upper);
- void openClosed(C lower, C upper);
- void open(C lower, C upper);
+ void atLeast(V lower);
+ void atMost(V upper);
+ void greaterThan(V lower);
+ void lessThan(V upper);
+ void singleton(V value);
+ void closed(V lower, V upper);
+ void closedOpen(V lower, V upper);
+ void openClosed(V lower, V upper);
+ void open(V lower, V upper);
}
/** Handler that converts a Range into another Range of the same type,
diff --git a/core/src/main/java/org/apache/calcite/util/TimeString.java b/core/src/main/java/org/apache/calcite/util/TimeString.java
index e0d46b837a..249c955030 100644
--- a/core/src/main/java/org/apache/calcite/util/TimeString.java
+++ b/core/src/main/java/org/apache/calcite/util/TimeString.java
@@ -18,6 +18,8 @@ package org.apache.calcite.util;
import org.apache.calcite.avatica.util.DateTimeUtils;
+import com.fasterxml.jackson.annotation.JsonCreator;
+import com.fasterxml.jackson.annotation.JsonProperty;
import com.google.common.base.Preconditions;
import com.google.common.base.Strings;
@@ -146,7 +148,8 @@ public class TimeString implements Comparable<TimeString> {
.withMillis(calendar.get(Calendar.MILLISECOND));
}
- public static TimeString fromMillisOfDay(int i) {
+ @JsonCreator
+ public static TimeString fromMillisOfDay(@JsonProperty("millisOfDay") int i) {
return new TimeString(DateTimeUtils.unixTimeToString(i))
.withMillis((int) floorMod(i, 1000L));
}
@@ -163,7 +166,6 @@ public class TimeString implements Comparable<TimeString> {
}
return new TimeString(v);
}
-
public int getMillisOfDay() {
int h = Integer.valueOf(v.substring(0, 2));
int m = Integer.valueOf(v.substring(3, 5));
diff --git a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
index 6c5275ec3f..c493d51ed0 100644
--- a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
@@ -47,6 +47,7 @@ import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCorrelVariable;
import org.apache.calcite.rex.RexFieldCollation;
import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexProgramBuilder;
import org.apache.calcite.rex.RexWindowBounds;
@@ -65,10 +66,13 @@ import org.apache.calcite.test.schemata.hr.HrSchema;
import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.util.DateString;
import org.apache.calcite.util.Holder;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.JsonBuilder;
+import org.apache.calcite.util.NlsString;
import org.apache.calcite.util.TestUtil;
+import org.apache.calcite.util.TimeString;
import org.apache.calcite.util.TimestampString;
import com.fasterxml.jackson.core.JsonProcessingException;
@@ -93,6 +97,7 @@ import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
+import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Stream;
@@ -826,6 +831,94 @@ class RelWriterTest {
.assertThatPlan(isLinux(expected));
}
+ @Test void testSearchOperator() {
+ final FrameworkConfig config = RelBuilderTest.config().build();
+ final RelBuilder b = RelBuilder.create(config);
+ final RexBuilder rexBuilder = b.getRexBuilder();
+
+ // Test toJson -> toRex -> toJson is the same.
+ final JsonBuilder jsonBuilder = new JsonBuilder();
+ final RelJson relJson = RelJson.create().withJsonBuilder(jsonBuilder);
+ final Consumer<RexNode> consumer = node -> {
+ Object jsonRepresentation = relJson.toJson(node);
+ assertThat(jsonRepresentation, notNullValue());
+
+ RexNode deserialized = relJson.toRex(b.getCluster(), jsonRepresentation);
+ assertThat(node, is(deserialized));
+ assertThat(jsonRepresentation, is(relJson.toJson(deserialized)));
+
+ // Test that toRex is the same as toJsonString -> readRex
+ final String s = jsonBuilder.toJsonString(jsonRepresentation);
+ RexNode deserialized2;
+ try {
+ deserialized2 = RelJsonReader.readRex(b.getCluster(), s);
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ assertThat(deserialized2, is(deserialized));
+ };
+
+ // Commented out but we should also get this passing! SEARCH in a RelNode
+ // using the JSON writer also leads to failures.
+ if (false) {
+ final RelNode rel = b
+ .scan("EMP")
+ .project(b.between(b.field("DEPTNO"), b.literal(20), b.literal(30)))
+ .build();
+ final RelJsonWriter jsonWriter =
+ new RelJsonWriter(new JsonBuilder(), RelJson::withLibraryOperatorTable);
+ rel.explain(jsonWriter);
+ String relJsonString = jsonWriter.asString();
+ String result = deserializeAndDumpToTextFormat(getSchema(rel), relJsonString);
+ final String expected = "<TODO>";
+ assertThat(result, isLinux(expected));
+ }
+
+ RexNode between =
+ rexBuilder.makeBetween(b.literal(45),
+ b.literal(20),
+ b.literal(30));
+ consumer.accept(between);
+
+ RexNode inNode =
+ rexBuilder.makeIn(b.literal(12),
+ ImmutableList.of(
+ b.literal(20),
+ b.literal(14)));
+ consumer.accept(inNode);
+
+ // Test Calcite DateString class works in a Range
+ final DateString d1 =
+ DateString.fromCalendarFields(
+ new TimestampString(1970, 2, 1, 1, 1, 0).toCalendar());
+ final DateString d2 = DateString.fromDaysSinceEpoch(100);
+ final DateString d3 = DateString.fromDaysSinceEpoch(1000);
+ RexNode dateNode =
+ rexBuilder.makeBetween(rexBuilder.makeDateLiteral(d2),
+ rexBuilder.makeDateLiteral(d1),
+ rexBuilder.makeDateLiteral(d3));
+ consumer.accept(dateNode);
+
+ // Test Calcite TimeString
+ final RexLiteral t1 = rexBuilder.makeTimeLiteral(new TimeString(1, 0, 0), 0);
+ final RexLiteral t2 = rexBuilder.makeTimeLiteral(new TimeString(2, 2, 2), 6);
+ final RexLiteral t3 = rexBuilder.makeTimeLiteral(new TimeString(3, 3, 3), 9);
+
+ RexNode timeNode = rexBuilder.makeBetween(t2, t1, t3);
+ consumer.accept(timeNode);
+
+ // Test Calcite NlsString
+ final NlsString nls1 = new NlsString("one", null, null);
+ final NlsString nls2 = new NlsString("ten", null, null);
+ final NlsString nls3 = new NlsString("sixteen", null, null);
+ RexNode nlsNode =
+ rexBuilder.makeIn(
+ rexBuilder.makeCharLiteral(nls2),
+ ImmutableList.of(rexBuilder.makeCharLiteral(nls1),
+ rexBuilder.makeCharLiteral(nls3)));
+ consumer.accept(nlsNode);
+ }
+
@ParameterizedTest
@MethodSource("explainFormats")
void testAggregateWithAlias(SqlExplainFormat format) {
@@ -872,7 +965,7 @@ class RelWriterTest {
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-4804">[CALCITE-4804]
- * Support Snapshot operator serialization and deserizalization</a>. */
+ * Support Snapshot operator serialization and deserialization</a>. */
@Test void testSnapshot() {
// Equivalent SQL:
// SELECT *
diff --git a/core/src/test/java/org/apache/calcite/util/RangeSetTest.java b/core/src/test/java/org/apache/calcite/util/RangeSetTest.java
index 904979ae38..5de76de31e 100644
--- a/core/src/test/java/org/apache/calcite/util/RangeSetTest.java
+++ b/core/src/test/java/org/apache/calcite/util/RangeSetTest.java
@@ -17,6 +17,7 @@
package org.apache.calcite.util;
import org.apache.calcite.linq4j.Ord;
+import org.apache.calcite.rel.externalize.RelJson;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableRangeSet;
@@ -27,6 +28,7 @@ import com.google.common.collect.TreeRangeSet;
import org.junit.jupiter.api.Test;
+import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@@ -44,6 +46,61 @@ import static org.hamcrest.MatcherAssert.assertThat;
*/
@SuppressWarnings("UnstableApiUsage")
class RangeSetTest {
+
+ /** Tests {@link org.apache.calcite.rel.externalize.RelJson#toJson(Range)}
+ * and {@link RelJson#rangeFromJson(List)}. */
+ @Test void testRangeSetSerializeDeserialize() {
+ RelJson relJson = RelJson.create();
+ final Range<BigDecimal> point = Range.singleton(BigDecimal.valueOf(0));
+ final Range<BigDecimal> closedRange1 =
+ Range.closed(BigDecimal.valueOf(0), BigDecimal.valueOf(5));
+ final Range<BigDecimal> closedRange2 =
+ Range.closed(BigDecimal.valueOf(6), BigDecimal.valueOf(10));
+
+ final Range<BigDecimal> gt1 = Range.greaterThan(BigDecimal.valueOf(7));
+ final Range<BigDecimal> al1 = Range.atLeast(BigDecimal.valueOf(8));
+ final Range<BigDecimal> lt1 = Range.lessThan(BigDecimal.valueOf(4));
+ final Range<BigDecimal> am1 = Range.atMost(BigDecimal.valueOf(3));
+
+ // Test serialize/deserialize Range
+ // Point
+ assertThat(RelJson.rangeFromJson(relJson.toJson(point)), is(point));
+ // Closed Range
+ assertThat(RelJson.rangeFromJson(relJson.toJson(closedRange1)),
+ is(closedRange1));
+ // Open Range
+ assertThat(RelJson.rangeFromJson(relJson.toJson(gt1)), is(gt1));
+ assertThat(RelJson.rangeFromJson(relJson.toJson(al1)), is(al1));
+ assertThat(RelJson.rangeFromJson(relJson.toJson(lt1)), is(lt1));
+ assertThat(RelJson.rangeFromJson(relJson.toJson(am1)), is(am1));
+ // Test closed single RangeSet
+ final RangeSet<BigDecimal> closedRangeSet = ImmutableRangeSet.of(closedRange1);
+ assertThat(RelJson.rangeSetFromJson(relJson.toJson(closedRangeSet)),
+ is(closedRangeSet));
+ // Test complex RangeSets
+ final RangeSet<BigDecimal> complexClosedRangeSet1 =
+ ImmutableRangeSet.<BigDecimal>builder()
+ .add(closedRange1)
+ .add(closedRange2)
+ .build();
+ assertThat(
+ RelJson.rangeSetFromJson(relJson.toJson(complexClosedRangeSet1)),
+ is(complexClosedRangeSet1));
+ final RangeSet<BigDecimal> complexClosedRangeSet2 =
+ ImmutableRangeSet.<BigDecimal>builder()
+ .add(gt1)
+ .add(am1)
+ .build();
+ assertThat(RelJson.rangeSetFromJson(relJson.toJson(complexClosedRangeSet2)),
+ is(complexClosedRangeSet2));
+
+ // Test None and All
+ final RangeSet<BigDecimal> setNone = ImmutableRangeSet.of();
+ final RangeSet<BigDecimal> setAll = setNone.complement();
+ assertThat(RelJson.rangeSetFromJson(relJson.toJson(setNone)), is(setNone));
+ assertThat(RelJson.rangeSetFromJson(relJson.toJson(setAll)), is(setAll));
+ }
+
/** Tests {@link RangeSets#minus(RangeSet, Range)}. */
@SuppressWarnings("UnstableApiUsage")
@Test void testRangeSetMinus() {
[calcite] 01/03: [CALCITE-5543] Add functions PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP (enabled in BigQuery library)
Posted by jh...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit bbc2cec5e8a70a09806fc499f750e9d9fa081a9e
Author: TJ Banghart <66...@users.noreply.github.com>
AuthorDate: Fri Mar 24 14:49:45 2023 -0700
[CALCITE-5543] Add functions PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP (enabled in BigQuery library)
Because BigQuery has different names for types, in Calcite's
type system these functions parse values of type DATE,
TIMESTAMP, TIME, and TIMESTAMP WITH LOCAL TIME ZONE,
respectively.
Close apache/calcite#3132
---
babel/src/test/resources/sql/big-query.iq | 163 ++++++++++-------
.../calcite/adapter/enumerable/RexImpTable.java | 16 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 90 ++++++++--
.../calcite/sql/fun/SqlLibraryOperators.java | 43 +++++
.../org/apache/calcite/sql/type/ReturnTypes.java | 7 +
.../apache/calcite/util/format/FormatElement.java | 18 +-
.../calcite/util/format/FormatElementEnum.java | 197 ++++++++++++---------
.../apache/calcite/util/format/FormatModels.java | 28 ++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 32 ++++
.../calcite/util/format/FormatElementEnumTest.java | 5 +-
site/_docs/reference.md | 4 +
.../org/apache/calcite/test/SqlOperatorTest.java | 78 ++++++++
12 files changed, 503 insertions(+), 178 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index 0df376e76a..941c30657b 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -2957,29 +2957,37 @@ SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00" /* TODO should
# in format_string must match the location of each element in
# date_string.
-!if (false) {
# This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008");
++------------+
+| EXPR$0 |
++------------+
+| 2008-12-25 |
++------------+
+(1 row)
+
!ok
-!}
-!if (false) {
# This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008");
+Invalid format: '%Y %A %b %e' for datetime string: 'Thursday Dec 25 2008'
!error
-!}
-!if (false) {
# This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008");
+Invalid format: '%A %b %e' for datetime string: 'Thursday Dec 25 2008'
!error
-!}
-!if (false) {
# This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30");
++------------+
+| EXPR$0 |
++------------+
+| 2000-12-30 |
++------------+
+(1 row)
+
!ok
-!}
# When using PARSE_DATE, keep the following in mind:
#
@@ -3000,27 +3008,27 @@ SELECT PARSE_DATE("%F", "2000-12-30");
# This example converts a MM/DD/YY formatted string to a DATE object:
-!if (false) {
SELECT PARSE_DATE("%x", "12/25/08") AS parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
+(1 row)
+
!ok
-!}
# This example converts a YYYYMMDD formatted string to a DATE object:
-!if (false) {
SELECT PARSE_DATE("%Y%m%d", "20081225") AS parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
+(1 row)
+
!ok
-!}
#####################################################################
# PARSE_DATETIME
@@ -3036,28 +3044,38 @@ SELECT PARSE_DATE("%Y%m%d", "20081225") AS parsed;
# datetime_string.
# This works because elements on both sides match.
-!if (false) {
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
+
# This doesn't work because the year element is in different locations.
-!if (false) {
SELECT PARSE_DATETIME("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %Y %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
# This doesn't work because one of the year elements is missing.
-!if (false) {
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
+
# This works because %c can find all matching elements in datetime_string.
-!if (false) {
SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
# The format string fully supports most format elements, except for %P.
#
@@ -3084,39 +3102,39 @@ SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008");
# The following examples parse a STRING literal as a DATETIME.
-!if (false) {
-SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;
+SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS dt;
+---------------------+
-| datetime |
+| dt |
+---------------------+
-| 1998-10-18T13:45:55 |
+| 1998-10-18 13:45:55 |
+---------------------+
+(1 row)
+
!ok
-!}
-!if (false) {
-SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm') AS datetime
+SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm') AS dt;
+---------------------+
-| datetime |
+| dt |
+---------------------+
-| 2018-08-30T14:23:38 |
+| 2018-08-30 14:23:38 |
+---------------------+
+(1 row)
+
!ok
-!}
# The following example parses a STRING literal containing a date in a
# natural language format as a DATETIME.
-!if (false) {
SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
- AS datetime;
+ AS dt;
+---------------------+
-| datetime |
+| dt |
+---------------------+
-| 2018-12-19T00:00:00 |
+| 2018-12-19 00:00:00 |
+---------------------+
+(1 row)
+
!ok
-!}
#####################################################################
# PARSE_TIME
@@ -3132,29 +3150,41 @@ SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
# time_string.
#
-!if (false) {
# This works because elements on both sides match.
SELECT PARSE_TIME("%I:%M:%S", "07:30:00");
++----------+
+| EXPR$0 |
++----------+
+| 07:30:00 |
++----------+
+(1 row)
+
!ok
-!}
+# TODO: validate for format element ordering
!if (false) {
# This doesn't work because the seconds element is in different locations.
SELECT PARSE_TIME("%S:%I:%M", "07:30:00");
!error
!}
-!if (false) {
# This doesn't work because one of the seconds elements is missing.
SELECT PARSE_TIME("%I:%M", "07:30:00");
+Invalid format: '%I:%M' for datetime string: '07:30:00'
!error
-!}
-!if (false) {
+
# This works because %T can find all matching elements in time_string.
SELECT PARSE_TIME("%T", "07:30:00");
++----------+
+| EXPR$0 |
++----------+
+| 07:30:00 |
++----------+
+(1 row)
+
!ok
-!}
+
# The format string fully supports most format elements except for %P.
#
@@ -3174,25 +3204,26 @@ SELECT PARSE_TIME("%T", "07:30:00");
#
# Returns TIME
-!if (false) {
SELECT PARSE_TIME("%H", "15") as parsed_time;
+-------------+
| parsed_time |
+-------------+
| 15:00:00 |
+-------------+
+(1 row)
+
!ok
-!}
-!if (false) {
SELECT PARSE_TIME('%I:%M:%S %p', '2:23:38 pm') AS parsed_time;
+-------------+
| parsed_time |
+-------------+
| 14:23:38 |
+-------------+
+(1 row)
+
!ok
-!}
+
#####################################################################
# PARSE_TIMESTAMP
@@ -3209,28 +3240,36 @@ SELECT PARSE_TIME('%I:%M:%S %p', '2:23:38 pm') AS parsed_time;
# timestamp_string.
# This works because elements on both sides match.
-!if (false) {
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
# This doesn't work because the year element is in different locations.
-!if (false) {
SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %Y %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
# This doesn't work because one of the year elements is missing.
-!if (false) {
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
# This works because %c can find all matching elements in timestamp_string.
-!if (false) {
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
# The format string fully supports most format elements, except for %P.
#
@@ -3256,15 +3295,19 @@ SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008");
# Display of results may differ, depending upon the environment and
# time zone where this query was executed.
-!if (false) {
+#
+# After [CALCITE-5446] is fixed, this and other TIMESTAMP WITH LOCAL TIME
+# values will end in UTC, like this: 2008-12-25 15:30:00 UTC.
+#
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;
-+-------------------------+
-| parsed |
-+-------------------------+
-| 2008-12-25 07:30:00 UTC |
-+-------------------------+
++---------------------+
+| parsed |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
#####################################################################
# STRING and BYTES functions ########################################
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 92b874ee11..9978ea2647 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
@@ -161,6 +161,10 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.MAX_BY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MD5;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MIN_BY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MONTHNAME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_DATE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_DATETIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_TIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_TIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.POW;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REPEAT;
@@ -589,16 +593,22 @@ public class RexImpTable {
// Datetime constructors
defineMethod(DATE, "date", NullPolicy.STRICT);
defineMethod(DATETIME, "datetime", NullPolicy.STRICT);
- defineMethod(TIMESTAMP, "timestamp", NullPolicy.STRICT);
defineMethod(TIME, "time", NullPolicy.STRICT);
+ defineMethod(TIMESTAMP, "timestamp", NullPolicy.STRICT);
+
+ // Datetime parsing methods
+ defineMethod(PARSE_DATE, "parseDate", NullPolicy.STRICT);
+ defineMethod(PARSE_DATETIME, "parseDatetime", NullPolicy.STRICT);
+ defineMethod(PARSE_TIME, "parseTime", NullPolicy.STRICT);
+ defineMethod(PARSE_TIMESTAMP, "parseTimestamp", NullPolicy.STRICT);
// Datetime formatting methods
defineMethod(TO_CHAR, "toChar", NullPolicy.STRICT);
final FormatDatetimeImplementor datetimeFormatImpl = new FormatDatetimeImplementor();
- map.put(FORMAT_TIMESTAMP, datetimeFormatImpl);
map.put(FORMAT_DATE, datetimeFormatImpl);
- map.put(FORMAT_TIME, datetimeFormatImpl);
map.put(FORMAT_DATETIME, datetimeFormatImpl);
+ map.put(FORMAT_TIME, datetimeFormatImpl);
+ map.put(FORMAT_TIMESTAMP, datetimeFormatImpl);
// Boolean operators
map.put(IS_NULL, new IsNullImplementor());
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 8f726822fd..42dccc92e0 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -66,7 +66,10 @@ import java.nio.charset.Charset;
import java.nio.charset.CharsetDecoder;
import java.sql.SQLException;
import java.sql.Timestamp;
+import java.text.DateFormat;
import java.text.DecimalFormat;
+import java.text.ParsePosition;
+import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
@@ -82,9 +85,11 @@ import java.time.temporal.ChronoField;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Base64;
+import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
+import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
@@ -96,7 +101,6 @@ import java.util.TimeZone;
import java.util.concurrent.atomic.AtomicLong;
import java.util.function.BinaryOperator;
import java.util.regex.Pattern;
-import java.util.stream.Collectors;
import static org.apache.calcite.linq4j.Nullness.castNonNull;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -2662,11 +2666,10 @@ public class SqlFunctions {
}
private static String internalFormatDatetime(String fmtString, java.util.Date date) {
- List<FormatElement> elements =
- FormatModels.BIG_QUERY.parse(fmtString);
- return elements.stream()
- .map(ele -> ele.format(date))
- .collect(Collectors.joining());
+ StringBuilder sb = new StringBuilder();
+ List<FormatElement> elements = FormatModels.BIG_QUERY.parse(fmtString);
+ elements.forEach(ele -> ele.format(sb, date));
+ return sb.toString();
}
public static String formatTimestamp(DataContext ctx, String fmtString, long timestamp) {
@@ -2675,11 +2678,9 @@ public class SqlFunctions {
public static String toChar(long timestamp, String pattern) {
List<FormatElement> elements = FormatModels.POSTGRESQL.parse(pattern);
-
- return elements.stream()
- .map(ele -> ele.format(internalToTimestamp(timestamp)))
- .collect(Collectors.joining())
- .trim();
+ StringBuilder sb = new StringBuilder();
+ elements.forEach(ele -> ele.format(sb, internalToTimestamp(timestamp)));
+ return sb.toString().trim();
}
public static String formatDate(DataContext ctx, String fmtString, int date) {
@@ -2690,6 +2691,73 @@ public class SqlFunctions {
return internalFormatDatetime(fmtString, internalToTime(time));
}
+ private static String parseDatetimePattern(String fmtString) {
+ StringBuilder sb = new StringBuilder();
+ List<FormatElement> elements = FormatModels.BIG_QUERY.parse(fmtString);
+ elements.forEach(ele -> ele.toPattern(sb));
+ return sb.toString();
+ }
+
+ private static long internalParseDatetime(String fmtString, String datetime) {
+ return internalParseDatetime(fmtString, datetime,
+ DateTimeUtils.DEFAULT_ZONE);
+ }
+
+ private static long internalParseDatetime(String fmt, String datetime,
+ TimeZone tz) {
+ final String javaFmt = parseDatetimePattern(fmt);
+ // TODO: make Locale configurable. ENGLISH set for weekday parsing (e.g.
+ // Thursday, Friday).
+ final DateFormat parser = new SimpleDateFormat(javaFmt, Locale.ENGLISH);
+ final ParsePosition pos = new ParsePosition(0);
+ parser.setLenient(false);
+ parser.setCalendar(Calendar.getInstance(tz, Locale.ROOT));
+ Date parsed = parser.parse(datetime, pos);
+ // Throw if either the parse was unsuccessful, or the format string did not
+ // contain enough elements to parse the datetime string completely.
+ if (pos.getErrorIndex() >= 0 || pos.getIndex() != datetime.length()) {
+ SQLException e =
+ new SQLException(
+ String.format(Locale.ROOT,
+ "Invalid format: '%s' for datetime string: '%s'.", fmt,
+ datetime));
+ throw Util.toUnchecked(e);
+ }
+ // Suppress the Errorprone warning "[JavaUtilDate] Date has a bad API that
+ // leads to bugs; prefer java.time.Instant or LocalDate" because we know
+ // what we're doing.
+ @SuppressWarnings("JavaUtilDate")
+ final long millisSinceEpoch = parsed.getTime();
+ return millisSinceEpoch;
+ }
+
+ public static int parseDate(String fmtString, String date) {
+ final long millisSinceEpoch = internalParseDatetime(fmtString, date);
+ return toInt(new java.sql.Date(millisSinceEpoch));
+ }
+
+ public static long parseDatetime(String fmtString, String datetime) {
+ final long millisSinceEpoch = internalParseDatetime(fmtString, datetime);
+ return toLong(new java.sql.Timestamp(millisSinceEpoch));
+ }
+
+ public static int parseTime(String fmtString, String time) {
+ final long millisSinceEpoch = internalParseDatetime(fmtString, time);
+ return toInt(new java.sql.Time(millisSinceEpoch));
+ }
+
+ public static long parseTimestamp(String fmtString, String timestamp) {
+ return parseTimestamp(fmtString, timestamp, "UTC");
+ }
+
+ public static long parseTimestamp(String fmtString, String timestamp,
+ String timeZone) {
+ TimeZone tz = TimeZone.getTimeZone(timeZone);
+ final long millisSinceEpoch =
+ internalParseDatetime(fmtString, timestamp, tz);
+ return toLong(new java.sql.Timestamp(millisSinceEpoch), tz);
+ }
+
/**
* Converts a SQL TIMESTAMP value from the internal representation type
* (number of milliseconds since January 1st, 1970) to the Java Type
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index ed3ebd5ff8..75a27f2c53 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -892,6 +892,49 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
+ /**
+ * The "PARSE_TIME(string, string)" function (BigQuery);
+ * converts a string representation of time to a TIME value.
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_TIME =
+ SqlBasicFunction.create("PARSE_TIME", ReturnTypes.TIME_NULLABLE,
+ OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE);
+
+ /**
+ * The "PARSE_DATE(string, string)" function (BigQuery); Converts a string representation of date
+ * to a DATE object.
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_DATE =
+ SqlBasicFunction.create("PARSE_DATE",
+ ReturnTypes.DATE_NULLABLE, OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE);
+
+ /**
+ * The "PARSE_TIMESTAMP(string, string [, timezone])" function (BigQuery); Formats a timestamp
+ * object according to the specified string.
+ *
+ * <p>In BigQuery, the "TIMESTAMP" datatype maps to Calcite's
+ * TIMESTAMP_WITH_LOCAL_TIME_ZONE
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_TIMESTAMP =
+ SqlBasicFunction.create("PARSE_TIMESTAMP",
+ ReturnTypes.TIMESTAMP_LTZ_NULLABLE, OperandTypes.STRING_STRING_OPTIONAL_STRING,
+ SqlFunctionCategory.TIMEDATE);
+
+ /**
+ * The "PARSE_DATETIME(string, string [, timezone])" function (BigQuery); Formats a timestamp
+ * object according to the specified string.
+ *
+ * <p>Note that the {@code TIMESTAMP} type of Calcite and Standard SQL
+ * is called {@code DATETIME} in BigQuery.
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_DATETIME =
+ SqlBasicFunction.create("PARSE_DATETIME", ReturnTypes.TIMESTAMP_NULLABLE,
+ OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE);
+
/** The "FORMAT_TIME(string, time)" function (BigQuery);
* Formats a time object according to the specified string. */
@LibraryOperator(libraries = {BIG_QUERY})
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index ab4aac1784..0b7385438f 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -335,6 +335,13 @@ public abstract class ReturnTypes {
public static final SqlReturnTypeInference TIMESTAMP_LTZ =
explicit(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE);
+ /**
+ * Type-inference strategy whereby the result type of a call is nullable
+ * TIMESTAMP WITH LOCAL TIME ZONE.
+ */
+ public static final SqlReturnTypeInference TIMESTAMP_LTZ_NULLABLE =
+ TIMESTAMP_LTZ.andThen(SqlTypeTransforms.TO_NULLABLE);
+
/**
* Type-inference strategy whereby the result type of a call is Double.
*/
diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatElement.java b/core/src/main/java/org/apache/calcite/util/format/FormatElement.java
index 6670e23423..ef5c230510 100644
--- a/core/src/main/java/org/apache/calcite/util/format/FormatElement.java
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatElement.java
@@ -28,16 +28,18 @@ public interface FormatElement {
/**
* Formats a date to its appropriate string representation for the element.
+ */
+ void format(StringBuilder sb, java.util.Date date);
+
+ /**
+ * Adds the appropriate {@link java.text.SimpleDateFormat} pattern for the element to
+ * provided StringBuilder.
*
- * <p>This API is subject to change. It might be more efficient if the
- * signature was one of the following:
- *
- * <pre>
- * void format(StringBuilder, java.util.Date)
- * void format(StringBuilder, long)
- * </pre>
+ * <p>Note that certain FormatElements may not have a Java equivalent.
+ * In such cases, calling this method will throw an {@link UnsupportedOperationException}.
+ * See {@link FormatElementEnum#Q} as an example.</p>
*/
- String format(java.util.Date date);
+ void toPattern(StringBuilder sb) throws UnsupportedOperationException;
/**
* Returns the description of an element.
diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java b/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
index 2b1ea1c007..8c6ac03607 100644
--- a/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
@@ -28,6 +28,8 @@ import java.util.Locale;
import static org.apache.calcite.linq4j.Nullness.castNonNull;
+import static java.util.Objects.requireNonNull;
+
/**
* Implementation of {@link FormatElement} containing the standard format
* elements. These are based on Oracle's format model documentation.
@@ -39,188 +41,207 @@ import static org.apache.calcite.linq4j.Nullness.castNonNull;
* @see FormatModels#DEFAULT
*/
public enum FormatElementEnum implements FormatElement {
- D("The weekday (Monday as the first day of the week) as a decimal number (1-7)") {
- @Override public String format(Date date) {
+ D("F", "The weekday (Monday as the first day of the week) as a decimal number (1-7)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%d", calendar.get(Calendar.DAY_OF_WEEK));
+ sb.append(String.format(Locale.ROOT, "%d", calendar.get(Calendar.DAY_OF_WEEK)));
}
},
- DAY("The full weekday name") {
- @Override public String format(Date date) {
+ DAY("EEEE", "The full weekday name") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.eeeeFormat.format(date);
+ sb.append(work.eeeeFormat.format(date));
}
},
- DD("The day of the month as a decimal number (01-31)") {
- @Override public String format(Date date) {
+ DD("dd", "The day of the month as a decimal number (01-31)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.DAY_OF_MONTH));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.DAY_OF_MONTH)));
}
},
- DDD("The day of the year as a decimal number (001-366)") {
- @Override public String format(Date date) {
+ DDD("D", "The day of the year as a decimal number (001-366)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.DAY_OF_YEAR));
+ sb.append(String.format(Locale.ROOT, "%03d", calendar.get(Calendar.DAY_OF_YEAR)));
}
},
- DY("The abbreviated weekday name") {
- @Override public String format(Date date) {
+ DY("EEE", "The abbreviated weekday name") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.eeeFormat.format(date);
+ sb.append(work.eeeFormat.format(date));
}
},
- FF1("Fractional seconds to 1 digit") {
- @Override public String format(Date date) {
+ FF1("S", "Fractional seconds to 1 digit") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.sFormat.format(date);
+ sb.append(work.sFormat.format(date));
}
},
- FF2("Fractional seconds to 2 digits") {
- @Override public String format(Date date) {
+ FF2("SS", "Fractional seconds to 2 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.ssFormat.format(date);
+ sb.append(work.ssFormat.format(date));
}
},
- FF3("Fractional seconds to 3 digits") {
- @Override public String format(Date date) {
+ FF3("SSS", "Fractional seconds to 3 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.sssFormat.format(date);
+ sb.append(work.sssFormat.format(date));
}
},
- FF4("Fractional seconds to 4 digits") {
- @Override public String format(Date date) {
+ FF4("SSSS", "Fractional seconds to 4 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.ssssFormat.format(date);
+ sb.append(work.ssssFormat.format(date));
}
},
- FF5("Fractional seconds to 5 digits") {
- @Override public String format(Date date) {
+ FF5("SSSSS", "Fractional seconds to 5 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.sssssFormat.format(date);
+ sb.append(work.sssssFormat.format(date));
}
},
- FF6("Fractional seconds to 6 digits") {
- @Override public String format(Date date) {
+ FF6("SSSSSS", "Fractional seconds to 6 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.ssssssFormat.format(date);
+ sb.append(work.ssssssFormat.format(date));
}
},
- HH12("The hour (12-hour clock) as a decimal number (01-12)") {
- @Override public String format(Date date) {
+ HH12("h", "The hour (12-hour clock) as a decimal number (01-12)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
int hour = calendar.get(Calendar.HOUR);
- return String.format(Locale.ROOT, "%02d", hour == 0 ? 12 : hour);
+ sb.append(String.format(Locale.ROOT, "%02d", hour == 0 ? 12 : hour));
}
},
- HH24("The hour (24-hour clock) as a decimal number (00-23)") {
- @Override public String format(Date date) {
+ HH24("H", "The hour (24-hour clock) as a decimal number (00-23)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.HOUR_OF_DAY));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.HOUR_OF_DAY)));
}
},
- IW("The ISO 8601 week number of the year (Monday as the first day of the week) "
+ // TODO: Ensure ISO 8601 for parsing
+ IW("w", "The ISO 8601 week number of the year (Monday as the first day of the week) "
+ "as a decimal number (01-53)") {
- @Override public String format(Date date) {
+ @Override public void format(StringBuilder sb, Date date) {
// TODO: ensure this is isoweek
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
calendar.setFirstDayOfWeek(Calendar.MONDAY);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
- MI("The minute as a decimal number (00-59)") {
- @Override public String format(Date date) {
+ MI("m", "The minute as a decimal number (00-59)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MINUTE));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MINUTE)));
}
},
- MM("The month as a decimal number (01-12)") {
- @Override public String format(Date date) {
+ MM("MM", "The month as a decimal number (01-12)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MONTH) + 1);
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MONTH) + 1));
}
},
- MON("The abbreviated month name") {
- @Override public String format(Date date) {
+ MON("MMM", "The abbreviated month name") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.mmmFormat.format(date);
+ sb.append(work.mmmFormat.format(date));
}
},
- MONTH("The full month name (English)") {
- @Override public String format(Date date) {
+ MONTH("MMMM", "The full month name (English)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.mmmmFormat.format(date);
+ sb.append(work.mmmmFormat.format(date));
+ }
+ },
+ // PM can represent both AM and PM
+ PM("a", "Meridian indicator without periods") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ String meridian = calendar.get(Calendar.HOUR_OF_DAY) < 12 ? "AM" : "PM";
+ sb.append(meridian);
}
},
- Q("The quarter as a decimal number (1-4)") {
- @Override public String format(Date date) {
+ Q("", "The quarter as a decimal number (1-4)") {
+ // TODO: Allow parsing of quarters.
+ @Override public void toPattern(StringBuilder sb) throws UnsupportedOperationException {
+ throw new UnsupportedOperationException("Cannot convert 'Q' FormatElement to Java pattern");
+ }
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%d", (calendar.get(Calendar.MONTH) / 3) + 1);
+ sb.append(String.format(Locale.ROOT, "%d", (calendar.get(Calendar.MONTH) / 3) + 1));
}
},
- SS("The second as a decimal number (00-60)") {
- @Override public String format(Date date) {
+ MS("SSS", "The millisecond as a decimal number (000-999)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND));
+ sb.append(String.format(Locale.ROOT, "%03d", calendar.get(Calendar.MILLISECOND)));
}
},
- MS("The millisecond as a decimal number (000-999)") {
- @Override public String format(Date date) {
+ SS("s", "The second as a decimal number (00-60)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.MILLISECOND));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND)));
}
},
- TZR("The time zone name") {
- @Override public String format(Date date) {
+ TZR("z", "The time zone name") {
+ @Override public void format(StringBuilder sb, Date date) {
// TODO: how to support timezones?
- return "";
}
},
- WW("The week number of the year (Sunday as the first day of the week) as a decimal "
+ WW("w", "The week number of the year (Sunday as the first day of the week) as a decimal "
+ "number (00-53)") {
- @Override public String format(Date date) {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
calendar.setFirstDayOfWeek(Calendar.SUNDAY);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
- YY("Last 2 digits of year") {
- @Override public String format(Date date) {
+ YY("yy", "Last 2 digits of year") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.yyFormat.format(date);
+ sb.append(work.yyFormat.format(date));
}
},
- YYYY("The year with century as a decimal number") {
- @Override public String format(Date date) {
+ YYYY("yyyy", "The year with century as a decimal number") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%d", calendar.get(Calendar.YEAR));
+ sb.append(String.format(Locale.ROOT, "%d", calendar.get(Calendar.YEAR)));
}
};
private final String description;
+ final String javaFmt;
// TODO: be sure to deal with TZ
- FormatElementEnum(String description) {
- this.description = description;
+ FormatElementEnum(String javaFmt, String description) {
+ this.javaFmt = requireNonNull(javaFmt, "javaFmt");
+ this.description = requireNonNull(description, "description");
}
@Override public String getDescription() {
return description;
}
+ @Override public void toPattern(StringBuilder sb) {
+ sb.append(this.javaFmt);
+ }
+
/** Work space. Provides a value for each mutable data structure that might
* be needed by a format element. Ensures thread-safety. */
static class Work {
@@ -236,16 +257,16 @@ public enum FormatElementEnum implements FormatElement {
Calendar.getInstance(DateTimeUtils.DEFAULT_ZONE, Locale.ROOT);
/** Uses Locale.US instead of Locale.ROOT to fix formatting in Java 11 */
- final DateFormat eeeeFormat = new SimpleDateFormat("EEEE", Locale.US);
- final DateFormat eeeFormat = new SimpleDateFormat("EEE", Locale.ROOT);
- final DateFormat mmmFormat = new SimpleDateFormat("MMM", Locale.ROOT);
- final DateFormat mmmmFormat = new SimpleDateFormat("MMMM", Locale.ROOT);
- final DateFormat sFormat = new SimpleDateFormat("S", Locale.ROOT);
- final DateFormat ssFormat = new SimpleDateFormat("SS", Locale.ROOT);
- final DateFormat sssFormat = new SimpleDateFormat("SSS", Locale.ROOT);
- final DateFormat ssssFormat = new SimpleDateFormat("SSSS", Locale.ROOT);
- final DateFormat sssssFormat = new SimpleDateFormat("SSSSS", Locale.ROOT);
- final DateFormat ssssssFormat = new SimpleDateFormat("SSSSSS", Locale.ROOT);
- final DateFormat yyFormat = new SimpleDateFormat("yy", Locale.ROOT);
+ final DateFormat eeeeFormat = new SimpleDateFormat(DAY.javaFmt, Locale.US);
+ final DateFormat eeeFormat = new SimpleDateFormat(DY.javaFmt, Locale.ROOT);
+ final DateFormat mmmFormat = new SimpleDateFormat(MON.javaFmt, Locale.ROOT);
+ final DateFormat mmmmFormat = new SimpleDateFormat(MONTH.javaFmt, Locale.ROOT);
+ final DateFormat sFormat = new SimpleDateFormat(FF1.javaFmt, Locale.ROOT);
+ final DateFormat ssFormat = new SimpleDateFormat(FF2.javaFmt, Locale.ROOT);
+ final DateFormat sssFormat = new SimpleDateFormat(FF3.javaFmt, Locale.ROOT);
+ final DateFormat ssssFormat = new SimpleDateFormat(FF4.javaFmt, Locale.ROOT);
+ final DateFormat sssssFormat = new SimpleDateFormat(FF5.javaFmt, Locale.ROOT);
+ final DateFormat ssssssFormat = new SimpleDateFormat(FF6.javaFmt, Locale.ROOT);
+ final DateFormat yyFormat = new SimpleDateFormat(YY.javaFmt, Locale.ROOT);
}
}
diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatModels.java b/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
index a8e5b4b9f2..545e315b58 100644
--- a/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
@@ -47,6 +47,7 @@ import static org.apache.calcite.util.format.FormatElementEnum.MM;
import static org.apache.calcite.util.format.FormatElementEnum.MON;
import static org.apache.calcite.util.format.FormatElementEnum.MONTH;
import static org.apache.calcite.util.format.FormatElementEnum.MS;
+import static org.apache.calcite.util.format.FormatElementEnum.PM;
import static org.apache.calcite.util.format.FormatElementEnum.Q;
import static org.apache.calcite.util.format.FormatElementEnum.SS;
import static org.apache.calcite.util.format.FormatElementEnum.TZR;
@@ -111,15 +112,24 @@ public class FormatModels {
map.put("%E4S", FF4);
map.put("%E5S", FF5);
map.put("%E*S", FF6);
+ map.put("%e", DD);
+ map.put("%F",
+ compositeElement("The date in the format %Y-%m-%d.", YYYY, literalElement("-"), MM,
+ literalElement("-"), DD));
map.put("%H", HH24);
+ map.put("%I", HH12);
map.put("%j", DDD);
map.put("%M", MI);
map.put("%m", MM);
+ map.put("%p", PM);
map.put("%Q", Q);
map.put("%R",
compositeElement("The time in the format %H:%M",
HH24, literalElement(":"), MI));
map.put("%S", SS);
+ map.put("%T",
+ compositeElement("The time in the format %H:%M:%S.",
+ HH24, literalElement(":"), MI, literalElement(":"), SS));
map.put("%u", D);
map.put("%V", IW);
map.put("%W", WW);
@@ -267,8 +277,12 @@ public class FormatModels {
this.literal = requireNonNull(literal, "literal");
}
- @Override public String format(Date date) {
- return literal;
+ @Override public void format(StringBuilder sb, Date date) {
+ sb.append(literal);
+ }
+
+ @Override public void toPattern(StringBuilder sb) {
+ sb.append(literal);
}
@Override public String getDescription() {
@@ -293,10 +307,12 @@ public class FormatModels {
this.description = requireNonNull(description, "description");
}
- @Override public String format(Date date) {
- StringBuilder buf = new StringBuilder();
- flatten(ele -> buf.append(ele.format(date)));
- return buf.toString();
+ @Override public void format(StringBuilder sb, Date date) {
+ flatten(ele -> ele.format(sb, date));
+ }
+
+ @Override public void toPattern(StringBuilder sb) {
+ flatten(ele -> ele.toPattern(sb));
}
/**
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 8cae0d8deb..4d146ec5d2 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -1894,6 +1894,38 @@ class RelToSqlConverterTest {
sql(query).withBigQuery().ok(expected);
}
+ @Test void testBigQueryParseDatetimeFunctions() {
+ String parseTime = "select parse_time('%I:%M:%S', '07:30:00')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedTimestampTrunc =
+ "SELECT PARSE_TIME('%I:%M:%S', '07:30:00')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseTime).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedTimestampTrunc);
+
+ String parseDate = "select parse_date('%A %b %e %Y', 'Thursday Dec 25 2008')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedParseDate =
+ "SELECT PARSE_DATE('%A %b %e %Y', 'Thursday Dec 25 2008')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseDate).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedParseDate);
+
+ String parseTimestamp =
+ "select parse_timestamp('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedParseTimestamp =
+ "SELECT PARSE_TIMESTAMP('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseTimestamp).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedParseTimestamp);
+
+ String parseDatetime =
+ "select parse_datetime('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedParseDatetime =
+ "SELECT PARSE_DATETIME('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseDatetime).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedParseDatetime);
+ }
+
@Test void testBigQueryTimeTruncFunctions() {
String timestampTrunc = "select timestamp_trunc(timestamp '2012-02-03 15:30:00', month)\n"
+ "from \"foodmart\".\"product\"\n";
diff --git a/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java b/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
index 59a268f3a8..6a69bb3aad 100644
--- a/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
+++ b/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
@@ -28,8 +28,9 @@ import static org.junit.jupiter.api.Assertions.assertEquals;
*/
class FormatElementEnumTest {
@Test void testDay() {
- assertEquals(
- FormatElementEnum.DAY.format(Date.from(Instant.parse("2014-09-30T10:00:00Z"))),
+ StringBuilder ts = new StringBuilder();
+ FormatElementEnum.DAY.format(ts, Date.from(Instant.parse("2014-09-30T10:00:00Z")));
+ assertEquals(ts.toString(),
"Tuesday");
}
}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index d90178f42c..2dbb03e1f1 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2716,6 +2716,10 @@ BigQuery's type system uses confusingly different names for types and functions:
| b m p | MD5(string) | Calculates an MD5 128-bit checksum of *string* and returns it as a hex string
| m | MONTHNAME(date) | Returns the name, in the connection's locale, of the month in *datetime*; for example, it returns '二月' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10'
| o | NVL(value1, value2) | Returns *value1* if *value1* is not null, otherwise *value2*
+| b | PARSE_DATE(format, string) | Uses format specified by *format* to convert *string* representation of date to a DATE value
+| b | PARSE_DATETIME(format, string) | Uses format specified by *format* to convert *string* representation of datetime to a TIMESTAMP value
+| b | PARSE_TIME(format, string) | Uses format specified by *format* to convert *string* representation of time to a TIME value
+| b | PARSE_TIMESTAMP(format, string[, timeZone]) | Uses format specified by *format* to convert *string* representation of timestamp to a TIMESTAMP WITH LOCAL TIME ZONE value in *timeZone*
| b | POW(numeric1, numeric2) | Returns *numeric1* raised to the power *numeric2*
| m o | REGEXP_REPLACE(string, regexp, rep, [, pos [, occurrence [, matchType]]]) | Replaces all substrings of *string* that match *regexp* with *rep* at the starting *pos* in expr (if omitted, the default is 1), *occurrence* means which occurrence of a match to search for (if omitted, the default is 1), *matchType* specifies how to perform matching
| b m p | REPEAT(string, integer) | Returns a string consisting of *string* repeated of *integer* times; returns an empty string if *integer* is less than 1
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 43b322a19f..96285ca7c7 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -9127,6 +9127,84 @@ public class SqlOperatorTest {
"VARCHAR(2000) NOT NULL");
}
+ @Test void testParseDate() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_DATE);
+ f.checkScalar("PARSE_DATE('%A %b %e %Y', 'Thursday Dec 25 2008')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%x', '12/25/08')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%F', '2000-12-30')",
+ "2000-12-30",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%x', '12/25/08')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%Y%m%d', '20081225')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%F', '2022-06-01')",
+ "2022-06-01",
+ "DATE NOT NULL");
+ }
+
+ @Test void testParseDatetime() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_DATETIME);
+ f.checkScalar("PARSE_DATETIME('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%c', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55')",
+ "1998-10-18 13:45:55",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm')",
+ "2018-08-30 14:23:38",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%A, %B %e, %Y', 'Wednesday, December 19, 2018')",
+ "2018-12-19 00:00:00",
+ "TIMESTAMP(0) NOT NULL");
+ }
+
+ @Test void testParseTime() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_TIME);
+ f.checkScalar("PARSE_TIME('%I:%M:%S', '07:30:00')",
+ "07:30:00",
+ "TIME(0) NOT NULL");
+ f.checkScalar("PARSE_TIME('%T', '07:30:00')",
+ "07:30:00",
+ "TIME(0) NOT NULL");
+ f.checkScalar("PARSE_TIME('%H', '15')",
+ "15:00:00",
+ "TIME(0) NOT NULL");
+ f.checkScalar("PARSE_TIME('%I:%M:%S %p', '2:23:38 pm')",
+ "14:23:38",
+ "TIME(0) NOT NULL");
+ }
+
+ @Test void testParseTimestamp() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_TIMESTAMP);
+ f.checkScalar("PARSE_TIMESTAMP('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL");
+ f.checkScalar("PARSE_TIMESTAMP('%c', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL");
+ f.checkScalar("PARSE_TIMESTAMP('%c', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL");
+ }
+
@Test void testDenseRankFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.DENSE_RANK, VM_FENNEL, VM_JAVA);
[calcite] 02/03: [CALCITE-4698] Result type of datetime '+' operators and TIMESTAMPADD function should be the same as the operand type
Posted by jh...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 07a29b607199dfb9af46380faecd7747f8526272
Author: Sergey Nuyanzin <sn...@gmail.com>
AuthorDate: Sat Mar 11 22:26:44 2023 +0100
[CALCITE-4698] Result type of datetime '+' operators and TIMESTAMPADD function should be the same as the operand type
Close apache/calcite#3104
---
.../calcite/rel/type/RelDataTypeFactoryImpl.java | 27 +++++++-
.../calcite/sql/fun/SqlTimestampAddFunction.java | 47 +++++++-------
.../calcite/sql/type/SqlTypeFactoryImpl.java | 9 +--
.../apache/calcite/sql/test/ResultCheckers.java | 15 +++++
.../org/apache/calcite/test/SqlOperatorTest.java | 74 ++++++++++++++++++++--
5 files changed, 138 insertions(+), 34 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactoryImpl.java b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactoryImpl.java
index 3bf1a902e8..d73339c50e 100644
--- a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactoryImpl.java
@@ -18,6 +18,7 @@ package org.apache.calcite.rel.type;
import org.apache.calcite.linq4j.tree.Primitive;
import org.apache.calcite.sql.SqlCollation;
+import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.type.ArraySqlType;
import org.apache.calcite.sql.type.JavaToSqlTypeConversionRules;
import org.apache.calcite.sql.type.MapSqlType;
@@ -48,6 +49,8 @@ import java.util.List;
import java.util.Map;
import java.util.Objects;
+import static java.util.Objects.requireNonNull;
+
/**
* Abstract base for implementations of {@link RelDataTypeFactory}.
*/
@@ -109,7 +112,7 @@ public abstract class RelDataTypeFactoryImpl implements RelDataTypeFactory {
/** Creates a type factory. */
protected RelDataTypeFactoryImpl(RelDataTypeSystem typeSystem) {
- this.typeSystem = Objects.requireNonNull(typeSystem, "typeSystem");
+ this.typeSystem = requireNonNull(typeSystem, "typeSystem");
}
//~ Methods ----------------------------------------------------------------
@@ -313,6 +316,26 @@ public abstract class RelDataTypeFactoryImpl implements RelDataTypeFactory {
return new MapSqlType(keyType, valueType, isNullable);
}
+ protected RelDataType leastRestrictiveIntervalDatetimeType(
+ final RelDataType dateTimeType, final RelDataType type1) {
+ assert SqlTypeUtil.isDatetime(dateTimeType);
+ if (SqlTypeUtil.isIntType(type1)) {
+ return dateTimeType;
+ }
+ final SqlIntervalQualifier intervalQualifier = type1.getIntervalQualifier();
+ requireNonNull(intervalQualifier, "intervalQualifier");
+ if (!dateTimeType.getSqlTypeName().allowsPrec()
+ || intervalQualifier.useDefaultFractionalSecondPrecision()
+ || intervalQualifier.getFractionalSecondPrecision(typeSystem)
+ <= dateTimeType.getPrecision()) {
+ return dateTimeType;
+ } else {
+ return
+ createSqlType(dateTimeType.getSqlTypeName(),
+ intervalQualifier.getFractionalSecondPrecision(typeSystem));
+ }
+ }
+
// copy a non-record type, setting nullability
private RelDataType copySimpleType(
RelDataType type,
@@ -374,7 +397,7 @@ public abstract class RelDataTypeFactoryImpl implements RelDataTypeFactory {
@Override public RelDataType createTypeWithNullability(
final RelDataType type,
final boolean nullable) {
- Objects.requireNonNull(type, "type");
+ requireNonNull(type, "type");
RelDataType newType;
if (type.isNullable() == nullable) {
newType = type;
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java
index 7cef98c097..08bf7ce0f5 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java
@@ -32,8 +32,12 @@ import org.apache.calcite.sql.type.SqlTypeTransforms;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
+import com.google.common.collect.ImmutableMap;
+
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.util.Map;
+
import static org.apache.calcite.util.Util.first;
/**
@@ -64,8 +68,10 @@ import static org.apache.calcite.util.Util.first;
*/
public class SqlTimestampAddFunction extends SqlFunction {
- private static final int MILLISECOND_PRECISION = 3;
- private static final int MICROSECOND_PRECISION = 6;
+ private static final Map<TimeUnit, Integer> FRAC_SECOND_PRECISION_MAP =
+ ImmutableMap.of(TimeUnit.MILLISECOND, 3,
+ TimeUnit.MICROSECOND, 6,
+ TimeUnit.NANOSECOND, 9);
private static final SqlReturnTypeInference RETURN_TYPE_INFERENCE =
opBinding ->
@@ -84,35 +90,28 @@ public class SqlTimestampAddFunction extends SqlFunction {
static RelDataType deduceType(RelDataTypeFactory typeFactory,
@Nullable TimeUnit timeUnit, RelDataType datetimeType) {
- TimeUnit timeUnit2 = first(timeUnit, TimeUnit.EPOCH);
+ final TimeUnit timeUnit2 = first(timeUnit, TimeUnit.EPOCH);
+ SqlTypeName typeName = datetimeType.getSqlTypeName();
switch (timeUnit2) {
- case MILLISECOND:
- return typeFactory.createSqlType(SqlTypeName.TIMESTAMP,
- MILLISECOND_PRECISION);
-
case MICROSECOND:
- return typeFactory.createSqlType(SqlTypeName.TIMESTAMP,
- MICROSECOND_PRECISION);
-
+ case MILLISECOND:
+ case NANOSECOND:
+ return typeFactory.createSqlType(typeName,
+ Math.max(FRAC_SECOND_PRECISION_MAP.getOrDefault(timeUnit2, 0),
+ datetimeType.getPrecision()));
case HOUR:
case MINUTE:
case SECOND:
- SqlTypeName typeName = datetimeType.getSqlTypeName();
- switch (typeName) {
- case TIME:
- case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
- break;
- default:
- // If it is not a TIMESTAMP_WITH_LOCAL_TIME_ZONE, operations involving
- // HOUR, MINUTE, SECOND with DATE or TIMESTAMP types will result in
- // TIMESTAMP type.
- typeName = SqlTypeName.TIMESTAMP;
- break;
+ if (datetimeType.getFamily() == SqlTypeFamily.TIME) {
+ return datetimeType;
+ } else if (datetimeType.getFamily() == SqlTypeFamily.TIMESTAMP) {
+ return
+ typeFactory.createSqlType(typeName,
+ datetimeType.getPrecision());
+ } else {
+ return typeFactory.createSqlType(SqlTypeName.TIMESTAMP);
}
- return typeFactory.createSqlType(typeName);
-
default:
- case EPOCH:
return datetimeType;
}
}
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
index ff08400ff8..b414e6787e 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
@@ -473,7 +473,7 @@ public class SqlTypeFactoryImpl extends RelDataTypeFactoryImpl {
if (types.size() > (i + 1)) {
RelDataType type1 = types.get(i + 1);
if (SqlTypeUtil.isDatetime(type1)) {
- resultType = type1;
+ resultType = leastRestrictiveIntervalDatetimeType(type1, type);
return createTypeWithNullability(resultType,
nullCount > 0 || nullableCount > 0);
}
@@ -497,9 +497,10 @@ public class SqlTypeFactoryImpl extends RelDataTypeFactoryImpl {
// datetime +/- interval (or integer) = datetime
if (types.size() > (i + 1)) {
RelDataType type1 = types.get(i + 1);
- if (SqlTypeUtil.isInterval(type1)
- || SqlTypeUtil.isIntType(type1)) {
- resultType = type;
+ final boolean isInterval1 = SqlTypeUtil.isInterval(type1);
+ final boolean isInt1 = SqlTypeUtil.isIntType(type1);
+ if (isInterval1 || isInt1) {
+ resultType = leastRestrictiveIntervalDatetimeType(type, type1);
return createTypeWithNullability(resultType,
nullCount > 0 || nullableCount > 0);
}
diff --git a/testkit/src/main/java/org/apache/calcite/sql/test/ResultCheckers.java b/testkit/src/main/java/org/apache/calcite/sql/test/ResultCheckers.java
index 8960a9b376..69fad877f1 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/test/ResultCheckers.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/test/ResultCheckers.java
@@ -28,6 +28,9 @@ import org.hamcrest.Matcher;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.Types;
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.time.ZoneOffset;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
@@ -57,6 +60,18 @@ public class ResultCheckers {
JdbcType.BIG_DECIMAL);
}
+ public static SqlTester.ResultChecker isExactDateTime(LocalDateTime dateTime) {
+ return new MatcherResultChecker<>(
+ is(BigDecimal.valueOf(dateTime.toInstant(ZoneOffset.UTC).toEpochMilli())),
+ JdbcType.BIG_DECIMAL);
+ }
+
+ public static SqlTester.ResultChecker isExactTime(LocalTime time) {
+ return new MatcherResultChecker<>(
+ is((int) (time.toNanoOfDay() / 1000_000)),
+ JdbcType.INTEGER);
+ }
+
public static SqlTester.ResultChecker isWithin(double value, double delta) {
return new MatcherResultChecker<>(Matchers.within(value, delta),
JdbcType.DOUBLE);
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 96285ca7c7..5f8d129ef0 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -96,6 +96,8 @@ import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
+import java.time.LocalDateTime;
+import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
@@ -114,6 +116,8 @@ import static org.apache.calcite.linq4j.tree.Expressions.list;
import static org.apache.calcite.rel.type.RelDataTypeImpl.NON_NULLABLE_SUFFIX;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PI;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.QUANTIFY_OPERATORS;
+import static org.apache.calcite.sql.test.ResultCheckers.isExactDateTime;
+import static org.apache.calcite.sql.test.ResultCheckers.isExactTime;
import static org.apache.calcite.sql.test.ResultCheckers.isExactly;
import static org.apache.calcite.sql.test.ResultCheckers.isNullValue;
import static org.apache.calcite.sql.test.ResultCheckers.isSet;
@@ -2630,6 +2634,31 @@ public class SqlOperatorTest {
f.checkScalar("timestamp '2003-08-02 12:54:01' "
+ "- interval '-4 2:4' day to minute",
"2003-08-06 14:58:01", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp '2003-08-02 12:54:01' "
+ + "- interval '0.01' second(1, 3)",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 12, 54, 0, 990_000_000)),
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp '2003-08-02 12:54:01.000' "
+ + "- interval '1' second",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 12, 54, 0, 0)),
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp '2003-08-02 12:54:01.123' "
+ + "- interval '1' hour",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 11, 54, 1, 123_000_000)),
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp with local time zone '2003-08-02 12:54:01' "
+ + "- interval '0.456' second(1, 3)",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 12, 54, 0, 544_000_000)),
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(3) NOT NULL");
+ f.checkScalar("time '23:54:01' "
+ + "- interval '0.01' second(1, 3)",
+ isExactTime(LocalTime.of(23, 54, 0, 990_000_000)), "TIME(3) NOT NULL");
+ f.checkScalar("time '23:54:01.123' "
+ + "- interval '1' minute",
+ isExactTime(LocalTime.of(23, 53, 1, 123_000_000)), "TIME(3) NOT NULL");
+ f.checkScalar("date '2003-08-02' "
+ + "- interval '1.123' second(1, 3)",
+ "2003-08-02", "DATE NOT NULL");
// Datetime minus year-month interval
f.checkScalar("timestamp '2003-08-02 12:54:01' - interval '12' year",
@@ -2931,6 +2960,30 @@ public class SqlOperatorTest {
f.checkScalar("timestamp '2003-08-02 12:54:01'"
+ " + interval '-4 2:4' day to minute",
"2003-07-29 10:50:01", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("interval '0.003' SECOND(1, 3) + timestamp '2003-08-02 12:54:01.001'",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 12, 54, 1, 4_000_000)),
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp '2003-08-02 12:54:01.000' "
+ + "+ interval '1' second",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 12, 54, 2, 0)),
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp '2003-08-02 12:54:01.123' "
+ + "+ interval '1' hour",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 13, 54, 1, 123_000_000)),
+ "TIMESTAMP(3) NOT NULL");
+ f.checkScalar("timestamp with local time zone '2003-08-02 12:54:01' "
+ + "+ interval '0.456' second(1, 3)",
+ isExactDateTime(LocalDateTime.of(2003, 8, 2, 12, 54, 1, 456_000_000)),
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(3) NOT NULL");
+ f.checkScalar("time '23:54:01' "
+ + "+ interval '0.01' second(1, 3)",
+ isExactTime(LocalTime.of(23, 54, 1, 10_000_000)), "TIME(3) NOT NULL");
+ f.checkScalar("time '23:54:01.123' "
+ + "+ interval '1' minute",
+ isExactTime(LocalTime.of(23, 55, 1, 123_000_000)), "TIME(3) NOT NULL");
+ f.checkScalar("date '2003-08-02' "
+ + "+ interval '1.123' second(1, 3)",
+ "2003-08-02", "DATE NOT NULL");
// Datetime plus year-to-month interval
f.checkScalar("interval '5-3' year to month + date '2005-03-02'",
@@ -8059,12 +8112,12 @@ public class SqlOperatorTest {
f.checkScalar("timestampadd(" + s
+ ", 3000000000, timestamp '2016-02-24 12:42:25')",
"2016-02-24 12:42:28",
- "TIMESTAMP(0) NOT NULL"));
+ "TIMESTAMP(3) NOT NULL"));
NANOSECOND_VARIANTS.forEach(s ->
f.checkScalar("timestampadd(" + s
+ ", 2000000000, timestamp '2016-02-24 12:42:25')",
"2016-02-24 12:42:27",
- "TIMESTAMP(0) NOT NULL"));
+ "TIMESTAMP(3) NOT NULL"));
MINUTE_VARIANTS.forEach(s ->
f.checkScalar("timestampadd(" + s
+ ", 2, timestamp '2016-02-24 12:42:25')",
@@ -8183,11 +8236,24 @@ public class SqlOperatorTest {
@Test void testTimestampAddFractionalSeconds() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.TIMESTAMP_ADD, VmName.EXPAND);
- f.checkType(
- "timestampadd(SQL_TSI_FRAC_SECOND, 2, timestamp '2016-02-24 12:42:25.000000')",
+ f.checkType("timestampadd(SQL_TSI_FRAC_SECOND, 2, "
+ + "timestamp '2016-02-24 12:42:25.000000')",
// "2016-02-24 12:42:25.000002",
"TIMESTAMP(3) NOT NULL");
+ f.checkType("timestampadd(SQL_TSI_FRAC_SECOND, 2, "
+ + "timestamp with local time zone '2016-02-24 12:42:25.000000')",
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(3) NOT NULL");
+
+ f.checkType("timestampadd(SECOND, 2, timestamp '2016-02-24 12:42:25.000')",
+ "TIMESTAMP(3) NOT NULL");
+
+ f.checkType("timestampadd(HOUR, 2, time '12:42:25.000')",
+ "TIME(3) NOT NULL");
+
+ f.checkType("timestampadd(MINUTE, 2, time '12:42:25')",
+ "TIME(0) NOT NULL");
+
// The following test would correctly return "TIMESTAMP(6) NOT NULL" if max
// precision were 6 or higher
assumeTrue(f.getFactory().getTypeFactory().getTypeSystem()