subselect in the `UNION ALL` query may produce incorrect result

### 1. Minimal reproduce step (Required)
Firstly, 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 `error.sql` multiple times or execute `init.sql` again to rebuild the table.
[init.sql.txt](https://github.com/pingcap/tidb/files/15149402/init.sql.txt)
[error.sql.txt](https://github.com/pingcap/tidb/files/15149400/error.sql.txt)

This problem is not easy to reproduce, but I have managed to reproduceit successfully several times. Sometimes, it requires waiting for a while after creating the table before repeatedly executing `error.sql`.

### 2. What did you expect to see? (Required)
When the result in the first column is 1, it means it comes from the second select query, where the third column is a subquery. When executed separately, the result of the subquery is NULL.
```
select c_tk from t_j order by c_tk limit 1 offset 5;
Empty set (0.01 sec)
```

Therefore, when the first column is 1, the third column should always be NULL.

### 3. What did you see instead (Required)
However, there are some cases where the column is not NULL, and there is inconsistency between the TiDB single-node version and the multi-node version.

[output_re_main2.log](https://github.com/pingcap/tidb/files/15149406/output_re_main2.log)
[output_re_single2.log](https://github.com/pingcap/tidb/files/15149407/output_re_single2.log)

### 4. What is your TiDB version? (Required)
```
Release Version: v8.0.0
Edition: Community
Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916
Git Branch: HEAD
UTC Build Time: 2024-03-28 14:22:15
GoVersion: go1.21.4
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

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

tiflash_servers:
- host: 10.0.2.32
```

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

tiflash_servers:
- host: 10.0.2.74
```

### 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.