Repeat calculation when aggregate function parameters are the same
- 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
Assessment
This issue has not been assessed yet.