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 <mbu...@feldera.com> 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 <mbu...@feldera.com> --- .../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))");