[CALCITE-6252] BigQuery FORMAT_DATE uses the wrong calendar for Julian dates
Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
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 e8dac9d..1fc6524 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
@@ -1597,7 +1597,7 @@
@LibraryOperator(libraries = {MYSQL, ORACLE, POSTGRESQL})
public static final SqlFunction TO_CHAR =
SqlBasicFunction.create("TO_CHAR",
- ReturnTypes.VARCHAR_2000,
+ ReturnTypes.VARCHAR,
OperandTypes.TIMESTAMP_STRING,
SqlFunctionCategory.TIMEDATE);
@@ -1666,14 +1666,14 @@
* Formats a time object according to the specified string. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction FORMAT_TIME =
- SqlBasicFunction.create("FORMAT_TIME", ReturnTypes.VARCHAR_2000_NULLABLE,
+ SqlBasicFunction.create("FORMAT_TIME", ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.CHARACTER_TIME, SqlFunctionCategory.STRING);
/** The "FORMAT_DATE(string, date)" function (BigQuery);
* Formats a date object according to the specified string. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction FORMAT_DATE =
- SqlBasicFunction.create("FORMAT_DATE", ReturnTypes.VARCHAR_2000_NULLABLE,
+ SqlBasicFunction.create("FORMAT_DATE", ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.CHARACTER_DATE, SqlFunctionCategory.STRING);
/** The "FORMAT_TIMESTAMP(string, timestamp)" function (BigQuery);
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 38625d3..de4b5df 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
@@ -22,6 +22,8 @@
import java.text.DateFormat;
import java.text.SimpleDateFormat;
+import java.time.LocalDate;
+import java.time.format.TextStyle;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
@@ -57,8 +59,17 @@
},
DAY("EEEE", "The full weekday name") {
@Override public void format(StringBuilder sb, Date date) {
- final Work work = Work.get();
- sb.append(work.eeeeFormat.format(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));
}
},
DD("dd", "The day of the month as a decimal number (01-31)") {
@@ -77,8 +88,17 @@
},
DY("EEE", "The abbreviated weekday name") {
@Override public void format(StringBuilder sb, Date date) {
- final Work work = Work.get();
- sb.append(work.eeeFormat.format(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));
}
},
E("d", "The day of the month as a decimal number (1-31); "
@@ -279,11 +299,13 @@
final Calendar calendar =
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(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 mmmFormat = new SimpleDateFormat(MON.javaFmt, Locale.US);
+ /* Need to sse Locale.US instead of Locale.ROOT, because Locale.ROOT
+ * may actually return the *short* month name instead of the long name.
+ * See [CALCITE-6252] BigQuery FORMAT_DATE uses the wrong calendar for Julian dates:
+ * 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);
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 12052c9..11cc788 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4421,31 +4421,40 @@
f.setFor(SqlLibraryOperators.TO_CHAR);
f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'YYYY-MM-DD HH24:MI:SS.MS TZ')",
"2022-06-03 12:15:48.678",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'Day')",
"Friday",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '0001-01-01 00:00:00.000', 'Day')",
+ "Monday",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'DY')",
+ "Fri",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '0001-01-01 00:00:00.000', 'DY')",
+ "Mon",
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'CC')",
"21",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'HH12')",
"01",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'HH24')",
"13",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MI')",
"15",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MS')",
"678",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Q')",
"2",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'IW')",
"23",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkNull("to_char(timestamp '2022-06-03 12:15:48.678', NULL)");
f.checkNull("to_char(cast(NULL as timestamp), NULL)");
f.checkNull("to_char(cast(NULL as timestamp), 'Day')");
@@ -12628,19 +12637,28 @@
false);
f.checkScalar("FORMAT_TIME('%H', TIME '12:34:33')",
"12",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkScalar("FORMAT_TIME('%R', TIME '12:34:33')",
"12:34",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkScalar("FORMAT_TIME('The time is %M-%S', TIME '12:34:33')",
"The time is 34-33",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
}
@Test void testFormatDate() {
final SqlOperatorFixture f = fixture()
.withLibrary(SqlLibrary.BIG_QUERY)
.setFor(SqlLibraryOperators.FORMAT_DATE);
+ // Test case for [CALCITE-6252] https://issues.apache.org/jira/browse/CALCITE-6252
+ // BigQuery FORMAT_DATE uses the wrong calendar for Julian dates
+ f.checkScalar("FORMAT_DATE('%A %a %d %B %Y', '0001-01-01')",
+ "Monday Mon 01 January 1",
+ "VARCHAR NOT NULL");
+ f.checkScalar("FORMAT_DATE('%A %a %d %B %Y', '2024-02-08')",
+ "Thursday Thu 08 February 2024",
+ "VARCHAR NOT NULL");
+
f.checkFails("^FORMAT_DATE('%x', 123)^",
"Cannot apply 'FORMAT_DATE' to arguments of type "
+ "'FORMAT_DATE\\(<CHAR\\(2\\)>, <INTEGER>\\)'\\. "
@@ -12650,24 +12668,24 @@
// Can implicitly cast TIMESTAMP to DATE
f.checkScalar("FORMAT_DATE('%x', timestamp '2008-12-25 15:30:00')",
"12/25/08",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkScalar("FORMAT_DATE('%b-%d-%Y', DATE '2008-12-25')",
"Dec-25-2008",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkScalar("FORMAT_DATE('%b %Y', DATE '2008-12-25')",
"Dec 2008",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
// Test case for [CALCITE-6247] https://issues.apache.org/jira/browse/CALCITE-6247
// BigQuery FORMAT_DATE function handles incorrectly the %e format specifier
f.checkScalar("FORMAT_DATE('*%e*', DATE '2008-12-02')",
"* 2*",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkScalar("FORMAT_DATE('%x', DATE '2008-12-25')",
"12/25/08",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkScalar("FORMAT_DATE('The date is: %x', DATE '2008-12-25')",
"The date is: 12/25/08",
- "VARCHAR(2000) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkNull("FORMAT_DATE('%x', CAST(NULL AS DATE))");
f.checkNull("FORMAT_DATE('%b-%d-%Y', CAST(NULL AS DATE))");
f.checkNull("FORMAT_DATE('%b %Y', CAST(NULL AS DATE))");