Hive Partitioning on Date Part breaking.

[Kurorah]

What happens?

When inserting into a table partitioned by a datepart function (ALTER TABLE example SET PARTITIONED BY (YEAR(ts));), nonsensical values are returned and used in the hive partitioning scheme. This has occurred on multiple datasets I have tried.

Example from my local Minio Object Store:

• year=9999
• year=-1
• year=-1005240734263017374
• year=-4521051002081600685
• year=-4521321939762871114
• year=-4528713560160304931
• year=-4528721211045426796
• year=-4534128942037135759
• year=-4539346949412749312
• year=-4543984170262200808
• year=-4548000494483161079
• year=-4550822401377224700
• year=-4557031394559129541
• year=-4561963618355968950
• year=-4565172248142360563
• year=-4580933227769618360

To Reproduce

CREATE TABLE IF NOT EXISTS example AS
    SELECT *
    FROM read_parquet('s3://lake/landing/example_tbl/*.parquet', union_by_name=true)
    WHERE 0=1
ALTER TABLE example SET PARTITIONED BY (YEAR(timestamp));
INSERT INTO example
    SELECT *
    FROM read_parquet('s3://lake/landing/example_tbl/*.parquet', union_by_name=true);

OS:

Windows

DuckDB Version:

1.4.3

DuckLake Version:

0.3

DuckDB Client:

Python

Hardware:

No response

Full Name:

Ryan Black

Affiliation:

none

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

No - I cannot share the data sets because they are confidential

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[pdet]

Hi @Kurorah, thanks for opening an issue.

Can you provide a fully reproducible example? (i.e., with the insert statements)

[Kurorah]

@pdet I updated the SQL to include insert, though what I am using is a parquet file. If needed, I can censor a smaller sample file and provide it.

[pdet]

If you can provide a small sample that reproduces the issue, that's indeed great!

[Kurorah]

example.csv @pdet

[pdet]

Hi @Kurorah

I did not manage to reproduce your issue with the following:

require ducklake

require parquet

test-env DUCKLAKE_CONNECTION __TEST_DIR__/{UUID}.db

test-env DATA_PATH __TEST_DIR__

statement ok
ATTACH 'ducklake:${DUCKLAKE_CONNECTION}' AS ducklake (DATA_PATH 'partition_year', METADATA_CATALOG 'ducklake_metadata')

statement ok
use ducklake

statement ok
CREATE TABLE IF NOT EXISTS example AS
    FROM 'example.csv'
    WHERE 0=1

statement ok
ALTER TABLE example SET PARTITIONED BY (YEAR(MainPartitionedColumn));

statement ok
INSERT INTO example
    FROM 'example.csv'

Am I missing something?

[Kurorah]

@pdet At first glance, the only real difference may be the lake catalog and data path. In my setup I am using postgres (latest docker image) and Minio s3. I haven't tested with local files yet, but with the duckdb catalog I did not have this issue.

[pdet]

@pdet At first glance, the only real difference may be the lake catalog and data path. In my setup I am using postgres (latest docker image) and Minio s3. I haven't tested with local files yet, but with the duckdb catalog I did not have this issue.

Alright, I'll have a go with PG And Minio

[pdet]

Hi @Kurorah
I also can't reproduce it using Postgres and MinIO.

statement ok
CREATE SECRET (TYPE S3, KEY_ID 'admin', SECRET 'password', ENDPOINT '127.0.0.1:9000', URL_STYLE 'path', USE_SSL 0);

statement ok
ATTACH 'ducklake:postgres:dbname=ducklakedb' AS ducklake (DATA_PATH 's3://mybucket/partition_year', METADATA_CATALOG 'ducklake_metadata')

statement ok
use ducklake

statement ok
CREATE TABLE IF NOT EXISTS example AS
    FROM 'example.csv'
    WHERE 0=1

statement ok
ALTER TABLE example SET PARTITIONED BY (YEAR(MainPartitionedColumn));

statement ok
INSERT INTO example
    FROM 'example.csv'

query I
SELECT COUNT(*) FROM 's3://mybucket/partition_year/main/example/year=2025/*.parquet'
----
1000

[duckdblabs-bot]

Thanks for opening this issue in the DuckLake issue tracker! To resolve this issue, our team needs a reproducible example. This includes:

• A source code snippet which reproduces the issue.
• The snippet should be self-contained, i.e., it should contain all imports and should use relative paths instead of hard coded paths (please avoid /Users/JohnDoe/...).
• A lot of issues can be reproduced with plain SQL code executed in the DuckDB command line client. If you can provide such an example, it greatly simplifies the reproduction process and likely results in a faster fix.
• If the script needs additional data, please share the data as a CSV, JSON, or Parquet file. Unfortunately, we cannot fix issues that can only be reproduced with a confidential data set. Support contracts allow sharing confidential data with the core DuckDB team under NDA.

For more detailed guidelines on how to create reproducible examples, please visit Stack Overflow's “Minimal, Reproducible Example” page.