cockroachdb / cockroachdb/cockroach

sql: EXPORT INTO PARQUET writes spec-noncompliant decimals and non-round-trippable dates

Open
#172,339 2 comments 0 reactions 1 assignee Claimed by @jeffswenson View on GitHub
A-cdc C-bug O-agent O-qa T-cdc
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

Two problems with `EXPORT INTO PARQUET` output, found while QA-ing the IMPORT side:

**1. DECIMAL columns are written as decimal *text* inside a BYTE_ARRAY annotated `decimal128` — violating the parquet spec and unreadable by pyarrow and DuckDB.** The spec requires BYTE_ARRAY-backed decimals to contain the big-endian two's-complement *unscaled binary* value. Readers fail with:

- pyarrow: `Invalid BYTE_ARRAY length for decimal128`
- DuckDB: `Invalid decimal encoding in Parquet file`

CRDB's own importer happens to parse the text, so CRDB→CRDB round trips work — but the exported files are broken for the rest of the ecosystem.

**2. DATE columns are written as plain strings, which CRDB's own importer then rejects** (`byte array type cannot be converted to DateFamily`) — IMPORT has string→TIMESTAMP/INTERVAL/JSON/DECIMAL parse paths but no string→DATE path, and parquet has a native DATE logical type (INT32) that EXPORT could use instead. So EXPORT→IMPORT round trips fail for any table with a DATE column.

Related: TIMESTAMPTZ is also written as a string, which currently *corrupts* on re-import due to the IMPORT-side bug #172328.

**To Reproduce**

```sql
CREATE TABLE t (i INT PRIMARY KEY, d DECIMAL(10,2), dt DATE);
INSERT INTO t VALUES (1, 123.45, '2024-06-15');
EXPORT INTO PARQUET 'nodelocal://1/out/' FROM TABLE t;
```

```python
import pyarrow.parquet as pq
pq.read_table("") # ArrowInvalid: Invalid BYTE_ARRAY length for decimal128
```

```sql
CREATE TABLE t2 (i INT PRIMARY KEY, d DECIMAL(10,2), dt DATE);
IMPORT INTO t2 PARQUET DATA ('nodelocal://1/out/*.parquet');
-- ERROR: column "dt": byte array type cannot be converted to DateFamily
```

**Expected behavior**

- Decimals encoded per spec (unscaled big-endian binary), or at least as an unannotated string column rather than a false `decimal128` annotation.
- DATE written as the parquet DATE logical type (INT32).
- Net goal: `EXPORT INTO PARQUET` output readable by pyarrow/DuckDB/Spark and re-importable by CRDB for every supported column type.

**Environment:**

- Current master (26.3 dev); EXPORT parquet encoding long predates 26.3.

**Additional context**

Full round-trip QA result: EXPORT→IMPORT currently fails or corrupts for DATE, TIMESTAMPTZ (#172328), and arrays of those types; everything else round-trips exactly.

Jira issue: CRDB-65566

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.