Encounter Error 1242: Subquery returns more than 1 row when running TPCH Q11 with tidb_enforce_mpp=1

[Yui-Song]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Deploy TiDB cluster with nightly version
2. restore tpch 100g data
3. alter tables set tiflash raplica 4
4. set parameters

set @@tidb_enforce_mpp = 1;
set @@tidb_isolation_read_engines='tiflash';
set @@tidb_allow_mpp=1;
set @@tidb_mem_quota_query = 10737418240;

5. connect to tidb and run Q11

explain analyze select
	ps_partkey,
	sum(ps_supplycost * ps_availqty) as value
from
	partsupp,
	supplier,
	nation
where
	ps_suppkey = s_suppkey
	and s_nationkey = n_nationkey
	and n_name = 'MOZAMBIQUE'
group by
	ps_partkey having
		sum(ps_supplycost * ps_availqty) > (
			select
				sum(ps_supplycost * ps_availqty) * 0.0001000000
			from
				partsupp,
				supplier,
				nation
			where
				ps_suppkey = s_suppkey
				and s_nationkey = n_nationkey
				and n_name = 'MOZAMBIQUE'
		)
order by
	value desc;

2. What did you expect to see? (Required)

The query is executed without error.

3. What did you see instead (Required)

[centos@ip-172-31-23-214 aws-test]$ mysql -h 127.0.0.1 -P 3390 -u root -D tpch
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.25-TiDB-v5.5.0-alpha TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [tpch]> set @@tidb_enforce_mpp = 1;
Query OK, 0 rows affected (0.00 sec)

MySQL [tpch]> set @@tidb_isolation_read_engines='tiflash';
Query OK, 0 rows affected (0.00 sec)

MySQL [tpch]> set @@tidb_allow_mpp=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [tpch]> set @@tidb_mem_quota_query = 10737418240;
Query OK, 0 rows affected (0.00 sec)

MySQL [tpch]> explain analyze select
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'MOZAMBIQUE'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0001000000
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'MOZAMBIQUE'
    ->         )
    -> order by
    ->     value desc;
ERROR 1242 (21000): Subquery returns more than 1 row

4. What is your TiDB version? (Required)

| Release Version: v5.5.0-alpha
Edition: Community
Git Commit Hash: 171a35486036e8ea5b168c2d4d29f4639df1611d
Git Branch: heads/refs/tags/v5.5.0-alpha
UTC Build Time: 2022-02-20 14:56:04
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

topo file

server_configs:
  tidb:
    new_collations_enabled_on_first_bootstrap: true
    performance.tcp-keep-alive: true
    prepared-plan-cache.enabled: true
  tiflash:
    delta_index_cache_size: 3221225472
    dt_segment_force_merge_delta_rows: 800000000
    dt_segment_stop_write_delta_rows: 4000000000
    mark_cache_size: 2147483648
    minmax_index_cache_size: 2147483648
    profiles.profiles.default.max_memory_usage: 137438953472
    profiles.profiles.default.max_memory_usage_for_all_queries: 137438953472
  tikv:
    raftstore.apply-pool-size: 2
    raftstore.store-pool-size: 2
    rocksdb.defaultcf.max-write-buffer-number: 10
    rocksdb.lockcf.max-write-buffer-number: 10
    rocksdb.writecf.max-write-buffer-number: 10
    server.grpc-concurrency: 3
    server.max-grpc-send-msg-len: 100000000
    storage.io-rate-limit.max-bytes-per-sec: 400MB
tidb_servers:
- host: 172.31.16.18
- host: 172.31.45.225
tiflash_servers:
- config:
    storage.io_rate_limit.max_bytes_per_sec: 625MB
    storage.main.dir:
    - /aws-test/data/tiflash
  host: 172.31.31.73
- config:
    storage.io_rate_limit.max_bytes_per_sec: 625MB
    storage.main.dir:
    - /aws-test/data/tiflash
  host: 172.31.35.128
- config:
    storage.io_rate_limit.max_bytes_per_sec: 625MB
    storage.main.dir:
    - /aws-test/data/tiflash
  host: 172.31.10.122
