min(truncate()) may lead to incorrect result

[r33s3n6]

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
error.sql.txt

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

The first column represents the minimum value of the c_ou2 column within each group,
the second column represents the maximum value of the c_ou2 column within each group,
and the third column represents the minimum value of truncate(-26340, c_ou2). If there exists a c_ou2 greater than 0, there should be a result of -26340, which is necessarily the minimum value.

3. What did you see instead (Required)

In the multi-node version: correct.
In the single-node version: The value in the third column of the second row is incorrectly 0; it should be -26340.

output_re_main2.log
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.

[yibin87]

Reproduced locally with latest tidb version.

[yibin87]

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t2 values(-1790816583),(2049821819), (-1366665321), (536581933), (-1613686445);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT min(truncate(cast(-26340 as double), ref_11.a)) as c3 FROM t2 as ref_11;
+------+
| c3   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select truncate(cast(-26340 as double), ref_11.a) as c3 FROM t2 as ref_11;
+--------+
| c3     |
+--------+
|      0 |
| -26340 |
|      0 |
| -26340 |
|      0 |
+--------+
5 rows in set (0.01 sec)

The plan looks a little inefficiency but all right:

mysql> explain SELECT min(truncate(cast(-26340 as double), ref_11.a)) as c3 FROM t2 as ref_11;
+----------------------------------+---------+-----------+---------------+--------------------------------------------------+
| id                               | estRows | task      | access object | operator info                                    |
+----------------------------------+---------+-----------+---------------+--------------------------------------------------+
| StreamAgg_9                      | 1.00    | root      |               | funcs:min(Column#5)->Column#3                    |
| └─Projection_17                  | 1.00    | root      |               | truncate(-26340, test.t2.a)->Column#5            |
|   └─Projection_15                | 1.00    | root      |               | test.t2.a                                        |
|     └─TopN_10                    | 1.00    | root      |               | Column#4, offset:0, count:1                      |
|       └─Projection_16            | 5.00    | root      |               | test.t2.a, truncate(-26340, test.t2.a)->Column#4 |
|         └─TableReader_14         | 5.00    | root      |               | data:TableFullScan_13                            |
|           └─TableFullScan_13     | 5.00    | cop[tikv] | table:ref_11  | keep order:false, stats:pseudo                   |
+----------------------------------+---------+-----------+---------------+--------------------------------------------------+

[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

[yibin87]

/assign @yibin87

[yibin87]

/remove-severity critical

[yibin87]

/severity major

[yibin87]

Corner case for not common used 'truncate' function, change to major severity.