pingcap / pingcap/tidb

Repeat calculation when aggregate function parameters are the same

Open
#39,576 1 comment 0 reactions 0 assignees View on GitHub
sig/planner type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**

Suppose I have a table like this:

```SQL
mysql> SHOW CREATE TABLE `test`.`tbl`;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl | CREATE TABLE `tbl` (
`s_i_id` int(11) NOT NULL,
`s_w_id` int(11) DEFAULT NULL,
PRIMARY KEY (`s_i_id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

Then calculate a value for each line according to the specified function:
```SQL
mysql> SELECT CAST(CRC32(CONCAT_WS(',', `s_i_id`, `s_w_id`, CONCAT(ISNULL(`s_i_id`), ISNULL(`s_w_id`))))AS UNSIGNED) as CRC32RES FROM `test`.`tbl`;
+------------+
| CRC32RES |
+------------+
| 3896458847 |
| 3591842708 |
| 2157756552 |
+------------+
3 rows in set (0.01 sec)
```

Finally, I want to calculate `SUM` and `BITXOR` of `CRC32RES` at the same time:
```SQL
mysql> SELECT
-> COUNT(1) as CNT, SUM(CRC32RES) as SUM_CHECKSUM, BIT_XOR(CRC32RES) as BIT_XOR_CHECKSUM
-> FROM
-> (SELECT
-> CAST(CRC32(CONCAT_WS(',', `s_i_id`, `s_w_id`, CONCAT(ISNULL(`s_i_id`), ISNULL(`s_w_id`))))AS UNSIGNED) as CRC32RES
-> FROM `test`.`tbl`) as CRC32LIST;
+-----+--------------+------------------+
| CNT | SUM_CHECKSUM | BIT_XOR_CHECKSUM |
+-----+--------------+------------------+
| 3 | 9646058107 | 3199522115 |
+-----+--------------+------------------+
1 row in set (0.01 sec)
```

This is OK when the table is very small. However, when the each line of the table is very large, there will be double delay than only use one aggregation.

See the explain of this SQL:
```SQL
mysql> explain SELECT
-> COUNT(1) as CNT, SUM(CRC32RES) as SUM_CHECKSUM, BIT_XOR(CRC32RES) as BIT_XOR_CHECKSUM
-> FROM
-> (SELECT
-> CAST(CRC32(CONCAT_WS(',', `s_i_id`, `s_w_id`, CONCAT(ISNULL(`s_i_id`), ISNULL(`s_w_id`))))AS UNSIGNED) as CRC32RES
-> FROM `test`.`tbl`) as CRC32LIST;
+----------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_17 | 1.00 | root | | funcs:count(Column#11)->Column#4, funcs:sum(Column#12)->Column#5, funcs:bit_xor(Column#13)->Column#6 |
| └─TableReader_18 | 1.00 | root | | data:StreamAgg_9 |
| └─StreamAgg_9 | 1.00 | cop[tikv] | | funcs:count(1)->Column#11, funcs:sum(cast(crc32(concat_ws(",", cast(test.tbl.s_i_id, var_string(20)), cast(test.tbl.s_w_id, var_string(20)), concat("0", cast(isnull(test.tbl.s_w_id), var_string(20))))), bigint(22) UNSIGNED BINARY))->Column#12, funcs:bit_xor(cast(crc32(concat_ws(",", cast(test.tbl.s_i_id, var_string(20)), cast(test.tbl.s_w_id, var_string(20)), concat("0", cast(isnull(test.tbl.s_w_id), var_string(20))))), bigint(22) UNSIGNED BINARY))->Column#13 |
| └─TableFullScan_16 | 3.00 | cop[tikv] | table:tbl | keep order:false, stats:pseudo |
+----------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
```

It seems that this function is calculated twice, which costs double time.
```
cast(crc32(concat_ws(",", cast(test.tbl.s_i_id, var_string(20)), cast(test.tbl.s_w_id, var_string(20)), concat("0", cast(isnull(test.tbl.s_w_id), var_string(20))))), bigint(22) UNSIGNED BINARY)
```

**Describe the feature you'd like:**

Can this be optimized?

**Describe alternatives you've considered:**

**Teachability, Documentation, Adoption, Migration Strategy:**

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.