subqueries produce different result between different deploy topology (2)

[r33s3n6]

1. Minimal reproduce step (Required)

First execute init.sql to create the table. Then executing error.sql yields unexpected results. Note that reproducing these results might not be entirely stable. Typically, it can be completed within three attempts. You can try executing multiple times or execute it again after rebuilding the table.
init.sql.txt
error.sql.txt

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

The first column is a column c_k in the table.
The second column is the maximum value of t_xf1at0.c_pv from another table t_xf1at0 under certain conditions.
The third column is a subquery that calculates t_xf1at0.c_pv <= c_k, sorted in ascending order. If the maximum value of t_xf1at0.c_pv is greater than c_k, the result should be 0; otherwise, it should be 1.

3. What did you see instead (Required)

In both TiDB multi-node and TiDB single-node versions, incorrect values are present, but the results are different.
output_re_main.log
output_re_single.log

4. What is your TiDB version? (Required)

Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv

topology:

distributed.yaml:

global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

pd_servers:
  - host: 10.0.2.31

tidb_servers:
  - host: 10.0.2.21
  - host: 10.0.2.22

tikv_servers:
  - host: 10.0.2.11
  - host: 10.0.2.12
  - host: 10.0.2.13

monitoring_servers:
  - host: 10.0.2.8

grafana_servers:
  - host: 10.0.2.8

alertmanager_servers:
  - host: 10.0.2.8

single.yaml

global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

pd_servers:
  - host: 10.0.2.73

tidb_servers:
  - host: 10.0.2.72

tikv_servers:
  - host: 10.0.2.71

about us

We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned vulnerabilities in TiDB that may lead to database logic error.

[yibin87]

+------------------------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------
| id                                       | estRows   | actRows | task      | access object | execution info                                                           +------------------------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------
| Sort_32                                  | 328.00    | 328     | root      |               | time:242.4ms, loops:2, RU:257.804864
| └─Projection_34                          | 328.00    | 328     | root      |               | time:242.3ms, loops:2, Concurrency:OFF
|   └─Apply_36                             | 328.00    | 328     | root      |               | time:242.2ms, loops:2, Concurrency:OFF, cache:OFF
|     ├─TableReader_38(Build)              | 328.00    | 328     | root      |               | time:1.59ms, loops:3, cop_task: {num: 2, max: 992.9µs, min: 517.7µs, avg:|     │ └─TableFullScan_37                 | 328.00    | 328     | cop[tikv] | table:ref_3   | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:6, tasks:2
|     └─Projection_39(Probe)               | 328.00    | 328     | root      |               | time:225.3ms, loops:656, Concurrency:OFF
|       └─Projection_48                    | 328.00    | 328     | root      |               | time:224.7ms, loops:656, Concurrency:OFF
|         └─TopN_40                        | 328.00    | 328     | root      |               | time:224.2ms, loops:656
|           └─Projection_49                | 328.00    | 328     | root      |               | time:221.9ms, loops:656, Concurrency:OFF
|             └─TableReader_46             | 328.00    | 328     | root      |               | time:219.2ms, loops:656, cop_task: {num: 328, max: 2.68ms, min: 218µs, av|               └─TopN_45                  | 328.00    | 328     | cop[tikv] |               | tikv_task:{proc max:1ms, min:0s, avg: 606.7µs, p80:1ms, p95:1ms, iters:32
|                 └─Selection_44           | 155971.87 | 85280   | cop[tikv] |               | tikv_task:{proc max:1ms, min:0s, avg: 268.3µs, p80:1ms, p95:1ms, iters:32
|                   └─TableFullScan_43     | 156128.00 | 156128  | cop[tikv] | table:ref_4   | tikv_task:{proc max:1ms, min:0s, avg: 243.9µs, p80:1ms, p95:1ms, iters:32
+------------------------------------------+-----------+---------+-----------+---------------+--------------------------------------------------------------------------

The plan seems ok, while reproduced the incorrect result with latest version. Correlated column replacement is suspicious.

[yibin87]

/severity critical

[yibin87]

/label affects-8.1

[yibin87]

Narrow down the case:

create table t0 (c_k int); 
insert into t0 values(-2127559046),(-190905159),(-171305020),(-59638845),(98004414),(2111663670),(2137868682),(2137868682),(2142611610);
create table t1 (c_pv int);
insert into t1 values(-2123227448), (2131706870), (-2071508387), (2135465388), (2052805244), (-2066000113);
mysql> source error_test.sql
+-------------+------------+------+
| p0          | p1         | p2   |
+-------------+------------+------+
| -2127559046 | 2135465388 |    0 |
|  -190905159 | 2135465388 |    1 |
|  -171305020 | 2135465388 |    1 |
|   -59638845 | 2135465388 |    1 |
|    98004414 | 2135465388 |    1 |
|  2111663670 | 2135465388 |    1 |
|  2137868682 | 2135465388 |    1 |
|  2137868682 | 2135465388 |    1 |
|  2142611610 | 2135465388 |    1 |
+-------------+------------+------+
9 rows in set (0.01 sec)

[yibin87]

/label affects-5.4

[yibin87]

/label affects-6.1

[yibin87]

/label affects-6.5

[yibin87]

/label affects-7.1

[yibin87]

/label affects-7.5