select count(1) with group by statement return one row when there is actual no data

[peacefulprogram]

Which version of ShardingSphere did you use?

5.5.0

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

both ShardingSphere-JDBC and ShardingSphere-Proxy

Expected behavior

The following sql should return no data.

select count(1)
from t_user
where 1 = 2
group by id,username
order by id;

Actual behavior

The sql returns one row, the count is zero.

Reason analyze (If you can)

When order by fields is differenet from group by fields, GroupByMemoryMergedResult will be used.
In GroupByMemoryMergedResult, if there is no data, it will try to generate one row. when select fields have count(1), it will renturn 0.

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

• Environment
  mysql: 5.7.4
  shardingsphere: 5.5.0

• Create table

create table t_user_0(
    id int primary key auto_increment,
    username varchar(30)
);
create table t_user_1(
    id int primary key auto_increment,
    username varchar(30)
);
create table t_user_2(
    id int primary key auto_increment,
    username varchar(30)
);

• ShardingSphere Jdbc config file. ShardingSphere Proxy config file content is similar with it.

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: sharding_ds
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60

props:
  sql-show: false

dataSources:
  ds_0:
    url: jdbc:mysql://xxx
    username: xxx
    password: xxx
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 500
    minPoolSize: 1
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource


rules:
  - !SHARDING
    autoTables:
      t_user:
        actualDataSources: ds_0
        shardingStrategy:
          standard:
            shardingColumn: username
            shardingAlgorithmName: table_hash_mod

    shardingAlgorithms:
      table_hash_mod:
        type: HASH_MOD
        props:
          sharding-count:  3

• This is the two test sql

# Wrong result, returns one row.
select count(1)
from t_user
where 1 = 2
group by id,username
order by id;

# Return no data as expect.
select count(1)
from t_user
where 1 = 2
group by id,username
order by id,username;

Related #33209

[Malaydewangan09]

Hey @peacefulprogram
Is this still open?
can I work on this?

[peacefulprogram]

Hey @peacefulprogram

Is this still open?

can I work on this?

Yes.It's still open.

[strongduanmu]

@Malaydewangan09 Assigned, please go ahead.

[Malaydewangan09]

@strongduanmu Thanks!