apache / apache/doris

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

Open
#67,366 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.