TiFlash may throw Cannot convert NULL value to non-Nullable type error when enable tidb_opt_agg_push_down

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `customer` (
  `C_CUSTKEY` bigint(20) NOT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` bigint(20) NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL,
  PRIMARY KEY (`C_CUSTKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `orders` (
  `O_ORDERKEY` bigint(20) NOT NULL,
  `O_CUSTKEY` bigint(20) NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` bigint(20) NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into customer values (6, "a", "a", 2, "12345", 3, "Z", "haha");
alter table customer set tiflash replica 1;
alter table orders set tiflash replica 1;
set tidb_opt_agg_push_down=1;
select c_custkey,count(o_orderkey) as c_count from customer left outer join orders on c_custkey = o_custkey group by c_custkey;

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

query sucess

3. What did you see instead (Required)

query return error

mysql> select c_custkey,count(o_orderkey) as c_count from customer left outer join orders on c_custkey = o_custkey group by c_custkey;
ERROR 1105 (HY000): other error for mpp stream: From MPP<query:456410370056650754,task:4>: Code: 349, e.displayText() = DB::Exception: Cannot convert NULL value to non-Nullable type, e.what() = DB::Exception,

4. What is your TiFlash version? (Required)

v8.5.1

[windtalker]

The root cause is when tidb_opt_agg_push_down is enabled, TiDB add an extra agg(HashAgg_73)

+----------------------------------------------+----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                           | estRows  | task         | access object  | operator info                                                                                                                                         |
+----------------------------------------------+----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_76                               | 1.00     | root         |                | MppVersion: 3, data:ExchangeSender_75                                                                                                                 |
| └─ExchangeSender_75                          | 1.00     | mpp[tiflash] |                | ExchangeType: PassThrough                                                                                                                             |
|   └─Projection_9                             | 1.00     | mpp[tiflash] |                | tpch_1.customer.c_custkey, Column#18                                                                                                                  |
|     └─Projection_74                          | 1.00     | mpp[tiflash] |                | Column#18, tpch_1.customer.c_custkey                                                                                                                  |
|       └─HashAgg_73                           | 1.00     | mpp[tiflash] |                | group by:tpch_1.customer.c_custkey, funcs:sum(Column#19)->Column#18, funcs:firstrow(tpch_1.customer.c_custkey)->tpch_1.customer.c_custkey             |
|         └─Projection_68                      | 1.00     | mpp[tiflash] |                | tpch_1.customer.c_custkey, Column#19                                                                                                                  |
|           └─HashJoin_66                      | 1.00     | mpp[tiflash] |                | left outer join, left side:ExchangeReceiver_19, equal:[eq(tpch_1.customer.c_custkey, tpch_1.orders.o_custkey)], stream_count: 11                      |
|             ├─ExchangeReceiver_19(Build)     | 1.00     | mpp[tiflash] |                | stream_count: 11                                                                                                                                      |
|             │ └─ExchangeSender_18            | 1.00     | mpp[tiflash] |                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch_1.customer.c_custkey, collate: binary], stream_count: 11                       |
|             │   └─TableFullScan_17           | 1.00     | mpp[tiflash] | table:customer | keep order:false                                                                                                                                      |
|             └─Projection_29(Probe)           | 8000.00  | mpp[tiflash] |                | Column#19, tpch_1.orders.o_custkey                                                                                                                    |
|               └─HashAgg_30                   | 8000.00  | mpp[tiflash] |                | group by:tpch_1.orders.o_custkey, funcs:sum(Column#20)->Column#19, funcs:firstrow(tpch_1.orders.o_custkey)->tpch_1.orders.o_custkey, stream_count: 11 |
|                 └─ExchangeReceiver_32        | 8000.00  | mpp[tiflash] |                | stream_count: 11                                                                                                                                      |
|                   └─ExchangeSender_31        | 8000.00  | mpp[tiflash] |                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch_1.orders.o_custkey, collate: binary], stream_count: 11                         |
|                     └─HashAgg_21             | 8000.00  | mpp[tiflash] |                | group by:tpch_1.orders.o_custkey, funcs:count(tpch_1.orders.o_orderkey)->Column#20                                                                    |
|                       └─TableFullScan_28     | 10000.00 | mpp[tiflash] | table:orders   | keep order:false, stats:pseudo                                                                                                                        |
+----------------------------------------------+----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+

in HashAgg_73, count is rewriten to sum(Column#19)->Column#18 since it is the last stage of count, but in TiFlash's context, it does not know sum(Column#19) is actual the final stage of a count function, and sum(null) = null, so it generate a null value for column#18, which is not right.