Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The result of max aggregation fun with collation ci is not correct #6807

Closed
lilinghai opened this issue Feb 13, 2023 · 2 comments · Fixed by #7002
Closed

The result of max aggregation fun with collation ci is not correct #6807

lilinghai opened this issue Feb 13, 2023 · 2 comments · Fixed by #7002
Assignees
Labels

Comments

@lilinghai
Copy link

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `x1` (
  `a` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `x2` (
  `a` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `b` char(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert into x1(a ,id) values('jlsf',1),(null,2),('YmkS',3),('0',4);
insert into x2(a,b,id) values('yMKs','jlsf',1),('yyds','YmkS',2),('cc',4,3),(null,null,4),('abc',null,5),(null,'df',6);
-- set Tiflash replica
select max(a) from x1 where a in ( select b from x2 where a>b);
-- expect yMKs but got jlsf
 explain analyze select max(a) from x1 where a in ( select b from x2 where a>b);
+----------------------------------------------+---------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                                           | estRows | actRows | task         | access object | execution info                                                                                                                                                                                                                                                                                                                                                          | operator info                                                                                                              | memory    | disk |
+----------------------------------------------+---------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_17                                 | 1.00    | 1       | root         |               | time:7.7ms, loops:2                                                                                                                                                                                                                                                                                                                                                     | funcs:max(test.x1.a)->Column#6                                                                                             | 1.28 KB   | N/A  |
| └─TopN_21                                    | 1.00    | 1       | root         |               | time:7.69ms, loops:2                                                                                                                                                                                                                                                                                                                                                    | test.x1.a:desc, offset:0, count:1                                                                                          | 288 Bytes | N/A  |
|   └─TableReader_78                           | 1.00    | 1       | root         |               | time:7.68ms, loops:2, cop_task: {num: 3, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                | MppVersion: 1, data:ExchangeSender_77                                                                                      | 809 Bytes | N/A  |
|     └─ExchangeSender_77                      | 1.00    | 1       | mpp[tiflash] |               | tiflash_task:{proc max:5.68ms, min:5.47ms, avg: 5.58ms, p80:5.68ms, p95:5.68ms, iters:1, tasks:2, threads:8}                                                                                                                                                                                                                                                            | ExchangeType: PassThrough                                                                                                  | N/A       | N/A  |
|       └─TopN_76                              | 1.00    | 1       | mpp[tiflash] |               | tiflash_task:{proc max:5.68ms, min:5.47ms, avg: 5.58ms, p80:5.68ms, p95:5.68ms, iters:1, tasks:2, threads:2}                                                                                                                                                                                                                                                            | test.x1.a:desc, offset:0, count:1                                                                                          | N/A       | N/A  |
|         └─HashJoin_75                        | 4.00    | 2       | mpp[tiflash] |               | tiflash_task:{proc max:5.47ms, min:4.68ms, avg: 5.08ms, p80:5.47ms, p95:5.47ms, iters:3, tasks:2, threads:8}                                                                                                                                                                                                                                                            | inner join, equal:[eq(test.x1.a, test.x2.b)]                                                                               | N/A       | N/A  |
|           ├─ExchangeReceiver_31(Build)       | 4.00    | 6       | mpp[tiflash] |               | tiflash_task:{proc max:4.68ms, min:4.47ms, avg: 4.58ms, p80:4.68ms, p95:4.68ms, iters:3, tasks:2, threads:8}                                                                                                                                                                                                                                                            |                                                                                                                            | N/A       | N/A  |
|           │ └─ExchangeSender_30              | 4.00    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:4.56ms, min:0s, avg: 2.28ms, p80:4.56ms, p95:4.56ms, iters:2, tasks:2, threads:1}                                                                                                                                                                                                                                                                | ExchangeType: Broadcast                                                                                                    | N/A       | N/A  |
|           │   └─Selection_29                 | 4.00    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:3.56ms, min:0s, avg: 1.78ms, p80:3.56ms, p95:3.56ms, iters:2, tasks:2, threads:1}                                                                                                                                                                                                                                                                | not(isnull(test.x1.a))                                                                                                     | N/A       | N/A  |
|           │     └─TableFullScan_28           | 4.00    | 4       | mpp[tiflash] | table:x1      | tiflash_task:{proc max:3.56ms, min:0s, avg: 1.78ms, p80:3.56ms, p95:3.56ms, iters:2, tasks:2, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:2, total_skipped_packs:0, total_scanned_rows:6, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0} | keep order:false, stats:pseudo                                                                                             | N/A       | N/A  |
|           └─Projection_41(Probe)             | 3.84    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:2.47ms, min:1.68ms, avg: 2.08ms, p80:2.47ms, p95:2.47ms, iters:3, tasks:2, threads:8}                                                                                                                                                                                                                                                            | test.x2.b                                                                                                                  | N/A       | N/A  |
|             └─HashAgg_42                     | 3.84    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:2.47ms, min:1.68ms, avg: 2.08ms, p80:2.47ms, p95:2.47ms, iters:3, tasks:2, threads:8}                                                                                                                                                                                                                                                            | group by:test.x2.b, funcs:firstrow(test.x2.b)->test.x2.b, stream_count: 4                                                  | N/A       | N/A  |
|               └─ExchangeReceiver_44          | 3.84    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:2.47ms, min:1.68ms, avg: 2.08ms, p80:2.47ms, p95:2.47ms, iters:3, tasks:2, threads:8}                                                                                                                                                                                                                                                            | stream_count: 4                                                                                                            | N/A       | N/A  |
|                 └─ExchangeSender_43          | 3.84    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:4.13ms, min:0s, avg: 2.07ms, p80:4.13ms, p95:4.13ms, iters:1, tasks:2, threads:1}                                                                                                                                                                                                                                                                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.x2.b, collate: utf8mb4_general_ci], stream_count: 4 | N/A       | N/A  |
|                   └─HashAgg_33               | 3.84    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:4.13ms, min:0s, avg: 2.07ms, p80:4.13ms, p95:4.13ms, iters:1, tasks:2, threads:1}                                                                                                                                                                                                                                                                | group by:test.x2.b,                                                                                                        | N/A       | N/A  |
|                     └─Selection_40           | 4.80    | 3       | mpp[tiflash] |               | tiflash_task:{proc max:4.13ms, min:0s, avg: 2.07ms, p80:4.13ms, p95:4.13ms, iters:1, tasks:2, threads:1}                                                                                                                                                                                                                                                                | gt(test.x2.a, test.x2.b), not(isnull(test.x2.b))                                                                           | N/A       | N/A  |
|                       └─TableFullScan_39     | 6.00    | 6       | mpp[tiflash] | table:x2      | tiflash_task:{proc max:4.13ms, min:0s, avg: 2.07ms, p80:4.13ms, p95:4.13ms, iters:1, tasks:2, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:2, total_skipped_packs:0, total_scanned_rows:2, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0} | keep order:false, stats:pseudo                                                                                             | N/A       | N/A  |
+----------------------------------------------+---------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+-----------+------+
17 rows in set (0.03 sec)

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiFlash version? (Required)

master

@xzhangxian1008
Copy link
Contributor

/assign

@xzhangxian1008
Copy link
Contributor

It seems that data is lost during the concurrent compution as we can get correct when max_threads is set to 1.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants