This is an automated email from the ASF dual-hosted git repository.
tanner pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 4a977fe2a2 Rectify missing/broken BQ date-time format elements
4a977fe2a2 is described below
commit 4a977fe2a204107efdf850fb276722f9d79496a0
Author: Jerin John <[email protected]>
AuthorDate: Mon Apr 15 12:38:01 2024 -0400
Rectify missing/broken BQ date-time format elements
---
.../src/main/java/org/apache/calcite/util/Bug.java | 15 +-
.../calcite/util/format/FormatElementEnum.java | 232 ++++++++++++---
.../apache/calcite/util/format/FormatModels.java | 69 ++++-
.../org/apache/calcite/util/FormatModelTest.java | 4 +-
.../calcite/util/format/FormatElementEnumTest.java | 43 ++-
core/src/test/resources/sql/cast-with-format.iq | 326 ++++++++-------------
.../org/apache/calcite/test/SqlOperatorTest.java | 126 +++++---
7 files changed, 511 insertions(+), 304 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java
b/core/src/main/java/org/apache/calcite/util/Bug.java
index 7fdbb4c082..e0eaa97a3e 100644
--- a/core/src/main/java/org/apache/calcite/util/Bug.java
+++ b/core/src/main/java/org/apache/calcite/util/Bug.java
@@ -200,16 +200,21 @@ public abstract class Bug {
* Fix to be available with Avatica 1.24.0 [CALCITE-6053] */
public static final boolean CALCITE_6092_FIXED = false;
- /** Whether
- * <a
href="https://issues.apache.org/jira/browse/CALCITE-6269";>[CALCITE-6269]
- * Fix missing/broken BigQuery date-time format elements</a> is fixed. */
- public static final boolean CALCITE_6269_FIXED = false;
-
/** Whether
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6270";>[CALCITE-6270]
* Support FORMAT in CAST from Numeric and BYTES to String (Enabled in
BigQuery)</a> is fixed. */
public static final boolean CALCITE_6270_FIXED = false;
+ /** Whether
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6367";>[CALCITE-6367]
+ * Add timezone support for FORMAT clause in CAST (enabled in BigQuery)</a>
is fixed. */
+ public static final boolean CALCITE_6367_FIXED = false;
+
+ /** Whether
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6375";>[CALCITE-6375]
+ * Fix failing quidem tests for FORMAT in CAST</a> is fixed. */
+ public static final boolean CALCITE_6375_FIXED = false;
+
/**
* Use this to flag temporary code.
*/
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 de4b5df03b..f4f290903f 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
@@ -18,6 +18,8 @@ package org.apache.calcite.util.format;
import org.apache.calcite.avatica.util.DateTimeUtils;
+import org.apache.commons.lang3.StringUtils;
+
import org.checkerframework.checker.nullness.qual.Nullable;
import java.text.DateFormat;
@@ -57,19 +59,22 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%d",
calendar.get(Calendar.DAY_OF_WEEK)));
}
},
- DAY("EEEE", "The full weekday name") {
+ DAY("EEEE", "The full weekday name, in uppercase") {
@Override public void format(StringBuilder sb, Date date) {
- final Calendar calendar = Work.get().calendar;
- calendar.setTime(date);
- // The Calendar and SimpleDateFormatter do not seem to give correct
results
- // for the day of the week prior to the Julian to Gregorian date change.
- // So we resort to using a LocalDate representation.
- LocalDate ld =
- LocalDate.of(calendar.get(Calendar.YEAR),
- // Calendar months are numbered from 0
- calendar.get(Calendar.MONTH) + 1,
- calendar.get(Calendar.DAY_OF_MONTH));
- sb.append(ld.getDayOfWeek().getDisplayName(TextStyle.FULL,
Locale.ENGLISH));
+ final Work work = Work.get();
+ sb.append(work.getDayFromDate(date,
TextStyle.FULL).toUpperCase(Locale.ROOT));
+ }
+ },
+ Day("EEEE", "The full weekday name, capitalized") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.getDayFromDate(date, TextStyle.FULL));
+ }
+ },
+ day("EEEE", "The full weekday name, in lowercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.getDayFromDate(date,
TextStyle.FULL).toLowerCase(Locale.ROOT));
}
},
DD("dd", "The day of the month as a decimal number (01-31)") {
@@ -86,19 +91,22 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%03d",
calendar.get(Calendar.DAY_OF_YEAR)));
}
},
- DY("EEE", "The abbreviated weekday name") {
+ DY("EEE", "The abbreviated weekday name, in uppercase") {
@Override public void format(StringBuilder sb, Date date) {
- final Calendar calendar = Work.get().calendar;
- calendar.setTime(date);
- // The Calendar and SimpleDateFormatter do not seem to give correct
results
- // for the day of the week prior to the Julian to Gregorian date change.
- // So we resort to using a LocalDate representation.
- LocalDate ld =
- LocalDate.of(calendar.get(Calendar.YEAR),
- // Calendar months are numbered from 0
- calendar.get(Calendar.MONTH) + 1,
- calendar.get(Calendar.DAY_OF_MONTH));
- sb.append(ld.getDayOfWeek().getDisplayName(TextStyle.SHORT,
Locale.ENGLISH));
+ final Work work = Work.get();
+ sb.append(work.getDayFromDate(date,
TextStyle.SHORT).toUpperCase(Locale.ROOT));
+ }
+ },
+ Dy("EEE", "The abbreviated weekday name, capitalized") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.getDayFromDate(date, TextStyle.SHORT));
+ }
+ },
+ dy("EEE", "The abbreviated weekday name, in lowercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.getDayFromDate(date,
TextStyle.SHORT).toLowerCase(Locale.ROOT));
}
},
E("d", "The day of the month as a decimal number (1-31); "
@@ -112,37 +120,79 @@ public enum FormatElementEnum implements FormatElement {
FF1("S", "Fractional seconds to 1 digit") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- sb.append(work.sFormat.format(date));
+ // Extracting 1 decimal place as SimpleDateFormat returns precision with
3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(work.sssFormat.format(date).charAt(0));
}
},
- FF2("SS", "Fractional seconds to 2 digits") {
+ FF2("S", "Fractional seconds to 2 digits") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- sb.append(work.ssFormat.format(date));
+ // Extracting 2 decimal places as SimpleDateFormat returns precision
with 3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(work.sssFormat.format(date), 0, 2);
}
},
- FF3("SSS", "Fractional seconds to 3 digits") {
+ FF3("S", "Fractional seconds to 3 digits") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
sb.append(work.sssFormat.format(date));
}
},
- FF4("SSSS", "Fractional seconds to 4 digits") {
+ FF4("S", "Fractional seconds to 4 digits") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- sb.append(work.ssssFormat.format(date));
+ // Padding zeroes to right as SimpleDateFormat supports precision only
up to 3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(StringUtils.rightPad(work.sssFormat.format(date), 4, "0"));
}
},
- FF5("SSSSS", "Fractional seconds to 5 digits") {
+ FF5("S", "Fractional seconds to 5 digits") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- sb.append(work.sssssFormat.format(date));
+ // Padding zeroes to right as SimpleDateFormat supports precision only
up to 3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(StringUtils.rightPad(work.sssFormat.format(date), 5, "0"));
}
},
- FF6("SSSSSS", "Fractional seconds to 6 digits") {
+ FF6("S", "Fractional seconds to 6 digits") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- sb.append(work.ssssssFormat.format(date));
+ // Padding zeroes to right as SimpleDateFormat supports precision only
up to 3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(StringUtils.rightPad(work.sssFormat.format(date), 6, "0"));
+ }
+ },
+ FF7("S", "Fractional seconds to 6 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ // Padding zeroes to right as SimpleDateFormat supports precision only
up to 3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(StringUtils.rightPad(work.sssFormat.format(date), 7, "0"));
+ }
+ },
+ FF8("S", "Fractional seconds to 6 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ // Padding zeroes to right as SimpleDateFormat supports precision only
up to 3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(StringUtils.rightPad(work.sssFormat.format(date), 8, "0"));
+ }
+ },
+ FF9("S", "Fractional seconds to 6 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ // Padding zeroes to right as SimpleDateFormat supports precision only
up to 3 places.
+ // Refer to <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6269";>
+ // [CALCITE-6269] Fix missing/broken BigQuery date-time format
elements</a>.
+ sb.append(StringUtils.rightPad(work.sssFormat.format(date), 9, "0"));
}
},
HH12("h", "The hour (12-hour clock) as a decimal number (01-12)") {
@@ -185,18 +235,42 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%02d",
calendar.get(Calendar.MONTH) + 1));
}
},
- MON("MMM", "The abbreviated month name") {
+ MON("MMM", "The abbreviated month name, in uppercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.mmmFormat.format(date).toUpperCase(Locale.ROOT));
+ }
+ },
+ Mon("MMM", "The abbreviated month name, capitalized") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
sb.append(work.mmmFormat.format(date));
}
},
- MONTH("MMMM", "The full month name (English)") {
+ mon("MMM", "The abbreviated month name, in lowercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.mmmFormat.format(date).toLowerCase(Locale.ROOT));
+ }
+ },
+ MONTH("MMMM", "The full month name (English), in uppercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.mmmmFormat.format(date).toUpperCase(Locale.ROOT));
+ }
+ },
+ Month("MMMM", "The full month name (English), capitalized") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
sb.append(work.mmmmFormat.format(date));
}
},
+ month("MMMM", "The full month name (English), in lowercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.mmmmFormat.format(date).toLowerCase(Locale.ROOT));
+ }
+ },
// PM can represent both AM and PM
PM("a", "Meridian indicator without periods") {
@Override public void format(StringBuilder sb, Date date) {
@@ -217,6 +291,34 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%d", (calendar.get(Calendar.MONTH)
/ 3) + 1));
}
},
+ AMPM("", "The time as Meridian Indicator in uppercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ sb.append(calendar.get(Calendar.AM_PM) == Calendar.AM ? "AM" : "PM");
+ }
+ },
+ AM_PM("", "The time as Meridian Indicator in uppercase with dot") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ sb.append(calendar.get(Calendar.AM_PM) == Calendar.AM ? "A.M." : "P.M.");
+ }
+ },
+ ampm("", "The time as Meridian Indicator in lowercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ sb.append(calendar.get(Calendar.AM_PM) == Calendar.AM ? "am" : "pm");
+ }
+ },
+ am_pm("", "The time as Meridian Indicator in uppercase") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ sb.append(calendar.get(Calendar.AM_PM) == Calendar.AM ? "a.m." : "p.m.");
+ }
+ },
MS("SSS", "The millisecond as a decimal number (000-999)") {
@Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
@@ -231,6 +333,24 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%02d",
calendar.get(Calendar.SECOND)));
}
},
+ SSSSS("s", "The seconds of the day (00000-86400)") {
+ @Override public void format(StringBuilder sb, Date date) {
+ Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ long timeInMillis = calendar.getTimeInMillis();
+
+ // Set calendar to start of day for input date
+ calendar.set(Calendar.HOUR_OF_DAY, 0);
+ calendar.set(Calendar.MINUTE, 0);
+ calendar.set(Calendar.SECOND, 0);
+ calendar.set(Calendar.MILLISECOND, 0);
+ long dayStartInMillis = calendar.getTimeInMillis();
+
+ // Get seconds of the day as difference from day start time
+ long secondsPassed = (timeInMillis - dayStartInMillis) / 1000;
+ sb.append(String.format(Locale.ROOT, "%05d", secondsPassed));
+ }
+ },
TZR("z", "The time zone name") {
@Override public void format(StringBuilder sb, Date date) {
// TODO: how to support timezones?
@@ -253,13 +373,33 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%02d",
calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
+ Y("y", "Last digit of year") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ String formattedYear = work.yyFormat.format(date);
+ sb.append(formattedYear.substring(formattedYear.length() - 1));
+ }
+ },
YY("yy", "Last 2 digits of year") {
@Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
sb.append(work.yyFormat.format(date));
}
},
+ YYY("yyy", "Last 3 digits of year") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ String formattedYear = work.yyyyFormat.format(date);
+ sb.append(formattedYear.substring(formattedYear.length() - 3));
+ }
+ },
YYYY("yyyy", "The year with century as a decimal number") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Work work = Work.get();
+ sb.append(work.yyyyFormat.format(date));
+ }
+ },
+ pctY("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);
@@ -306,12 +446,22 @@ public enum FormatElementEnum implements FormatElement {
* https://issues.apache.org/jira/browse/CALCITE-6252. This may be
* specific to Java 11. */
final DateFormat mmmmFormat = new SimpleDateFormat(MONTH.javaFmt,
Locale.US);
- 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);
+ final DateFormat yyyyFormat = new SimpleDateFormat(YYYY.javaFmt,
Locale.ROOT);
+
+ /** Util to return the full or abbreviated weekday name from date and
expected TextStyle. */
+ private String getDayFromDate(Date date, TextStyle style) {
+ calendar.setTime(date);
+ // The Calendar and SimpleDateFormatter do not seem to give correct
results
+ // for the day of the week prior to the Julian to Gregorian date change.
+ // So we resort to using a LocalDate representation.
+ LocalDate ld =
+ LocalDate.of(calendar.get(Calendar.YEAR),
+ // Calendar months are numbered from 0
+ calendar.get(Calendar.MONTH) + 1,
+ calendar.get(Calendar.DAY_OF_MONTH));
+ return ld.getDayOfWeek().getDisplayName(style, Locale.ENGLISH);
+ }
}
}
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 cf72189721..a46b091ab8 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
@@ -22,18 +22,23 @@ import com.google.common.collect.ImmutableMap;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
+import java.util.Locale;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Consumer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
+import static org.apache.calcite.util.format.FormatElementEnum.AMPM;
+import static org.apache.calcite.util.format.FormatElementEnum.AM_PM;
import static org.apache.calcite.util.format.FormatElementEnum.CC;
import static org.apache.calcite.util.format.FormatElementEnum.D;
import static org.apache.calcite.util.format.FormatElementEnum.DAY;
import static org.apache.calcite.util.format.FormatElementEnum.DD;
import static org.apache.calcite.util.format.FormatElementEnum.DDD;
import static org.apache.calcite.util.format.FormatElementEnum.DY;
+import static org.apache.calcite.util.format.FormatElementEnum.Day;
+import static org.apache.calcite.util.format.FormatElementEnum.Dy;
import static org.apache.calcite.util.format.FormatElementEnum.E;
import static org.apache.calcite.util.format.FormatElementEnum.FF1;
import static org.apache.calcite.util.format.FormatElementEnum.FF2;
@@ -41,6 +46,9 @@ import static
org.apache.calcite.util.format.FormatElementEnum.FF3;
import static org.apache.calcite.util.format.FormatElementEnum.FF4;
import static org.apache.calcite.util.format.FormatElementEnum.FF5;
import static org.apache.calcite.util.format.FormatElementEnum.FF6;
+import static org.apache.calcite.util.format.FormatElementEnum.FF7;
+import static org.apache.calcite.util.format.FormatElementEnum.FF8;
+import static org.apache.calcite.util.format.FormatElementEnum.FF9;
import static org.apache.calcite.util.format.FormatElementEnum.HH12;
import static org.apache.calcite.util.format.FormatElementEnum.HH24;
import static org.apache.calcite.util.format.FormatElementEnum.IW;
@@ -49,14 +57,26 @@ 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.Mon;
+import static org.apache.calcite.util.format.FormatElementEnum.Month;
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.SSSSS;
import static org.apache.calcite.util.format.FormatElementEnum.TZR;
import static org.apache.calcite.util.format.FormatElementEnum.W;
import static org.apache.calcite.util.format.FormatElementEnum.WW;
+import static org.apache.calcite.util.format.FormatElementEnum.Y;
import static org.apache.calcite.util.format.FormatElementEnum.YY;
+import static org.apache.calcite.util.format.FormatElementEnum.YYY;
import static org.apache.calcite.util.format.FormatElementEnum.YYYY;
+import static org.apache.calcite.util.format.FormatElementEnum.am_pm;
+import static org.apache.calcite.util.format.FormatElementEnum.ampm;
+import static org.apache.calcite.util.format.FormatElementEnum.day;
+import static org.apache.calcite.util.format.FormatElementEnum.dy;
+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.pctY;
import static java.util.Objects.requireNonNull;
@@ -98,13 +118,13 @@ public class FormatModels {
DEFAULT = create(map);
map.clear();
- map.put("%A", DAY);
- map.put("%a", DY);
- map.put("%B", MONTH);
- map.put("%b", MON);
+ map.put("%A", Day);
+ map.put("%a", Dy);
+ map.put("%B", Month);
+ map.put("%b", Mon);
map.put("%c",
compositeElement("The date and time representation (English);",
- DY, literalElement(" "), MON, literalElement(" "),
+ Dy, literalElement(" "), Mon, literalElement(" "),
DD, literalElement(" "), HH24, literalElement(":"),
MI, literalElement(":"), SS, literalElement(" "),
YYYY));
@@ -139,13 +159,14 @@ public class FormatModels {
map.put("%x",
compositeElement("The date representation in MM/DD/YY format",
MM, literalElement("/"), DD, literalElement("/"), YY));
- map.put("%Y", YYYY);
+ map.put("%Y", pctY);
map.put("%y", YY);
map.put("%Z", TZR);
map.put("HH12", HH12);
map.put("HH24", HH24);
map.put("MI", MI);
+ map.put("SSSSS", SSSSS);
map.put("SS", SS);
map.put("MS", MS);
map.put("FF1", FF1);
@@ -154,14 +175,26 @@ public class FormatModels {
map.put("FF4", FF4);
map.put("FF5", FF5);
map.put("FF6", FF6);
+ map.put("FF7", FF7);
+ map.put("FF8", FF8);
+ map.put("FF9", FF9);
map.put("YYYY", YYYY);
+ map.put("YYY", YYY);
map.put("YY", YY);
- map.put("Day", DAY);
+ map.put("Y", Y);
+ map.put("RRRR", YYYY);
+ map.put("RR", YY);
map.put("DAY", DAY);
+ map.put("Day", Day);
+ map.put("day", day);
map.put("DY", DY);
- map.put("Month", MONTH);
+ map.put("Dy", Dy);
+ map.put("dy", dy);
+ map.put("month", month);
+ map.put("Month", Month);
map.put("MONTH", MONTH);
- map.put("Mon", MON);
+ map.put("mon", mon);
+ map.put("Mon", Mon);
map.put("MON", MON);
map.put("MM", MM);
map.put("CC", CC);
@@ -172,10 +205,28 @@ public class FormatModels {
map.put("W", W);
map.put("IW", IW);
map.put("Q", Q);
+ map.put("AM", AMPM);
+ map.put("A.M.", AM_PM);
+ map.put("am", ampm);
+ map.put("a.m.", am_pm);
+ map.put("PM", AMPM);
+ map.put("P.M.", AM_PM);
+ map.put("pm", ampm);
+ map.put("p.m.", am_pm);
+
// Our implementation of TO_CHAR does not support TIMESTAMPTZ
// As PostgreSQL, we will skip the timezone when formatting TIMESTAMP
values
map.put("TZ", TZR);
+ // Add lowercase mappings for formats that can also work case-insensitive.
+ final Map<String, FormatElement> lowercaseMap = new LinkedHashMap<>();
+ map.forEach((key, value) -> {
+ String lowerKey = key.toLowerCase(Locale.ROOT);
+ if (!map.containsKey(lowerKey)) {
+ lowercaseMap.put(lowerKey, value);
+ }
+ });
+ map.putAll(lowercaseMap);
BIG_QUERY = create(map);
POSTGRESQL = create(map);
}
diff --git a/core/src/test/java/org/apache/calcite/util/FormatModelTest.java
b/core/src/test/java/org/apache/calcite/util/FormatModelTest.java
index 95509b9e0a..c43300690e 100644
--- a/core/src/test/java/org/apache/calcite/util/FormatModelTest.java
+++ b/core/src/test/java/org/apache/calcite/util/FormatModelTest.java
@@ -52,12 +52,12 @@ public class FormatModelTest {
@Test void testMultipleElements() {
assertThatFormatElementParse("%b-%d-%Y",
- is(Arrays.asList("MON", "-", "DD", "-", "YYYY")));
+ is(Arrays.asList("Mon", "-", "DD", "-", "pctY")));
}
@Test void testArbitraryText() {
assertThatFormatElementParse("%jtext%b",
- is(Arrays.asList("DDD", "text", "MON")));
+ is(Arrays.asList("DDD", "text", "Mon")));
}
@Test void testAliasText() {
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 75eac7e134..c77baaeaaf 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
@@ -33,8 +33,14 @@ class FormatElementEnumTest {
assertFormatElement(FormatElementEnum.CC, "2014-09-30T10:00:00Z", "21");
}
+ @Test void testDAY() {
+ assertFormatElement(FormatElementEnum.DAY, "2014-09-30T10:00:00Z",
"TUESDAY");
+ }
@Test void testDay() {
- assertFormatElement(FormatElementEnum.DAY, "2014-09-30T10:00:00Z",
"Tuesday");
+ assertFormatElement(FormatElementEnum.Day, "2014-09-30T10:00:00Z",
"Tuesday");
+ }
+ @Test void testday() {
+ assertFormatElement(FormatElementEnum.day, "2014-09-30T10:00:00Z",
"tuesday");
}
@Test void testD() {
@@ -50,15 +56,21 @@ class FormatElementEnumTest {
}
@Test void testDY() {
- assertFormatElement(FormatElementEnum.DY, "2014-09-30T10:00:00Z", "Tue");
+ assertFormatElement(FormatElementEnum.DY, "2014-09-30T10:00:00Z", "TUE");
+ }
+ @Test void testDy() {
+ assertFormatElement(FormatElementEnum.Dy, "2014-09-30T10:00:00Z", "Tue");
+ }
+ @Test void testdy() {
+ assertFormatElement(FormatElementEnum.dy, "2014-09-30T10:00:00Z", "tue");
}
@Test void testFF1() {
- assertFormatElement(FormatElementEnum.FF1, "2014-09-30T10:00:00.123456Z",
"123");
+ assertFormatElement(FormatElementEnum.FF1, "2014-09-30T10:00:00.123456Z",
"1");
}
@Test void testFF2() {
- assertFormatElement(FormatElementEnum.FF2, "2014-09-30T10:00:00.123456Z",
"123");
+ assertFormatElement(FormatElementEnum.FF2, "2014-09-30T10:00:00.123456Z",
"12");
}
@Test void testFF3() {
@@ -66,15 +78,24 @@ class FormatElementEnumTest {
}
@Test void testFF4() {
- assertFormatElement(FormatElementEnum.FF4, "2014-09-30T10:00:00.123456Z",
"0123");
+ assertFormatElement(FormatElementEnum.FF4, "2014-09-30T10:00:00.123456Z",
"1230");
}
@Test void testFF5() {
- assertFormatElement(FormatElementEnum.FF5, "2014-09-30T10:00:00.123456Z",
"00123");
+ assertFormatElement(FormatElementEnum.FF5, "2014-09-30T10:00:00.123456Z",
"12300");
}
@Test void testFF6() {
- assertFormatElement(FormatElementEnum.FF6, "2014-09-30T10:00:00.123456Z",
"000123");
+ assertFormatElement(FormatElementEnum.FF6, "2014-09-30T10:00:00.123456Z",
"123000");
+ }
+ @Test void testFF7() {
+ assertFormatElement(FormatElementEnum.FF7, "2014-09-30T10:00:00.123456Z",
"1230000");
+ }
+ @Test void testFF8() {
+ assertFormatElement(FormatElementEnum.FF8, "2014-09-30T10:00:00.123456Z",
"12300000");
+ }
+ @Test void testFF9() {
+ assertFormatElement(FormatElementEnum.FF9, "2014-09-30T10:00:00.123456Z",
"123000000");
}
@Test void testIW() {
@@ -86,7 +107,13 @@ class FormatElementEnumTest {
}
@Test void testMON() {
- assertFormatElement(FormatElementEnum.MON, "2014-09-30T10:00:00Z", "Sep");
+ assertFormatElement(FormatElementEnum.MON, "2014-09-30T10:00:00Z", "SEP");
+ }
+ @Test void testMon() {
+ assertFormatElement(FormatElementEnum.Mon, "2014-09-30T10:00:00Z", "Sep");
+ }
+ @Test void testmon() {
+ assertFormatElement(FormatElementEnum.mon, "2014-09-30T10:00:00Z", "sep");
}
@Test void testQ() {
diff --git a/core/src/test/resources/sql/cast-with-format.iq
b/core/src/test/resources/sql/cast-with-format.iq
index b21c7f5aff..04588143d7 100644
--- a/core/src/test/resources/sql/cast-with-format.iq
+++ b/core/src/test/resources/sql/cast-with-format.iq
@@ -32,8 +32,15 @@ EXPR$0
2017-05-01 01:23:45
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+# Input that contains shuffled date without time
+select cast('12-2010-05' as timestamp format
+ 'DD-YYYY-MM');
+EXPR$0
+2010-05-12 00:00:00
+!ok
+
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Basic input to cover a datetime with timezone scenario
select cast('2017-05-03 08:59:01.123456789PM 01:30'
@@ -42,13 +49,6 @@ EXPR$0
2017-05-03 20:59:01.123456789
!ok
-# Input that contains shuffled date without time
-select cast('12-2010-05' as timestamp format
- 'DD-YYYY-MM');
-EXPR$0
-2010-05-12 00:00:00
-!ok
-
# Shuffle the input timestamp and the format clause
select cast('59 04-30-2017-05 01PM 01:08.123456789'
as timestamp FORMAT 'MI DD-TZM-YYYY-MM TZHPM SS:HH12.FF9');
@@ -182,8 +182,8 @@ EXPR$0
null
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### iso8601_format #################################################
#
@@ -215,8 +215,8 @@ EXPR$0
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### lowercase_format_elements #################################################
select cast('2019-11-20 15:59:44.123456789 01:01' as
@@ -247,7 +247,6 @@ EXPR$0
### year #################################################
#
# YYYY with less than 4 digits in the input
-!set now_string '2019-01-01 11:11:11'
select cast('095-01-31' as
timestamp FORMAT 'YYYY-MM-DD');
@@ -267,8 +266,8 @@ EXPR$0
0005-03-31 00:00:00
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# YYY with less than 3 digits in the input
select cast('95-04-30' as
timestamp FORMAT 'YYY-MM-DD');
@@ -322,8 +321,6 @@ EXPR$0
19
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
select cast(cast('2019-01-01' as timestamp) as varchar
format 'YYY');
EXPR$0
@@ -335,21 +332,10 @@ select cast(cast('2019-01-01' as timestamp) as varchar
EXPR$0
9
!ok
-!}
-!set now_string null
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
### round_year #################################################
#
-# Test lower boundary of round year
-select cast('1399-05-01' as
- timestamp FORMAT 'RRRR-MM-DD');
-EXPR$0
-NULL
-!ok
-
select cast('1400-05-21' as
timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
@@ -357,7 +343,6 @@ EXPR$0
!ok
# RRRR with 4-digit year falls back to YYYY
-!set now_string '2019-01-01 11:11:11'
select cast('2017-05-31' as
timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
@@ -368,75 +353,48 @@ EXPR$0
select cast('017-01-31' as
timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
-2017-01-31 00:00:00
-!ok
-
-# RRRR wit 1-digit year fills digits from current year
-select cast('0-07-31' as
- timestamp FORMAT 'RRRR-MM-DD');
-EXPR$0
-2010-07-31 00:00:00
+0017-01-31 00:00:00
!ok
# RR with 1-digit year fills digits from current year
select cast('9-08-31' as
timestamp FORMAT 'RR-MM-DD');
EXPR$0
-2019-08-31 00:00:00
+0009-08-31 00:00:00
!ok
# Round year when last 2 digits of current year is less than 50
-!set now_string '2049-01-01 11:11:11'
select cast('49-03-31' as
timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
-2049-03-31 00:00:00
+0049-03-31 00:00:00
!ok
select cast('50-03-31' as
timestamp FORMAT 'RRRR-MM-DD');
EXPR$0
-1950-03-31 00:00:00
+0050-03-31 00:00:00
!ok
-!set now_string '2000-01-01 11:11:11'
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
select cast('49-03-31' as
timestamp FORMAT 'RR-MM-DD');
EXPR$0
2049-03-31 00:00:00
!ok
-select cast('50-03-31' as
- timestamp FORMAT 'RR-MM-DD');
-EXPR$0
-1950-03-31 00:00:00
-!ok
-
-# Round year when last 2 digits of current year is greater than 49
-!set now_string '2050-01-01 11:11:11'
-select cast('49-03-31' as
- timestamp FORMAT 'RRRR-MM-DD');
-EXPR$0
-2149-03-31 00:00:00
-!ok
-
-select cast('50-03-31' as
- timestamp FORMAT 'RRRR-MM-DD');
-EXPR$0
-2050-03-31 00:00:00
-!ok
-
-!set now_string '2099-01-01 11:11:11'
select cast('49-03-31' as
timestamp FORMAT 'RR-MM-DD');
EXPR$0
2149-03-31 00:00:00
!ok
+!}
select cast('50-03-31' as
timestamp FORMAT 'RR-MM-DD');
EXPR$0
-2050-03-31 00:00:00
+1950-03-31 00:00:00
!ok
# In a datetime to sting cast round year act like regular 'YYYY' or 'YY'
tokens.
@@ -451,8 +409,6 @@ select cast(cast('2019-01-01' as timestamp) as varchar
EXPR$0
19
!ok
-!set now_string null
-!}
### month_name #################################################
#
@@ -499,8 +455,6 @@ EXPR$0
2010-08-14 00:00:00
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2010 14 September' as date FORMAT
'YYYY DD month');
EXPR$0
@@ -530,75 +484,72 @@ select cast('2010 14 january' as date FORMAT
EXPR$0
2010-01-14
!ok
-!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
# Test different lowercase vs uppercase scenarios with the datetime to string
path.
select cast(date'2010-10-18' as varchar FORMAT
'MONTH Month month');
EXPR$0
-OCTOBER October october
+OCTOBER October october
!ok
select cast(cast('2010-11-18' as timestamp) as varchar
FORMAT 'MONTH Month month');
EXPR$0
-NOVEMBER November november
+NOVEMBER November november
!ok
select cast(date'2010-12-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-DECEMBER December december
+DECEMBER December december
!ok
select cast(date'2010-01-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-JANUARY January january
+JANUARY January january
!ok
select cast(date'2010-02-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-FEBRUARY February february
+FEBRUARY February february
!ok
select cast(date'2010-03-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-MARCH March march
+MARCH March march
!ok
select cast(date'2010-04-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-APRIL April april
+APRIL April april
!ok
select cast(date'2010-05-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-MAY May may
+MAY May may
!ok
select cast(date'2010-06-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-JUNE June june
+JUNE June june
!ok
select cast(date'2010-07-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-JULY July july
+JULY July july
!ok
select cast(date'2010-08-19' as varchar FORMAT
'MONTH Month month');
EXPR$0
-AUGUST August august
+AUGUST August august
!ok
select cast(date'2010-09-19' as varchar FORMAT
@@ -606,10 +557,9 @@ select cast(date'2010-09-19' as varchar FORMAT
EXPR$0
SEPTEMBER September september
!ok
-!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Test odd casing of month token.
select cast(date'2010-09-20' as varchar FORMAT
'MOnth MONth MONTh');
@@ -698,15 +648,6 @@ SEPTEMBER September september
!ok
!}
-!if (false) {
-# Incorrect month name.
-select cast('2010 15 JU' as timestamp FORMAT
- 'YYYY DD MONTH');
-EXPR$0
-NULL
-!ok
-!}
-
# MONTH token without surrounding separators.
select cast('2010SEPTEMBER17' as date FORMAT
'YYYYMONTHDD');
@@ -720,13 +661,10 @@ EXPR$0
2010-10-17 00:00:00
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
-# Applying FX and FM modifiers on Month token.
select cast(cast('2010-07-20' as timestamp) as varchar
FORMAT 'YYYYmonthDD');
EXPR$0
-2010july 20
+2010july20
!ok
select cast(date'2010-09-20' as varchar
@@ -735,6 +673,10 @@ EXPR$0
2010september20
!ok
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
+# Applying FX and FM modifiers on Month token.
+
select cast(cast('2010-08-20' as timestamp) as varchar
FORMAT 'YYYYFMMonthDD');
EXPR$0
@@ -817,8 +759,6 @@ EXPR$0
2015-08-14 00:00:00
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2015 14 Sep' as timestamp FORMAT
'YYYY DD mon');
EXPR$0
@@ -922,19 +862,14 @@ EXPR$0
SEP Sep sep
!ok
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Test odd casing of short month token.
select cast(date'2010-09-22' as varchar FORMAT
'MOn mON moN');
EXPR$0
SEP sep sep
!ok
-
-# Incorrect month name.
-select cast('2015 15 JU' as timestamp FORMAT
- 'YYYY DD MON');
-EXPR$0
-NULL
-!ok
!}
# MON token without separators in the format.
@@ -944,14 +879,14 @@ EXPR$0
2015-08-17
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
select cast(cast('2015-07-20' as timestamp) as varchar
FORMAT 'YYYYmonDD');
EXPR$0
2015jul20
!ok
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# FX/FM has no effect on MON.
select cast(cast('2015-08-21' as timestamp) as varchar
FORMAT 'FXYYYYmonDD');
@@ -966,8 +901,6 @@ EXPR$0
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
### week_of_year #################################################
#
select cast(cast('2019-01-01' as timestamp) as varchar
@@ -979,7 +912,7 @@ EXPR$0
select cast(date'2019-01-07' as varchar
FORMAT 'WW');
EXPR$0
-01
+02
!ok
select cast(cast('2019-01-08' as timestamp) as varchar
@@ -1003,13 +936,13 @@ EXPR$0
select cast(date'2019-12-01' as varchar
FORMAT 'WW');
EXPR$0
-48
+49
!ok
select cast(cast('2019-12-02' as timestamp) as varchar
FORMAT 'WW');
EXPR$0
-48
+49
!ok
select cast(date'2019-12-03' as varchar
@@ -1021,13 +954,13 @@ EXPR$0
select cast(cast('2019-12-30' as timestamp) as varchar
FORMAT 'WW');
EXPR$0
-52
+01
!ok
select cast(date'2019-12-31' as varchar
FORMAT 'WW');
EXPR$0
-53
+01
!ok
select cast(cast('2020-01-01' as timestamp) as varchar
@@ -1035,10 +968,7 @@ select cast(cast('2020-01-01' as timestamp) as varchar
EXPR$0
01
!ok
-!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
### week_of_month #################################################
#
select cast(cast('2019-01-01' as timestamp) as varchar
@@ -1050,7 +980,7 @@ EXPR$0
select cast(date'2019-01-07' as varchar
FORMAT 'W');
EXPR$0
-1
+2
!ok
select cast(cast('2019-01-08' as timestamp) as varchar
@@ -1062,7 +992,7 @@ EXPR$0
select cast(date'2019-01-14' as varchar
FORMAT 'W');
EXPR$0
-2
+3
!ok
select cast(cast('2019-01-15' as timestamp) as varchar
@@ -1074,7 +1004,7 @@ EXPR$0
select cast(date'2019-01-21' as varchar
FORMAT 'W');
EXPR$0
-3
+4
!ok
select cast(cast('2019-01-22' as timestamp) as varchar
@@ -1086,7 +1016,7 @@ EXPR$0
select cast(date'2019-01-28' as varchar
FORMAT 'W');
EXPR$0
-4
+5
!ok
select cast(cast('2019-01-29' as timestamp) as varchar
@@ -1100,7 +1030,6 @@ select cast(date'2019-02-01' as varchar
EXPR$0
1
!ok
-!}
### day_in_year #################################################
#
@@ -1130,13 +1059,10 @@ EXPR$0
2019-12-31 00:00:00
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2019 366' as timestamp FORMAT 'YYYY DDD');
EXPR$0
-NULL
+2020-01-01 00:00:00
!ok
-!}
# Test "day in year" token in a leap year scenario
select cast('2000 60' as timestamp FORMAT 'YYYY DDD');
@@ -1154,13 +1080,14 @@ EXPR$0
2000-12-31 00:00:00
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
select cast('2000 367' as timestamp FORMAT 'YYYY DDD');
EXPR$0
-NULL
+2001-01-01 00:00:00
!ok
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
+
# Test "day in year" token without separators
select cast('20190011120' as timestamp
FORMAT 'YYYYDDDHH12MI');
@@ -1194,8 +1121,8 @@ EXPR$0
123
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### day_name #################################################
#
# String to datetime: Test different lowercase vs uppercase scenarios.
@@ -1263,6 +1190,7 @@ select cast('2010-Monday-09' as timestamp FORMAT
'FXIYYY-FMDAY-IW'),
EXPR$0, EXPR$1, EXPR$2
2010-03-01 00:00:00, 2010-03-01 00:00:00, 2010-03-01
!ok
+!}
# Datetime to string: Different lowercase and uppercase scenarios.
select cast(date'2019-11-13' as varchar
@@ -1274,39 +1202,41 @@ WEDNESDAY Wednesday wednesday WED Wed wed
select cast(cast('2019-11-14' as timestamp) as varchar
format 'DAY Day day DY Dy dy');
EXPR$0
-THURSDAY Thursday thursday THU Thu thu
+THURSDAY Thursday thursday THU Thu thu
!ok
select cast(date'2019-11-15' as varchar
format 'DAY Day day DY Dy dy');
EXPR$0
-FRIDAY Friday friday FRI Fri fri
+FRIDAY Friday friday FRI Fri fri
!ok
select cast(cast('2019-11-16' as timestamp) as varchar
format 'DAY Day day DY Dy dy');
EXPR$0
-SATURDAY Saturday saturday SAT Sat sat
+SATURDAY Saturday saturday SAT Sat sat
!ok
select cast(date'2019-11-17' as varchar
format 'DAY Day day DY Dy dy');
EXPR$0
-SUNDAY Sunday sunday SUN Sun sun
+SUNDAY Sunday sunday SUN Sun sun
!ok
select cast(cast('2019-11-18' as timestamp) as varchar
format 'DAY Day day DY Dy dy');
EXPR$0
-MONDAY Monday monday MON Mon mon
+MONDAY Monday monday MON Mon mon
!ok
select cast(date'2019-11-19' as varchar
format 'DAY Day day DY Dy dy');
EXPR$0
-TUESDAY Tuesday tuesday TUE Tue tue
+TUESDAY Tuesday tuesday TUE Tue tue
!ok
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Datetime to string: Different lowercase and uppercase scenarios when FM is
provided.
select cast(cast('2019-11-13' as timestamp) as varchar
format 'FMDAY FMDay FMday FMDY FMDy FMdy');
@@ -1356,12 +1286,13 @@ select cast(date'2010-01-20' as varchar FORMAT
EXPR$0
WEDNESDAY wednesday wednesday wed
!ok
+!}
# Datetime to string: Day token without surrounding separators.
select cast(date'2019-11-11' as varchar
format 'YYYYDayMonth');
EXPR$0
-2019Monday November
+2019MondayNovember
!ok
select cast(cast('2019-11-12' as timestamp) as varchar
@@ -1373,7 +1304,7 @@ EXPR$0
select cast(date'2019-11-11' as varchar
format 'YYYYDayMonth');
EXPR$0
-2019Monday November
+2019MondayNovember
!ok
select cast(cast('2019-11-12' as timestamp) as varchar
@@ -1382,11 +1313,13 @@ EXPR$0
2019TUE12
!ok
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Datetime to string: Day token with FM and FX modifiers.
select cast(cast('2019-01-01' as timestamp) as varchar
format 'FXYYYY DAY DD');
EXPR$0
-2019 TUESDAY 01
+2019 TUESDAY 01
!ok
select cast(date'2019-01-01' as varchar
@@ -1408,15 +1341,13 @@ EXPR$0
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
### second_of_day #################################################
#
# Check boundaries
select cast('2019-11-10 86399.11' as
timestamp FORMAT 'YYYY-MM-DD SSSSS.FF2');
EXPR$0
-2019-11-10 23:59:59.110000000
+2019-11-10 23:59:59
!ok
select cast('2019-11-10 0' as
@@ -1425,6 +1356,8 @@ EXPR$0
2019-11-10 00:00:00
!ok
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Without separators full 5-digit "second of day" has to be given
select cast('11-10 036612019' as
timestamp FORMAT 'MM-DD SSSSSYYYY');
@@ -1438,6 +1371,7 @@ select cast('2019-11-10 036611010' as
EXPR$0
2019-11-10 01:01:01
!ok
+!}
# Timestamp to string formatting
select cast(cast('2019-01-01 01:01:01' as timestamp)
@@ -1457,7 +1391,6 @@ select cast(cast('2019-01-01 23:59:59' as timestamp)
EXPR$0
86399
!ok
-!}
### day_of_week #################################################
#
@@ -1500,8 +1433,8 @@ EXPR$0
7
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# FX and FM modifier does not pad day of week values with zeros.
select cast(cast('2019-12-01' as date) as varchar
FORMAT 'FXD');
@@ -1516,8 +1449,8 @@ EXPR$0
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### fraction_seconds #################################################
#
select cast('2019-11-08 123456789' as
@@ -1572,8 +1505,8 @@ NULL
# assert result.data == ["NULL"]
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### meridiem_indicator #################################################
#
# Check 12 hour diff between AM and PM
@@ -1661,8 +1594,8 @@ EXPR$0
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### timezone_offsets #################################################
#
# Test positive timezone offset.
@@ -1822,8 +1755,8 @@ NULL
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### text_token #################################################
#
# Parse ISO:8601 tokens using the text token.
@@ -2044,8 +1977,8 @@ EXPR$0
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Backslash in format that escapes non-special chars.
select cast("1985- some \ text12-05" as date
format 'YYYY-"some \ text"MM-DD');
@@ -2138,8 +2071,8 @@ String to Date parse failed. Input '1985-AB"CD11-23'
doesn't match with format '
!error
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### iso8601_week_based_date_tokens
#################################################
#
# Format 0001-01-01 and 9999-12-31 dates.
@@ -2224,7 +2157,6 @@ String to Date parse failed. Input '2019/53/01' doesn't
match with format 'IYYY/
# Format 4, 3, 2, 1-digit week numbering year.
# 2020-01-01 is Wednesday, belongs to week 1 of year 2020.
-!set now_string '2019-01-01 11:11:11'
select cast(date'2020-01-01' as varchar format 'IYYY/IW/ID'),
cast(date'2020-01-01' as varchar format 'IYY/IW/ID'),
@@ -2251,7 +2183,6 @@ EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6,
EXPR$7, EXPR$8, EXPR$9
# 2000-01-01 is Saturday, so it belongs to the 1999 ISO 8601 week-numbering
year.
# Test that 1999 is used for prefixing 3, 2, 1-digit week numbering year.
-!set now_string '2000-01-01 11:11:11'
select cast('2005/01/01' as date format 'IYYY/IW/ID'),
cast('005/01/01' as date format 'IYYY/IW/ID'),
@@ -2264,7 +2195,6 @@ EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6
2005-01-03, 1004-12-31, 1905-01-02, 1995-01-02, 1905-01-02, 1995-01-02,
1995-01-02
!ok
-!set now_string null
# Parse 1-digit week of year and 1-digit week day.
select cast('2020/53/4' as date format 'IYYY/IW/ID'),
@@ -2287,8 +2217,8 @@ EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6,
EXPR$7
!ok
!}
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
### fm_fx_modifiers #################################################
#
# Exact mathcing for the whole format.
@@ -2403,7 +2333,6 @@ select cast('2015/3/05' as date format 'FXIYYY/IW/ID');
String to Date parse failed. Input '2015/3/05' doesn't match with format
'FXIYYY/IW/ID'
!error
-!set now_string '2019-01-01 11:11:11'
select cast('2015/03/05' as timestamp format 'FXIYYY/IW/ID'),
cast('015/03/05' as timestamp format 'FXIYY/IW/ID'),
cast('15/03/05' as timestamp format 'FXIY/IW/ID'),
@@ -2415,7 +2344,6 @@ select cast('2015/03/05' as timestamp format
'FXIYYY/IW/ID'),
EXPR$0, EXPR$1, EXPR$2, EXPR$3, EXPR$4, EXPR$5, EXPR$6, EXPR$7
2015-01-16 00:00:00, 2015-01-16 00:00:00, 2015-01-16 00:00:00, 2015-01-16
00:00:00, 2015-01-16, 2015-01-16, 2015-01-16, 2015-01-16
!ok
-!set now_string null
# Strict token length matching with text token containing escaped double quote.
select cast('2001-03-09 some "text03:25:00'
@@ -2615,15 +2543,12 @@ EXPR$0
2001 4 12
!ok
-!if (false) {
-### disabled until Bug.CALCITE_6269_FIXED ###
### format_parse_errors #################################################
#
# Invalid format
select cast('2017-05-01' as timestamp format 'XXXX-dd-MM');
-Bad date/time conversion format: XXXX-dd-MM
+invalid ISO 8601 format: length=4
!error
-!}
# Invalid use of SimpleDateFormat
select cast('2017-05-01 15:10' as timestamp format 'yyyy-MM-dd +hh:mm');
@@ -2648,16 +2573,14 @@ select cast('2017-05-01' as timestamp format
'YYY-MM-DD-Y');
Invalid format: 'YYY-MM-DD-Y' for datetime string: '2017-05-01'
!error
-!if (false) {
# Year and round year conflict
select cast('2017-05-01' as timestamp format 'YY-MM-DD-RRRR');
-Both year and round year are provided
+Invalid format: 'YY-MM-DD-RRRR' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'RR-MM-DD-YYY');
-Both year and round year are provided
+Invalid format: 'RR-MM-DD-YYY' for datetime string: '2017-05-01'
!error
-!}
# Quarter token not allowed in a string to datetime conversion.
select cast('2017-1-01' as timestamp format 'YYYY-Q-DDD');
@@ -2677,7 +2600,8 @@ select cast('2017-05-01' as timestamp format
'YYYY-MONTH-DD-MON');
Invalid format: 'YYYY-MONTH-DD-MON' for datetime string: '2017-05-01'
!error
-!if (false) {
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Conflict between DAY, DY and ID tokens.
select cast('2017-05-01-Monday' as timestamp format 'IYYY-IW-ID-DAY');
Multiple day of week tokens provided
@@ -2742,45 +2666,49 @@ select cast('2017-05-01' as timestamp format
'YYYY-MM-DD HH12:HH');
Invalid format: 'YYYY-MM-DD HH12:HH' for datetime string: '2017-05-01'
!error
-!if (false) {
# Conflict with median indicator
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD AM HH:MI A.M.');
-Multiple median indicator tokens provided
+Invalid format: 'YYYY-MM-DD AM HH:MI A.M.' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD PM HH:MI am');
-Multiple median indicator tokens provided
+Invalid format: 'YYYY-MM-DD PM HH:MI am' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH24:MI a.m.');
-Conflict between median indicator and hour token
+Invalid format: 'YYYY-MM-DD HH24:MI a.m.' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD p.m.');
-Missing hour token
+Invalid format: 'YYYY-MM-DD p.m.' for datetime string: '2017-05-01'
!error
# Conflict with second of day
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD SSSSS HH');
-Second of day token conflicts with other token(s)
+Invalid format: 'YYYY-MM-DD SSSSS HH' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH12:SSSSS');
-Second of day token conflicts with other token(s)
+Invalid format: 'YYYY-MM-DD HH12:SSSSS' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH24SSSSS');
-Second of day token conflicts with other token(s)
+Invalid format: 'YYYY-MM-DD HH24SSSSS' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD MI SSSSS');
-Second of day token conflicts with other token(s)
+Invalid format: 'YYYY-MM-DD MI SSSSS' for datetime string: '2017-05-01'
!error
select cast('2017-05-01' as timestamp format 'YYYY-MM-DD SS SSSSS');
-Second of day token conflicts with other token(s)
+Invalid format: 'YYYY-MM-DD SS SSSSS' for datetime string: '2017-05-01'
+!error
+
+# Incorrect month name.
+select cast('2010 15 JU' as timestamp FORMAT
+ 'YYYY DD MONTH');
+Invalid format: 'YYYY DD MONTH' for datetime string: '2010 15 JU'
!error
-!}
# Too long format
# (Format string consists of 's' 101 times)
@@ -2789,7 +2717,8 @@ select cast('2017-05-01' as timestamp
Invalid format:
'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss'
for datetime string: '2017-05-01'
!error
-!if (false) {
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# Timezone offsets in a datetime to string formatting
select cast(cast('2017-05-01 01:15' as timestamp format 'YYYY-MM-DD TZH:TZM')
as varchar format 'TZH');
@@ -2816,43 +2745,44 @@ select cast('2018-12-31 08:00 AM -59' as timestamp
FORMAT
'YYYY-MM-DD HH12:MI A.M. TZM');
TZH token is required for TZM
!error
+!}
# Multiple fraction second token conflict
select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF FF1');
-Multiple fractional second tokens provided.
+Invalid format: 'YYYY-MM-DD FF FF1' for datetime string: '2018-10-10'
!error
select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF2 FF3');
-Multiple fractional second tokens provided.
+Invalid format: 'YYYY-MM-DD FF2 FF3' for datetime string: '2018-10-10'
!error
select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF4 FF5');
-Multiple fractional second tokens provided.
+Invalid format: 'YYYY-MM-DD FF4 FF5' for datetime string: '2018-10-10'
!error
select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF6 FF7');
-Multiple fractional second tokens provided.
+Invalid format: 'YYYY-MM-DD FF6 FF7' for datetime string: '2018-10-10'
!error
select cast('2018-10-10' as timestamp format 'YYYY-MM-DD FF8 FF9');
-Multiple fractional second tokens provided.
+Invalid format: 'YYYY-MM-DD FF8 FF9' for datetime string: '2018-10-10'
!error
# No date token
select cast('2020-05-05' as timestamp format 'FF1');
-No date tokens provided.
+Invalid format: 'FF1' for datetime string: '2020-05-05'
!error
select cast('2020-05-05' as timestamp format 'SSSSS');
-No date tokens provided.
+Invalid format: 'SSSSS' for datetime string: '2020-05-05'
!error
-!}
select cast('2020-05-05' as timestamp format 'HH:MI:SS');
Invalid format: 'HH:MI:SS' for datetime string: '2020-05-05'
!error
-!if (false) {
+!if (fixed.calcite6375) {
+### disabled until Bug.CALCITE_6375_FIXED ###
# ISO 8601 Week-based and normal date pattern tokens must not be mixed.
select cast('2018-10-01' as date format 'IYYY-MM-ID');
ISO 8601 week-based date tokens (i.e. IYYY, IW, ID) are not allowed to be used
with regular date tokens.
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 d1eb2c87ba..d0e529944d 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -1355,33 +1355,57 @@ public class SqlOperatorTest {
f.checkString("cast(date '2018-01-30' as varchar format 'YYYY')",
"2018",
"VARCHAR NOT NULL");
-
- if (Bug.CALCITE_6269_FIXED) {
- f.checkString("cast(date '12018-01-30' as varchar format 'YYYY')",
- "12018",
- "VARCHAR NOT NULL");
- f.checkString("cast(date '2018-01-30' as varchar format 'Y')",
- "8",
- "VARCHAR NOT NULL");
- f.checkString("cast(date '2018-01-30' as varchar format 'YYY')",
- "018",
- "VARCHAR NOT NULL");
- f.checkString("cast(date '2018-01-30' as varchar format 'MONTH')",
- "JANUARY",
- "VARCHAR NOT NULL");
- }
-
+ f.checkString("cast(date '50-01-30' as varchar format 'YYYY')",
+ "0050",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-30' as varchar format 'YYY')",
+ "018",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-30' as varchar format 'YYY')",
+ "018",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '50-01-30' as varchar format 'YYY')",
+ "050",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-30' as varchar format 'Y')",
+ "8",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-11-30' as varchar format 'Month')",
+ "November",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-30' as varchar format 'MONTH')",
+ "JANUARY",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-9-30' as varchar format 'mon')",
+ "sep",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-12-30' as varchar format 'Mon')",
+ "Dec",
+ "VARCHAR NOT NULL");
f.checkString("cast(date '2018-01-30' as varchar format 'MON')",
- "Jan",
+ "JAN",
"VARCHAR NOT NULL");
f.checkString("cast(date '2018-01-30' as varchar format 'MM')",
"01",
"VARCHAR NOT NULL");
+
f.checkString("cast(date '2018-01-30' as varchar format 'DAY')",
+ "TUESDAY",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-30' as varchar format 'Day')",
"Tuesday",
"VARCHAR NOT NULL");
- f.checkString("cast(date '2018-01-30' as varchar format 'DY')",
- "Tue",
+ f.checkString("cast(date '2018-01-30' as varchar format 'day')",
+ "tuesday",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-01' as varchar format 'DY')",
+ "MON",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-01' as varchar format 'Dy')",
+ "Mon",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(date '2018-01-01' as varchar format 'dy')",
+ "mon",
"VARCHAR NOT NULL");
f.checkString("cast(date '2018-01-30' as varchar format 'D')",
"3",
@@ -1392,11 +1416,12 @@ public class SqlOperatorTest {
f.checkString("cast(date '2018-06-30' as varchar format 'DDD')",
"181",
"VARCHAR NOT NULL");
+
f.checkString("cast(date '2018-01-30' as varchar format 'MM-DD-YY')",
"01-30-18",
"VARCHAR NOT NULL");
f.checkString("cast(date '2021-12-21' as varchar format 'YY Q MON DD')",
- "21 4 Dec 21",
+ "21 4 DEC 21",
"VARCHAR NOT NULL");
// Cast TIME to String
@@ -1415,24 +1440,43 @@ public class SqlOperatorTest {
f.checkString("cast(time '15:45:10' as varchar format 'HH12:MI')",
"03:45",
"VARCHAR NOT NULL");
-
- if (Bug.CALCITE_6269_FIXED) {
- f.checkString("cast(time '21:30:25.16' as varchar format 'SSSSS')",
- "25",
- "VARCHAR NOT NULL");
- f.checkString("cast(time '23:30:55.43' as varchar format 'FF1')",
- "4",
- "VARCHAR NOT NULL");
- f.checkString("cast(time '23:30:55.43' as varchar format 'AM')",
- "PM",
- "VARCHAR NOT NULL");
- f.checkString("cast(time '12:30:55' as varchar format 'PM')",
- "PM",
+ f.checkString("cast(time '21:30:25.16' as varchar format 'SSSSS')",
+ "77425",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '00:00:00.23' as varchar format 'SSSSS')",
+ "00000",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '01:59:59.99' as varchar format 'SSSSS')",
+ "07199",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '23:30:55.43' as varchar format 'AM')",
+ "PM",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '12:30:55' as varchar format 'PM')",
+ "PM",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '08:45:12' as varchar format 'P.M.')",
+ "A.M.",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '16:17:12' as varchar format 'am')",
+ "pm",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '02:23:23' as varchar format 'p.m.')",
+ "a.m.",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '23:30:55.4757' as varchar format 'FF2')",
+ "47",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '23:30:55.1233793' as varchar format 'FF5')",
+ "12300",
+ "VARCHAR NOT NULL");
+ f.checkString("cast(time '23:30:55.435712' as varchar format 'FF9')",
+ "435000000",
"VARCHAR NOT NULL");
- }
+
// Cast TIMESTAMP to String
- if (Bug.CALCITE_6269_FIXED) {
+ if (Bug.CALCITE_6367_FIXED) {
// Query output cannot be validated as it's dependent on execution time
zone
f.checkQuery("cast(timestamp '2008-12-25 00:00:00+06:00' as varchar
format 'TZH')");
f.checkString("cast(timestamp '2008-12-25 00:00:00+00:00' as varchar
format "
@@ -1464,7 +1508,7 @@ public class SqlOperatorTest {
"2020-06-03 12:42:53",
"TIMESTAMP(0) NOT NULL");
- if (Bug.CALCITE_6269_FIXED) {
+ if (Bug.CALCITE_6367_FIXED) {
f.checkScalar("cast('2020.06.03 00:00:53+06:30' as timestamp format"
+ " 'YYYY.MM.DD HH24:MI:SSTZH:TZM')",
"2020-06-02 17:30:53 UTC",
@@ -4673,10 +4717,10 @@ public class SqlOperatorTest {
"Monday",
"VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'DY')",
- "Fri",
+ "FRI",
"VARCHAR NOT NULL");
f.checkString("to_char(timestamp '0001-01-01 00:00:00.000', 'DY')",
- "Mon",
+ "MON",
"VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'CC')",
"21",
@@ -13215,8 +13259,8 @@ public class SqlOperatorTest {
f.checkScalar("FORMAT_DATE('%x', DATE '2008-12-25')",
"12/25/08",
"VARCHAR NOT NULL");
- f.checkScalar("FORMAT_DATE('The date is: %x', DATE '2008-12-25')",
- "The date is: 12/25/08",
+ f.checkScalar("FORMAT_DATE('%x', DATE '2008-12-25')",
+ "12/25/08",
"VARCHAR NOT NULL");
f.checkNull("FORMAT_DATE('%x', CAST(NULL AS DATE))");
f.checkNull("FORMAT_DATE('%b-%d-%Y', CAST(NULL AS DATE))");
@@ -13259,7 +13303,7 @@ public class SqlOperatorTest {
"VARCHAR(2000) NOT NULL");
f.checkScalar("FORMAT_TIMESTAMP('The time is: %R.%E2S',"
+ " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00.1235456')",
- "The time is: 15:30.123",
+ "The time is: 15:30.12",
"VARCHAR(2000) NOT NULL");
}
