[Bug] Arrow Flight SQL: year-zero DATE values are encoded one day late

[morningman]

Search before asking

• I had searched in the issues and found no similar issues.

Version

Apache Doris 4.1.3-rc02, commit 31263df4dc1d4d3a27517d264802cd4d6b92c874

Client: Python + ADBC Flight SQL driver (adbc_driver_flightsql), FE arrow_flight_sql_port = 41070.
The MySQL/JDBC protocol is used as the control path for comparison.

What's Wrong?

DATE values in year zero are shifted by one day when read over Arrow Flight SQL. 0000-01-01 (as returned by JDBC) is rendered as 0000-01-02 in Arrow, and 0000-02-28 is rendered as 0000-02-29. Modern dates are not affected.

The Flight SQL schema is date32[day] (and list<date32[day]> for arrays). The raw day value sent for 0000-01-01 is -719527, which PyArrow renders as 0000-01-02. 2024-01-01 matches JDBC exactly.

What You Expected?

ADBC/Arrow and JDBC should return the same DATE value. 0000-01-01 and 0000-02-28 should not be shifted by the calendar conversion.

How to Reproduce?

1. Run the query over MySQL/JDBC and record the result.
2. Run the same query over the Python ADBC Flight SQL driver.
3. Compare the year-zero dates with the modern date.

SELECT
  CAST('0000-01-01' AS DATE) AS d1,
  CAST('0000-02-28' AS DATE) AS d2,
  CAST('2024-01-01' AS DATE) AS modern_date;

SELECT [CAST('0000-01-01' AS DATE)] AS date_array;

Client side:

import adbc_driver_flightsql.dbapi as flight_sql

conn = flight_sql.connect(uri="grpc://127.0.0.1:41070",
                          db_kwargs={"username": "root", "password": ""})
cur = conn.cursor()
cur.execute("SELECT CAST('0000-01-01' AS DATE), CAST('0000-02-28' AS DATE), CAST('2024-01-01' AS DATE)")
print(cur.fetch_arrow_table().to_pylist())

Anything Else?

The off-by-one looks like a proleptic-Gregorian vs. Julian calendar mismatch in the day-number computation used to build the Arrow date32 value; Arrow date32 is defined as days since the epoch in the proleptic Gregorian calendar.

Workaround: CAST the column to STRING in Doris and parse it on the client. This bypasses the Arrow DATE encoding but loses the native date type.

Verified by comparing the raw Arrow day values against the JDBC results directly.

Tracking issue: #65615

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Doris-Breakwater]

Initial assessment: confirmed server-side BE serialization bug at commit 31263df4dc1d4d3a27517d264802cd4d6b92c874. The raw value reported by the client is exactly what the Doris code produces, so additional logs or a Doris profile are not required to establish the initial root cause.

Verified facts

• Doris maps DATEV2 to Arrow date32 in convert_to_arrow_type, and the Flight result converter delegates the value encoding to the type SerDe (call site).
• DataTypeDateV2SerDe::write_column_to_arrow sends DateV2Value::daynr() - 719528 directly to Date32Builder.
• daynr() uses Doris's MySQL-compatible calc_daynr. That internal numbering is not identical to Arrow's proleptic-Gregorian day ordinal around the start of year zero. Doris also deliberately treats year zero as non-leap in is_leap, while proleptic Gregorian year zero is divisible by 400.

Doris value	Doris daynr()	Current date32	Proleptic-Gregorian date32
0000-01-01	1	-719527	-719528
0000-02-28	59	-719469	-719470
0000-03-01	60	-719468	-719468
2024-01-01	739251	19723	19723

This confirms the reported values and narrows the affected range to valid Doris dates from 0000-01-01 through 0000-02-28; this is not a blanket shift for every year-zero date. The issue's calendar-mismatch hypothesis is directionally correct, but the precise problem is reusing the Doris/MySQL internal ordinal without an explicit calendar-boundary conversion.

ARRAY<DATE> is affected through the same code path: DataTypeArraySerDe delegates each element to the nested DATE SerDe. There is no separate array root cause.

Important reverse-path consideration

The fix should cover Arrow-to-Doris conversion as well as Doris-to-Arrow conversion. The current DATE32 reader adds 719528 and calls get_date_from_daynr (code). A correct Arrow value of -719528 for 0000-01-01 becomes internal day number 0, which that API rejects; -719470 for 0000-02-28 becomes 58 and decodes one day early. Fixing only the Flight output expression would therefore leave asymmetric round trips.

Missing information

Nothing is blocking initial confirmation. For a complete compatibility record, the exact adbc_driver_flightsql and PyArrow versions would still be useful. It would also be helpful to record raw values for 0000-01-02, 0000-03-01, and 0001-01-01, but the checked source already predicts that only the pre-March year-zero boundary needs adjustment.

Recommended next steps

1. Add a dedicated conversion helper between Doris DATE values and Arrow/Parquet proleptic-Gregorian day ordinals. Avoid changing shared calc_daynr, changing the epoch constant globally, or applying an unconditional -1: those approaches would break 0000-03-01 and/or the many SQL date operations that rely on Doris's existing numbering.
2. Add focused BE unit tests for both write_column_to_arrow and read_column_from_arrow, covering 0000-01-01, 0000-02-28, 0000-03-01, 0001-01-01, 1969-12-31, 1970-01-01, and a modern date. Include scalar, nullable, and nested array cases.
3. Add an end-to-end Flight SQL regression comparing native date32 results with the MySQL/JDBC control path.
4. Audit the other users of the same ordinal assumption. In particular, the ORC writer also emits daynr() - 719528, and Parquet/Arrow input paths add the same threshold before calling get_date_from_daynr. This is code evidence of potential shared boundary behavior, but those formats have not been reproduced end to end here.
5. Land the primary fix and then evaluate a branch-4.1 backport because the supplied reproducer is from 4.1.3-rc02.

The current CAST(... AS STRING) workaround is valid until a native-type fix is available. The issue currently has no labels; kind/bug and area/rpc would be appropriate if those labels remain the repository convention.

Breakwater-GitHub-Analysis-Slot: slot_eb5d685887c8