Negative operation can not be pushed to tiflash
Open
Nobody has claimed this yet.
component/compute
type/feature-request
- Dominant language
- C++
- Stars
- 1k
- Forks
- 423
- Avg merge
- 1d 15h
- Merged PRs (30d)
- 24
Description
v6.5.3
create table t1(
a decimal(20,3),
b decimal(20,3),
code char(10)
);
alter table t1 set tiflash replica 2;
explain analyze
select /*+ read_from_storage(tiflash[t1]) */
sum(
if( 0=0, -(a+b) , 1 )
)
from
t1
group by code;
+----------------------------+----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------+----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+-----------+------+
| HashAgg_6 | 8000.00 | 0 | root | | time:4.6ms, loops:1, partial_worker:{wall_time:4.587674ms, concurrency:5, task_num:0, tot_wait:22.127321ms, tot_exec:0s, tot_time:22.131425ms, max:4.42754ms, p95:4.42754ms}, final_worker:{wall_time:4.599553ms, concurrency:5, task_num:0, tot_wait:22.089071ms, tot_exec:1.884µs, tot_time:22.092744ms, max:4.435505ms, p95:4.435505ms} | group by:Column#7, funcs:sum(Column#6)->Column#5 | 28.0 KB | N/A |
| └─Projection_16 | 10000.00 | 0 | root | | time:4.45ms, loops:1, Concurrency:5 | unaryminus(plus(test.t1.a, test.t1.b))->Column#6, test.t1.code | 27.2 KB | N/A |
| └─TableReader_11 | 10000.00 | 0 | root | | time:4.38ms, loops:1, cop_task: {num: 1, max: 4.49ms, proc_keys: 0, rpc_num: 1, rpc_time: 4.47ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_10 | 192 Bytes | N/A |
| └─TableFullScan_10 | 10000.00 | 0 | cop[tiflash] | table:t1 | tiflash_task:{time:3.41ms, loops:0, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
+----------------------------+----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+-----------+------+
4 rows in set, 6 warnings (0.01 sec)
MySQL [TEST]> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Scalar function 'unaryminus'(signature: UnaryMinusDecimal, return type: decimal(22,3)) is not supported to push down to tiflash now. |
| Warning | 1105 | Aggregation can not be pushed to tiflash because arguments of AggFunc `sum` contains unsupported exprs |
| Warning | 1105 | Scalar function 'unaryminus'(signature: UnaryMinusDecimal, return type: decimal(22,3)) is not supported to push down to tiflash now. |
| Warning | 1105 | Aggregation can not be pushed to tiflash because arguments of AggFunc `sum` contains unsupported exprs |
| Warning | 1105 | Scalar function 'unaryminus'(signature: UnaryMinusDecimal, return type: decimal(22,3)) is not supported to push down to tiflash now. |
| Warning | 1105 | Aggregation can not be pushed to tiflash because arguments of AggFunc `sum` contains unsupported exprs |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
workaround：
explain analyze
select /*+ read_from_storage(tiflash[t1]) */
sum(
if( 0=0, a *(-1) + b *(-1) , 1 )
)
from
t1
group by code;
-> group by code;
+--------------------------------------+----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+-----------+------+
| TableReader_44 | 8000.00 | 0 | root | | time:7.95ms, loops:1, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_43 | 486 Bytes | N/A |
| └─ExchangeSender_43 | 8000.00 | 0 | mpp[tiflash] | | tiflash_task:{time:6.91ms, loops:0, threads:8} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_39 | 8000.00 | 0 | mpp[tiflash] | | tiflash_task:{time:6.91ms, loops:0, threads:8} | Column#5 | N/A | N/A |
| └─HashAgg_40 | 8000.00 | 0 | mpp[tiflash] | | tiflash_task:{time:5.91ms, loops:0, threads:8} | group by:test.t1.code, funcs:sum(Column#9)->Column#5 | N/A | N/A |
| └─ExchangeReceiver_42 | 8000.00 | 0 | mpp[tiflash] | | tiflash_task:{time:5.91ms, loops:0, threads:8} | | N/A | N/A |
| └─ExchangeSender_41 | 8000.00 | 0 | mpp[tiflash] | | tiflash_task:{time:6.36ms, loops:0, threads:8} | ExchangeType: HashPartition, Hash Cols: [name: test.t1.code, collate: utf8mb4_bin] | N/A | N/A |
| └─HashAgg_37 | 8000.00 | 0 | mpp[tiflash] | | tiflash_task:{time:6.36ms, loops:0, threads:8} | group by:Column#11, funcs:sum(Column#10)->Column#9 | N/A | N/A |
| └─Projection_45 | 10000.00 | 0 | mpp[tiflash] | | tiflash_task:{time:6.36ms, loops:0, threads:8} | plus(mul(test.t1.a, -1), mul(test.t1.b, -1))->Column#10, test.t1.code | N/A | N/A |
| └─TableFullScan_26 | 10000.00 | 0 | mpp[tiflash] | table:t1 | tiflash_task:{time:6.36ms, loops:0, threads:8} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------------+----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+-----------+------+
9 rows in set (0.01 sec)
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the v6.5.3 SQL example on TiFlash and compare it with the multiplication workaround. Investigate the pushdown handling for decimal unary-minus expressions; done means the negative expression and aggregation push down to TiFlash without the reported unsupported-expression warnings, with regression coverage for the query.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100