Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Bug] Fail to create table with partition description with MAXVALUE in Greenplum like syntax #795

Closed
2 tasks done
xinhuitian opened this issue Dec 18, 2024 · 6 comments · Fixed by #904
Closed
2 tasks done

[Bug] Fail to create table with partition description with MAXVALUE in Greenplum like syntax #795

xinhuitian opened this issue Dec 18, 2024 · 6 comments · Fixed by #904
Assignees
@avamingli
Labels
type: Bug Something isn't working

Comments

@xinhuitian
Copy link

xinhuitian commented Dec 18, 2024 •
edited
Loading

Apache Cloudberry version

1.6.0

What happened

Hello, I'm currently testing cb with the bootcamp 000-cbdb-sandbox docker file.
When I run a query similar as the below one:

gpadmin=# CREATE TABLE test_partition (
    name character varying,
    last_modified_date timestamp without time zone
)
WITH (appendoptimized=true, orientation=column, compresslevel=1)
DISTRIBUTED BY (name)
PARTITION BY RANGE (last_modified_date)
(
    PARTITION partition_202411 START ('2024-11-01 00:00:00') INCLUSIVE END ('2024-12-01 00:00:00') EXCLUSIVE,
    PARTITION partition_max START ('2024-12-01 00:00:00') INCLUSIVE END (MAXVALUE)
);

I got the following error:

ERROR:  cannot use column reference in partition bound expression
LINE 10: ...n_max START ('2024-12-01 00:00:00') INCLUSIVE END (MAXVALUE)

Split it to another query works fine:

CREATE TABLE partition_max partition of test_partition for values from ('2024-12-01 00:00:00') to (MAXVALUE);

Seems like MAXVALUE in the first query is parsed to a column ref. Seems like a bug here?

What you think should happen instead

Correctly process this kind of query without error, be more compatible with Greenplum syntax

How to reproduce

run such query:

CREATE TABLE test_partition (
    name character varying,
    last_modified_date timestamp without time zone
)
WITH (appendoptimized=true, orientation=column, compresslevel=1)
DISTRIBUTED BY (name)
PARTITION BY RANGE (last_modified_date)
(
    PARTITION partition_202411 START ('2024-11-01 00:00:00') INCLUSIVE END ('2024-12-01 00:00:00') EXCLUSIVE,
    PARTITION partition_max START ('2024-12-01 00:00:00') INCLUSIVE END (MAXVALUE)
);

Operating System

cbdb-1.6.0:rockylinux9 docker image running on Debian GNU/Linux 9 (stretch)

Anything else

Every time

Are you willing to submit PR?

  • Yes, I am willing to submit a PR!

Code of Conduct
@xinhuitian xinhuitian added the type: Bug Something isn't working label Dec 18, 2024
@github-actions GitHub Actions
Copy link

Hi, @xinhuitian welcome!🎊 Thanks for taking the time to point this out.🙌

@avamingli
Copy link
Contributor

Reproduce it on main branch, besides the MAXVALUE, the MINVALUE used in sub partition also causes an error:

CREATE TABLE test_partition2 (
    name character varying,
    last_modified_date timestamp without time zone
)
WITH (appendoptimized=true, orientation=column, compresslevel=1)
DISTRIBUTED BY (name)
PARTITION BY RANGE (last_modified_date)
(
    PARTITION partition_202411 START (MINVALUE) INCLUSIVE END ('2024-12-01 00:00:00') EXCLUSIVE,
    PARTITION partition_max START ('2024-12-01 00:00:00') INCLUSIVE END (MAXVALUE)
);
ERROR:  cannot use column reference in partition bound expression
LINE 9:     PARTITION partition_202411 START (MINVALUE) INCLUSIVE EN..

@gfphoenix78
Copy link
Contributor

The value of right boundary is always treat as exclusive. What's the exact meaning of less than maxvalue? If we want to set a partition for the rest values, default partition could be considered.

@avamingli
Copy link
Contributor

