This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 3bce23210b [CALCITE-6252] BigQuery FORMAT_DATE uses the wrong calendar
for Julian dates
3bce23210b is described below
commit 3bce23210b0149857ddab80200090735291a6a5d
Author: Mihai Budiu <[email protected]>
AuthorDate: Fri Feb 23 07:37:25 2024 -0800
[CALCITE-6252] BigQuery FORMAT_DATE uses the wrong calendar for Julian dates
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/sql/fun/SqlLibraryOperators.java | 6 +--
.../calcite/util/format/FormatElementEnum.java | 40 ++++++++++++----
.../org/apache/calcite/test/SqlOperatorTest.java | 54 ++++++++++++++--------
3 files changed, 70 insertions(+), 30 deletions(-)
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 e8dac9d7a9..1fc6524a3f 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 @@ public abstract class SqlLibraryOperators {
@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 @@ public abstract class SqlLibraryOperators {
* 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 38625d3308..de4b5df03b 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 org.checkerframework.checker.nullness.qual.Nullable;
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 @@ public enum FormatElementEnum implements FormatElement {
},
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 @@ public enum FormatElementEnum implements FormatElement {
},
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 @@ public enum FormatElementEnum implements FormatElement {
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 12052c9a82..11cc788019 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 @@ public class SqlOperatorTest {
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 @@ public class SqlOperatorTest {
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 @@ public class SqlOperatorTest {
// 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))");
