tidb
tidb copied to clipboard
pingcap
Unexpected Result in Windows Function inTiFlash
Bug Report
1. Minimal reproduce step (Required)
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 (c0) VALUES (1);
ALTER TABLE t0 SET TIFLASH REPLICA 1;
SELECT AVG(0) OVER(ORDER BY c0) FROM t0; -- {20813.5040}
This query should return 0, because AVG(0)=0. However, executing the above query with MPP in TiFlash returns a non-zero number, which is unexpected.
The plan is as follow:
id estRows task access object operator info
TableReader_25 10000.00 root MppVersion: 3, data:ExchangeSender_24
└─ExchangeSender_24 10000.00 mpp[tiflash] ExchangeType: PassThrough
└─Projection_8 10000.00 mpp[tiflash] Column#4->Column#5
└─Window_23 10000.00 mpp[tiflash] avg(0)->Column#4 over(order by testdb.t0.c0 range between unbounded preceding and current row)
└─Sort_14 10000.00 mpp[tiflash] testdb.t0.c0
└─ExchangeReceiver_13 10000.00 mpp[tiflash]
└─ExchangeSender_12 10000.00 mpp[tiflash] ExchangeType: PassThrough, Compression: FAST
└─TableFullScan_11 10000.00 mpp[tiflash] table:t0 keep order:false, stats:pseudo
2. What did you expect to see? (Required)
{0}
3. What did you see instead (Required)
{20813.5040}
4. What is your TiDB version? (Required)
tiup playground nightly --host 0.0.0.0 --kv 3 --tiflash 3
8.0.11-TiDB-v9.0.0-beta.1.pre-554-gb1a5536a64-dirty
It can get the right result in the v8.5.2. But It has the problem in the master.
Procedure:
drop table if exists t0;
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 (c0) VALUES (1);
ALTER TABLE t0 SET TIFLASH REPLICA 1;
set tidb_isolation_read_engines='tiflash';
SELECT AVG(0) OVER(ORDER BY c0) FROM t0;
wrong result & unexpected plan:
set tidb_isolation_read_engines='tiflash';
SELECT AVG(0) OVER(ORDER BY c0) FROM t0;
+--------------------------+
| AVG(0) OVER(ORDER BY c0) |
+--------------------------+
| -154750.0032 |
+--------------------------+
desc analyze SELECT AVG(0) OVER(ORDER BY c0) FROM t0;
+--------------------------------+----------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------+----------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+---------+------+
| TableReader_25 | 10000.00 | 1 | root | | time:6.6ms, open:742.1µs, close:4.79µs, loops:2, RU:0.67, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 3, data:ExchangeSender_24 | 1.96 KB | N/A |
| └─ExchangeSender_24 | 10000.00 | 1 | mpp[tiflash] | | tiflash_task:{time:4.32ms, loops:1, threads:8}, tiflash_network: {inner_zone_send_bytes: 56} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_8 | 10000.00 | 1 | mpp[tiflash] | | tiflash_task:{time:4.2ms, loops:1, threads:8} | Column#4->Column#5 | N/A | N/A |
| └─Window_23 | 10000.00 | 1 | mpp[tiflash] | | tiflash_task:{time:4.2ms, loops:1, threads:8} | avg(0)->Column#4 over(order by test.t0.c0 range between unbounded preceding and current row) | N/A | N/A |
| └─Sort_14 | 10000.00 | 1 | mpp[tiflash] | | tiflash_task:{time:4.15ms, loops:1, threads:1} | test.t0.c0 | N/A | N/A |
| └─ExchangeReceiver_13 | 10000.00 | 1 | mpp[tiflash] | | tiflash_task:{time:4.14ms, loops:1, threads:8} | | N/A | N/A |
| └─ExchangeSender_12 | 10000.00 | 1 | mpp[tiflash] | | tiflash_task:{time:4.1ms, loops:1, threads:8} | ExchangeType: PassThrough, Compression: FAST | N/A | N/A |
| └─TableFullScan_11 | 10000.00 | 1 | mpp[tiflash] | table:t0 | tiflash_task:{time:4.03ms, loops:1, threads:8}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, local_regions:1, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 1, max/min: 1/1=1.000000}, delta_rows:1, delta_bytes:22, segments:1, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:0ms, tot_build_inputstream:0ms, min_local_stream:1ms, max_local_stream:1ms, dtfile:{data_scanned_rows:1, data_skipped_rows:0, mvcc_scanned_rows:0, mvcc_skipp... | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------+----------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+---------+------+
Wait for a while or exec analyze table t0, the plan becomes normal
set tidb_isolation_read_engines='tiflash';
SELECT AVG(0) OVER(ORDER BY c0) FROM t0;
+--------------------------+
| AVG(0) OVER(ORDER BY c0) |
+--------------------------+
| 0.0000 |
+--------------------------+
desc analyze SELECT AVG(0) OVER(ORDER BY c0) FROM t0;
+----------------------------+---------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------+---------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+-----------+---------+
| Projection_7 | 1.00 | 1 | root | | time:5.68ms, open:858.9µs, close:7.04µs, loops:2, RU:0.33, Concurrency:OFF | Column#4->Column#5 | 1.77 KB | N/A |
| └─Window_10 | 1.00 | 1 | root | | time:5.66ms, open:848.6µs, close:5.5µs, loops:2 | avg(0)->Column#4 over(order by test.t0.c0 range between unbounded preceding and current row) | N/A | N/A |
| └─Sort_20 | 1.00 | 1 | root | | time:5.64ms, open:842.5µs, close:5.29µs, loops:2 | test.t0.c0 | 396 Bytes | 0 Bytes |
| └─TableReader_19 | 1.00 | 1 | root | | time:5.57ms, open:809.9µs, close:4.29µs, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 3, data:ExchangeSender_18 | 720 Bytes | N/A |
| └─ExchangeSender_18 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:3.62ms, loops:1, threads:8}, tiflash_network: {inner_zone_send_bytes: 24} | ExchangeType: PassThrough | N/A | N/A |
| └─TableFullScan_17 | 1.00 | 1 | mpp[tiflash] | table:t0 | tiflash_task:{time:3.57ms, loops:1, threads:8}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, local_regions:1, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 1, max/min: 1/1=1.000000}, delta_rows:0, delta_bytes:0, segments:1, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:0ms, tot_build_inputstream:0ms, min_local_stream:2ms, max_local_stream:2ms, dtfile:{data_scanned_rows:1, data_skipped_rows:0, mvcc_scanned_rows:0, mvcc_skippe... | keep order:false, stats:pseudo | N/A | N/A |
+----------------------------+---------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+-----------+---------+
Whatever the constant argument is, the result is a fixed random value. It looks like the intermediate results used for computing the constant expression are not initialized on the tiflash side.
drop table if exists t0;
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 (c0) VALUES (1);
ALTER TABLE t0 SET TIFLASH REPLICA 1;
set tidb_isolation_read_engines='tiflash';
SELECT AVG(0) OVER(ORDER BY c0), avg(c0) OVER(ORDER BY c0), sum(1) OVER(ORDER BY c0), sum(c0) OVER(ORDER BY c0) FROM t0;
+--------------------------+---------------------------+--------------------------+---------------------------+
| AVG(0) OVER(ORDER BY c0) | avg(c0) OVER(ORDER BY c0) | sum(1) OVER(ORDER BY c0) | sum(c0) OVER(ORDER BY c0) |
+--------------------------+---------------------------+--------------------------+---------------------------+
| 90695.4240 | 1.0000 | 240608864 | 1 |
+--------------------------+---------------------------+--------------------------+---------------------------+
SELECT AVG(1234) OVER(ORDER BY c0), avg(c0) OVER(ORDER BY c0), sum(5678) OVER(ORDER BY c0), sum(c0) OVER(ORDER BY c0) FROM t0;
+-----------------------------+---------------------------+-----------------------------+---------------------------+
| AVG(1234) OVER(ORDER BY c0) | avg(c0) OVER(ORDER BY c0) | sum(5678) OVER(ORDER BY c0) | sum(c0) OVER(ORDER BY c0) |
+-----------------------------+---------------------------+-----------------------------+---------------------------+
| 90695.4240 | 1.0000 | 240608864 | 1 |
+-----------------------------+---------------------------+-----------------------------+---------------------------+
It is a bug related to tiflash's mpp module.
