[Bug] Arrow Flight SQL: year-zero DATE values are encoded one day late
- Dominant language
- Java
- Stars
- 15.9k
- Forks
- 3.9k
- Avg merge
- 2d 23h
- Merged PRs (30d)
- 520
Description
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) 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` 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.
```sql
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:
```python
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
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
Contributor guide
Research direction
Start by reproducing the SQL queries through the MySQL/JDBC control path and the Python ADBC Flight SQL client, then compare the raw Arrow date32 day value with the returned date. Trace the Arrow Flight SQL DATE encoding entry point, including list arrays. Done means year-zero scalar and array dates match JDBC while modern dates remain unchanged.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, python, sql
- Domain
- api, backend, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100