SeaRise
commented
1 year ago use test;
drop table if exists t1;
CREATE TABLE t1 (
`store_no` varchar(10) NOT NULL,
`amount` decimal(8,2) NOT NULL DEFAULT '0.00'
);
drop table if exists t2;
CREATE TABLE t2 (
`year_month` date NOT NULL,
`amount` decimal(8,2) NOT NULL
);
alter table t1 set tiflash replica 1;
alter table t2 set tiflash replica 1;
set tidb_allow_mpp=1;set tidb_enforce_mpp=1; set tidb_isolation_read_engines='tiflash';
SELECT SUM(col2)
FROM (
SELECT SUM(amount) AS col1, 0.0 AS col2
FROM t1
GROUP BY store_no
UNION ALL
SELECT 0.0 AS col1, 0.0 AS col2
FROM t2
WHERE amount = 1
UNION ALL
SELECT 0.0 AS col1, amount AS col2
FROM t2
WHERE `year_month` = '2023-09-12 00:00:00.0'
) temp
GROUP BY col1;
+--------------------------------------------------+----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------------+----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------+
| TableReader_112 | 8002.00 | root | | MppVersion: 2, data:ExchangeSender_111 |
| └─ExchangeSender_111 | 8002.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection_106 | 8002.00 | mpp[tiflash] | | Column#17 |
| └─HashAgg_104 | 8002.00 | mpp[tiflash] | | group by:Column#15, funcs:sum(Column#16)->Column#17, stream_count: 36 |
| └─ExchangeReceiver_90 | 8020.00 | mpp[tiflash] | | stream_count: 36 |
| └─ExchangeSender_89 | 8020.00 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#15, collate: binary], stream_count: 36 |
| └─Union_88 | 8020.00 | mpp[tiflash] | | |
| ├─Projection_83 | 8000.00 | mpp[tiflash] | | Column#4->Column#15, 0.00->Column#16 |
| │ └─Projection_57 | 8000.00 | mpp[tiflash] | | Column#4 |
| │ └─HashAgg_58 | 8000.00 | mpp[tiflash] | | group by:test.t1.store_no, funcs:sum(Column#28)->Column#4, stream_count: 36 |
| │ └─ExchangeReceiver_60 | 8000.00 | mpp[tiflash] | | stream_count: 36 |
| │ └─ExchangeSender_59 | 8000.00 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t1.store_no, collate: utf8mb4_bin], stream_count: 36 |
| │ └─HashAgg_55 | 8000.00 | mpp[tiflash] | | group by:test.t1.store_no, funcs:sum(test.t1.amount)->Column#28 |
| │ └─TableFullScan_44 | 10000.00 | mpp[tiflash] | table:t1 | keep order:false, stats:pseudo |
| ├─Projection_84 | 10.00 | mpp[tiflash] | | 0.00->Column#15, 0.00->Column#16 |
| │ └─Selection_70 | 10.00 | mpp[tiflash] | | eq(test.t2.amount, 1) |
| │ └─TableFullScan_69 | 10000.00 | mpp[tiflash] | table:t2 | pushed down filter:empty, keep order:false, stats:pseudo |
| └─Projection_113 | 10.00 | mpp[tiflash] | | Column#15, cast(Column#16, decimal(8,2) BINARY)->Column#16 |
| └─Projection_85 | 10.00 | mpp[tiflash] | | 0.00->Column#15, test.t2.amount->Column#16 |
| └─TableFullScan_79 | 10.00 | mpp[tiflash] | table:t2 | pushed down filter:eq(test.t2.year_month, 2023-09-12 00:00:00.000000), keep order:false, stats:pseudo |
+--------------------------------------------------+----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------+
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
success
3. What did you see instead (Required)
4. What is your TiFlash version? (Required)
master