Incorrect result for PostgreSQL window aggregate max(...) OVER (...) on empty sharded table: returns one NULL row instead of zero rows

[duerwuyi]

Bug Report

Which version of ShardingSphere did you use?

5.5.3

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

For an empty sharding table, the following query should return 0 rows:

select
  pg_catalog.max(ref_0.c36) over (partition by ref_0.c39 order by ref_0.vkey desc) as c_5
from
  t24 as ref_0;

Actual behavior

ShardingSphere returns 1 row with NULL.

This is inconsistent with PostgreSQL semantics, where the same query on an empty table returns 0 rows.

Reason analyze (If you can)

This looks like a result-merging bug in ShardingSphere for window-function queries on sharding tables.

A window function such as:

max(c36) over (partition by c39 order by vkey desc)

does not change the cardinality of the input relation by itself.
It should compute one output value for each input row. Therefore, if the input is empty, the output must also be empty.

However, ShardingSphere seems to generate a synthetic aggregated NULL row during merge, as if this were a plain aggregate query like:

select max(c36) from t24;

That behavior is incorrect for window functions.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

docker compose up -d in this folder:
ss_postgres_553.zip

schema:

CREATE SHARDING TABLE RULE t24 (
STORAGE_UNITS(ds_0,ds_1,ds_2,ds_3,ds_4),
SHARDING_COLUMN=colocated_key,TYPE(NAME="HASH_MOD",PROPERTIES(
"sharding-count"="8"))
);
DROP TABLE IF EXISTS t24;
create table t24 ( 
vkey int4 ,
pkey int4 ,
c36 int4 ,
c37 int4 ,
c38 text ,
c39 text ,
colocated_key text 
);

[ym0506]

I did a quick code-path check and this looks like a merge-layer issue.

max(...) OVER (...) is parsed as an AggregationProjectionSegment with a window, but ProjectionsContext#getAggregationProjections() does not distinguish windowed aggregates from regular aggregates. Then ShardingDQLResultMerger#isNeedProcessGroupBy() treats the query as an aggregation query and uses GroupByMemoryMergedResult.

For an empty result, GroupByMemoryMergedResult#getMemoryResultSetRows() correctly creates one synthetic row for a regular aggregate without GROUP BY, e.g. select max(c36) from t24. But that fallback is incorrect for a window aggregate, because window functions should preserve input cardinality. Empty input should therefore return 0 rows.

A possible fix is to exclude aggregate projections with AggregationProjectionSegment#getWindow().isPresent() from the normal aggregate merge / empty aggregate-row fallback path. This would address this specific cardinality bug; the broader global window-function semantics across shards can remain a separate concern.

I'll send a PR with a regression test for this PostgreSQL empty sharded table case.