pingcap / pingcap/tidb

Same predicate yields different results in the SELECT and DELETE statements respectively.

Open
#57,640 5 comments 1 reaction 0 assignees View on GitHub
affects-8.1 may-affects-5.4 may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 may-affects-8.5 severity/major sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```sql
drop table if exists `t4`;
create table `t4`
(
`vkey` integer,
`pkey` integer,
`c15066` varchar(100),
`valueA` double,
`valueB` double

)SHARD_ROW_ID_BITS=0;

insert into `t4`
values (5084, 5094000, null, 30.10, 12);
insert into `t4`
values (5087, 5097000, null, 4.5, 20);
insert into `t4`
values (5090, 5100000, null, 450, 13);

-- same predicate in select and delete respectively
select *
from `t4`
where not (POW(450, 13) & 1 < 1);

delete
from `t4`
where not (POW(450, 13) & 1 < 1);

```

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

Generally speaking, the data affected by the SELECT and DELETE statements should be the same.

However, one executes normally while the other reports an error.

### 3. What did you see instead (Required)

```sql
MySQL [test]> select *
-> from `t4`
-> where not (POW(450, 13) & 1 < 1);
+------+---------+--------+--------+--------+
| vkey | pkey | c15066 | valueA | valueB |
+------+---------+--------+--------+--------+
| 5084 | 5094000 | NULL | 30.1 | 12 |
| 5087 | 5097000 | NULL | 4.5 | 20 |
| 5090 | 5100000 | NULL | 450 | 13 |
+------+---------+--------+--------+--------+
3 rows in set, 1 warning (0.04 sec)

MySQL [test]> delete
-> from `t4`
-> where not (POW(450, 13) & 1 < 1);
ERROR 1690 (22003): constant 31028635599719237000000000000000000 overflows LongLong
```

But when I try to understand whether the POW function lead to the error, the following results indicate that it is executing normally.

```sql
MySQL [test]> select POW(450, 13) & 1 < 1;
+----------------------+
| POW(450, 13) & 1 < 1 |
+----------------------+
| 0 |
+----------------------+
1 row in set, 1 warning (0.03 sec)
```

### 4. What is your TiDB version? (Required)

TiDB v8.1.1

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.