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

[xinhuitian]

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

• I agree to follow this project's Code of Conduct.

[github-actions]

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

[avamingli]

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]

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]

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.