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

[yanghy233]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

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)

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.

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

[yanghy233]

/label affects-8.1

[Wind-Gone]

The same problem will be encountered in the latest 8.4 release, either.
/label affects-8.4

[jebter]

TiDB [email protected]:test> explain delete
                       -> from `t4`
                       -> where not (POW(450, 13) & 1 < 1);
+-----------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| id                    | estRows  | task      | access object | operator info                                                     |
+-----------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| Delete_4              | N/A      | root      |               | N/A                                                               |
| └─TableReader_8       | 8000.00  | root      |               | data:Selection_7                                                  |
|   └─Selection_7       | 8000.00  | cop[tikv] |               | ge(bitand(cast(3.1028635599719237e+34, bigint(23) BINARY), 1), 1) |
|     └─TableFullScan_6 | 10000.00 | cop[tikv] | table:t4      | keep order:false, stats:pseudo                                    |
+-----------------------+----------+-----------+---------------+-------------------------------------------------------------------+

4 rows in set
Time: 0.009s
TiDB [email protected]:test> explain select *
                       -> from `t4`
                       -> where not (POW(450, 13) & 1 < 1);
+-------------------+---------+-----------+---------------+--------------------------------+
| id                | estRows | task      | access object | operator info                  |
+-------------------+---------+-----------+---------------+--------------------------------+
| TableReader_6     | 3.00    | root      |               | data:TableFullScan_5           |
| └─TableFullScan_5 | 3.00    | cop[tikv] | table:t4      | keep order:false, stats:pseudo |
+-------------------+---------+-----------+---------------+--------------------------------+

[yibin87]

The root cause is tidb treats "BIGINT outof range" as a warning instead of error. For select statement, warning doesn't affect the execution; while for delete, execution will abort if any warning encountered in "strict" sql mode.
Users can set sql mode to non strict mode to force delete statement to execute ignoring any warnings.

[yibin87]

BTW, Mysql treats "BIGINT outof range" as errors by default.