subqueries produce different result between different deploy topology (3)

[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_este10ce0x in the table.
The second column is a subquery that searches for the maximum value of ref_2.c_este10ce0x between ref_5.c_ub_1s91wi and NULL. If the maximum value of ref_5.c_ub_1s91wi is less than c_este10ce0x, all values will be evaluated as NULL, and thus output NULL. Otherwise, there exist some rows evaluated as 0, and thus output 0.

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.
Under the constraint condition, the maximum value of ref_5.c_ub_1s91wi is 2114215637. However, even when c_este10ce0x is less than this value, some result is still evaluated as NULL.
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_13 | 528.00 | 528 | root | | time:1.24s, loops:2, RU:7501.356338 | └─Apply_17 | 528.00 | 528 | root | | time:1.24s, loops:2, Concurrency:OFF, cache:ON, cacheHitRatio:9.280%
| ├─TableReader_19(Build) | 528.00 | 528 | root | | time:966.4µs, loops:3, cop_task: {num: 4, max: 885.6µs, min: 846.6µs, avg: | │ └─TableFullScan_18 | 528.00 | 528 | cop[tikv] | table:ref_2 | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:12, tasks:4}| └─Projection_20(Probe) | 528.00 | 479 | root | | time:1.17s, loops:958, Concurrency:OFF | └─Projection_29 | 528.00 | 479 | root | | time:1.17s, loops:958, Concurrency:OFF | └─TopN_21 | 528.00 | 479 | root | | time:1.17s, loops:958
| └─Projection_30 | 528.00 | 13412 | root | | time:1.16s, loops:1437, Concurrency:OFF | └─TableReader_27 | 528.00 | 13412 | root | | time:1.14s, loops:1437, cop_task: {num: 15328, max: 7.75ms, min: 281µs, avg| └─TopN_26 | 528.00 | 13412 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 1.3ms, p80:3ms, p95:4ms, iters:15328, | └─Selection_25 | 57728.00 | 68018 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 1.23ms, p80:3ms, p95:3ms, iters:15328,| └─TableFullScan_24 | 173184.00 | 157112 | cop[tikv] | table:ref_5 | tikv_task:{proc max:0s, min:0s, avg: 1.16ms, p80:3ms, p95:3ms, iters:15328,
+----------------------------------------+-----------+---------+-----------+---------------+----------------------------------------------------------------------------

[yibin87]

The plan and issue is very similar to #52777, it is probably the same root cause.

[yibin87]

/label affects-8.1

[yibin87]

close as duplicated.

[yibin87]

/close

[ti-chi-bot]

@yibin87: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.