Bug Report: query result based on cross shard aggregation is bad

[eseokoh]

Overview of the Issue

Sometimes query based on a cross shard aggregation shows a bad result, where not all shards are hit.

Reproduction Steps

0. Prepare for a keyspace with 2 shards

1. Deploy the following vschema:

alter vschema on aggt0 add vindex xxhash (shardkey) using xxhash;

2. Deploy the following schema:

 create table aggt0 (
 shardkey bigint primary key,
 a int,
 b int
);

insert into aggt0 (shardkey, a, b) values (0, 100, 10), (10, 200, 20);

Please notice that shardkey of 0 goes to 80- shard and that of 10 does to -80.

3. Run Query

mysql> select A.a, A.b, (A.a / A.b) as d from (select sum(a) as a, sum(b) as b from aggt0) A;
+------+------+---------+
| a    | b    | d       |
+------+------+---------+
|  300 |   30 | 10.0000 |
+------+------+---------+
1 row in set (0.13 sec)

This is absolutely good.

However, when the subquery hit not both but one shard, it may show a bad result.
Please repeat the query until you have the bad result.

• the expected result

mysql> select A.a, A.b, (A.a / A.b) as d from (select sum(a) as a, sum(b) as b from aggt0 where a = 100) A;
+------+------+---------+
| a    | b    | d       |
+------+------+---------+
|  100 |   10 | 10.0000 |
+------+------+---------+
1 row in set (0.00 sec)

• the unexpected result

mysql> select A.a, A.b, (A.a / A.b) as d from (select sum(a) as a, sum(b) as b from aggt0 where a = 100) A;
+------+------+------+
| a    | b    | d    |
+------+------+------+
|  100 |   10 | NULL |
+------+------+------+
1 row in set (0.01 sec)

Binary Version

14.0.4

Operating System and Environment details

unrelated

Log Fragments

N/A

[frouioui]

Hey @eseokoh, thank you for reporting this issue. I opened #12607 to fix it.