avamingli commented Dec 25, 2024 •
edited
Loading

The value of right boundary is always treat as exclusive. What's the exact meaning of less than maxvalue?

Good point.
INCLUSIVE is introduced by Greenplum, INCLUSIVE END (MAXVALUE) should be reported with some WARNING or error.

@avamingli avamingli self-assigned this Feb 5, 2025
@avamingli
Copy link
Contributor

The value of right boundary is always treat as exclusive. What's the exact meaning of less than maxvalue? If we want to set a partition for the rest values, default partition could be considered.

Oh..I just found that the case:

PARTITION partition_max START ('2024-12-01 00:00:00') INCLUSIVE END (MAXVALUE)

INCLUSIVE clause is for START ('2024-12-01 00:00:00'), not for END (MAXVALUE).

But your concern should be considered if it was.

avamingli added a commit to avamingli/cloudberrydb that referenced this issue Feb 6, 2025
@avamingli
Fix partition creation to handle MAXVALUE in subpartition bounds
95cbbf8 
Previously, the simplified partition creation syntax in GPDB did not
properly handle MAXVALUE as a boundary value for subpartitions.

The fix allows users to define subpartitions with MAXVALUE boundaries
using the simplified partition creation syntax, maintaining
compatibility with existing partition functionality.

During the fix, it was also identified that MINVALUE has a similar
issue.
However, addressing MINVALUE is more complex due to the current
implementation of the START/EVERY/END grammar, which only considers
the end boundary situation.
A broader refactor of the partition creation logic may be required to
properly support MINVALUE in subpartition definitions.

Fixes: apache#795

Authored-by: Zhang Mingli [email protected]
@avamingli avamingli mentioned this issue Feb 6, 2025
Merged
12 tasks
@avamingli
Copy link
Contributor

Reproduce it on main branch, besides the MAXVALUE, the MINVALUE used in sub partition also causes an error:

CREATE TABLE test_partition2 (
name character varying,
last_modified_date timestamp without time zone
)
WITH (appendoptimized=true, orientation=column, compresslevel=1)
DISTRIBUTED BY (name)
PARTITION BY RANGE (last_modified_date)
(
PARTITION partition_202411 START (MINVALUE) INCLUSIVE END ('2024-12-01 00:00:00') EXCLUSIVE,
PARTITION partition_max START ('2024-12-01 00:00:00') INCLUSIVE END (MAXVALUE)
);
ERROR: cannot use column reference in partition bound expression
LINE 9: PARTITION partition_202411 START (MINVALUE) INCLUSIVE EN..

Hi, all

I have a fix for the MAXVALUE in #904.

As said there:
However, addressing MINVALUE is more complex due to the current implementation of the START/EVERY/END grammar, which only considers the end boundary situation.
A broader refactor of the partition creation logic may be required to properly support MINVALUE in subpartition definitions.

I think we first fix this one, then create a new one to track MINVALUE.

my-ship-it pushed a commit that referenced this issue Feb 8, 2025
@avamingli @my-ship-it
Fix partition creation to handle MAXVALUE in subpartition bounds
e9bcc8d 
Previously, the simplified partition creation syntax in GPDB did not
properly handle MAXVALUE as a boundary value for subpartitions.

The fix allows users to define subpartitions with MAXVALUE boundaries
using the simplified partition creation syntax, maintaining
compatibility with existing partition functionality.

During the fix, it was also identified that MINVALUE has a similar
issue.
However, addressing MINVALUE is more complex due to the current
implementation of the START/EVERY/END grammar, which only considers
the end boundary situation.
A broader refactor of the partition creation logic may be required to
properly support MINVALUE in subpartition definitions.

Fixes: #795

Authored-by: Zhang Mingli [email protected]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@avamingli avamingli

Labels
type: Bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix partition creation to handle MAXVALUE in subpartition bounds avamingli/cloudberrydb
3 participants
@xinhuitian @avamingli @gfphoenix78