MaterializedPostgreSQL: order of columns in the primary key is not honoured on the Clickhouse Side

Company or project name

Tradablebits Media Inc. We use clickhouse database in conjuction with Postgresql database and MaterializedPostgresql engine (yes, I'm aware that feature is experimental).

Describe what's wrong

When table is attached to MaterializedPostgresql engine it takes order by from primary key of the underlying table, however the order of the columns in the primary key is not honoured.

Does it reproduce on the most recent release?

Yes

How to reproduce

Let's review these two Postgresql tables: create table test1(id1 int,id2 int, value varchar(255)); alter table test1 add primary key(id1,id2);

create table test2(id2 int,id1 int, value varchar(255)); alter table test2 add primary key(id1,id2);

Notice - both tables use identical primary key, which will be used as replica identity by default.

-- Add both into MaterializedPostgresql: attach table test1; attach table test2;

As per created tables:

test2:

ATTACH TABLE _ UUID 'ce79dd2c-4253-479d-bbfe-829540285cb8' ( id2 Int32, id1 Int32, value Nullable(String), _sign Int8 MATERIALIZED 1, _version UInt64 MATERIALIZED 1 ) ENGINE = ReplacingMergeTree(_version) ORDER BY (id2, id1) SETTINGS index_granularity = 8192

test1: ATTACH TABLE _ UUID '95bd524a-8cd0-4414-8a2b-d1e4775b3923' ( id1 Int32, id2 Int32, value Nullable(String), _sign Int8 MATERIALIZED 1, _version UInt64 MATERIALIZED 1 ) ENGINE = ReplacingMergeTree(_version) ORDER BY (id1, id2)

Expected behavior

Even though both tables have identical primary key, the order of "key" on the Clickhouse side varies and follows the original order of columns in the created table, which is not intiutive.

Expected behaviour order by in clickhouse to follow the order of primary key / replica identity and not the column order in the hash table.

Error message and/or stacktrace

No error.

Additional context

No response