- config:
    storage.io_rate_limit.max_bytes_per_sec: 625MB
    storage.main.dir:
    - /aws-test/data/tiflash
  host: 172.31.54.53

[LittleFall]

break on pull #32336, only affects master

[LittleFall]

smaller reproduce:

set tidb_enforce_mpp=1;  select sum(ps_supplycost) from partsupp, supplier where ps_suppkey = s_suppkey;

expect

Query OK, 0 rows affected (0.00 sec)

+--------------------+
| sum(ps_supplycost) |
+--------------------+
|       400420638.54 |
+--------------------+
1 row in set (0.28 sec)

mysql> explain analyze select sum(ps_supplycost) from partsupp, supplier where ps_suppkey = s_suppkey;
+--------------------------------------------+-----------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+------+
| id                                         | estRows   | actRows | task              | access object  | execution info                                                                                                                                                                                                                                                                                                                                                       | operator info                                                             | memory  | disk |
+--------------------------------------------+-----------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+------+
| HashAgg_44                                 | 1.00      | 1       | root              |                | time:101.5ms, loops:2, partial_worker:{wall_time:101.491405ms, concurrency:5, task_num:1, tot_wait:507.200573ms, tot_exec:6.39µs, tot_time:507.217008ms, max:101.463196ms, p95:101.463196ms}, final_worker:{wall_time:101.50267ms, concurrency:5, task_num:1, tot_wait:507.241714ms, tot_exec:21.792µs, tot_time:507.294917ms, max:101.478277ms, p95:101.478277ms}   | funcs:sum(Column#15)->Column#14                                           | 18.0 KB | N/A  |
| └─TableReader_46                           | 1.00      | 2       | root              |                | time:101.4ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                            | data:ExchangeSender_45                                                    | N/A     | N/A  |
|   └─ExchangeSender_45                      | 1.00      | 2       | batchCop[tiflash] |                | tiflash_task:{proc max:54ms, min:34.4ms, p80:54ms, p95:54ms, iters:2, tasks:2, threads:40}                                                                                                                                                                                                                                                                           | ExchangeType: PassThrough                                                 | N/A     | N/A  |
|     └─HashAgg_12                           | 1.00      | 2       | batchCop[tiflash] |                | tiflash_task:{proc max:53ms, min:33.4ms, p80:53ms, p95:53ms, iters:2, tasks:2, threads:2}                                                                                                                                                                                                                                                                            | funcs:sum(test.partsupp.ps_supplycost)->Column#15                         | N/A     | N/A  |
|       └─Projection_42                      | 800000.00 | 800000  | batchCop[tiflash] |                | tiflash_task:{proc max:53ms, min:33.4ms, p80:53ms, p95:53ms, iters:13, tasks:2, threads:40}                                                                                                                                                                                                                                                                          | test.partsupp.ps_supplycost                                               | N/A     | N/A  |
|         └─HashJoin_43                      | 800000.00 | 800000  | batchCop[tiflash] |                | tiflash_task:{proc max:53ms, min:33.4ms, p80:53ms, p95:53ms, iters:13, tasks:2, threads:40}                                                                                                                                                                                                                                                                          | inner join, equal:[eq(test.supplier.s_suppkey, test.partsupp.ps_suppkey)] | N/A     | N/A  |
|           ├─ExchangeReceiver_29(Build)     | 10000.00  | 20000   | batchCop[tiflash] |                | tiflash_task:{proc max:22ms, min:12.4ms, p80:22ms, p95:22ms, iters:2, tasks:2, threads:40}                                                                                                                                                                                                                                                                           |                                                                           | N/A     | N/A  |
|           │ └─ExchangeSender_28            | 10000.00  | 10000   | batchCop[tiflash] |                | tiflash_task:{proc max:3.53ms, min:0s, p80:3.53ms, p95:3.53ms, iters:1, tasks:2, threads:1}                                                                                                                                                                                                                                                                          | ExchangeType: Broadcast                                                   | N/A     | N/A  |
|           │   └─TableFullScan_27           | 10000.00  | 10000   | batchCop[tiflash] | table:supplier | tiflash_task:{proc max:3.53ms, min:0s, p80:3.53ms, p95:3.53ms, iters:1, tasks:2, threads:1}                                                                                                                                                                                                                                                                          | keep order:false                                                          | N/A     | N/A  |
|           └─TableFullScan_30(Probe)        | 800000.00 | 800000  | batchCop[tiflash] | table:partsupp | tiflash_task:{proc max:32ms, min:28.4ms, p80:32ms, p95:32ms, iters:13, tasks:2, threads:2}                                                                                                                                                                                                                                                                           | keep order:false                                                          | N/A     | N/A  |
+--------------------------------------------+-----------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+------+
10 rows in set (0.26 sec)

got:

Query OK, 0 rows affected (0.04 sec)

+--------------------+
| sum(ps_supplycost) |
+--------------------+
|       240823020.85 |
|       159597617.69 |
+--------------------+
2 rows in set (0.32 sec)

mysql> explain analyze select sum(ps_supplycost) from partsupp, supplier where ps_suppkey = s_suppkey;
+------------------------------------------+-----------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+--------+------+
| id                                       | estRows   | actRows | task              | access object  | execution info                                                                                           | operator info                                                             | memory | disk |
+------------------------------------------+-----------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+--------+------+
| TableReader_75                           | 1.00      | 2       | root              |                | time:94.2ms, loops:2, cop_task: {num: 3, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_74                                                    | N/A    | N/A  |
| └─ExchangeSender_74                      | 1.00      | 2       | batchCop[tiflash] |                | tiflash_task:{proc max:46.9ms, min:31.9ms, p80:46.9ms, p95:46.9ms, iters:2, tasks:2, threads:40}         | ExchangeType: PassThrough                                                 | N/A    | N/A  |
|   └─StreamAgg_15                         | 1.00      | 2       | batchCop[tiflash] |                | tiflash_task:{proc max:46.9ms, min:31.9ms, p80:46.9ms, p95:46.9ms, iters:2, tasks:2, threads:2}          | funcs:sum(test.partsupp.ps_supplycost)->Column#14                         | N/A    | N/A  |
|     └─Projection_73                      | 800000.00 | 800000  | batchCop[tiflash] |                | tiflash_task:{proc max:46.9ms, min:31.9ms, p80:46.9ms, p95:46.9ms, iters:13, tasks:2, threads:40}        | test.partsupp.ps_supplycost                                               | N/A    | N/A  |
|       └─HashJoin_69                      | 800000.00 | 800000  | batchCop[tiflash] |                | tiflash_task:{proc max:46.9ms, min:31.9ms, p80:46.9ms, p95:46.9ms, iters:13, tasks:2, threads:40}        | inner join, equal:[eq(test.supplier.s_suppkey, test.partsupp.ps_suppkey)] | N/A    | N/A  |
|         ├─ExchangeReceiver_32(Build)     | 10000.00  | 20000   | batchCop[tiflash] |                | tiflash_task:{proc max:21.9ms, min:13.9ms, p80:21.9ms, p95:21.9ms, iters:2, tasks:2, threads:40}         |                                                                           | N/A    | N/A  |
|         │ └─ExchangeSender_31            | 10000.00  | 10000   | batchCop[tiflash] |                | tiflash_task:{proc max:4.55ms, min:0s, p80:4.55ms, p95:4.55ms, iters:1, tasks:2, threads:1}              | ExchangeType: Broadcast                                                   | N/A    | N/A  |
|         │   └─TableFullScan_30           | 10000.00  | 10000   | batchCop[tiflash] | table:supplier | tiflash_task:{proc max:4.55ms, min:0s, p80:4.55ms, p95:4.55ms, iters:1, tasks:2, threads:1}              | keep order:false                                                          | N/A    | N/A  |
|         └─TableFullScan_33(Probe)        | 800000.00 | 800000  | batchCop[tiflash] | table:partsupp | tiflash_task:{proc max:37.9ms, min:24.9ms, p80:37.9ms, p95:37.9ms, iters:13, tasks:2, threads:2}         | keep order:false                                                          | N/A    | N/A  |
+------------------------------------------+-----------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+--------+------+
9 rows in set (0.29 sec)