tiflash return empty result
- 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)
```
use test;
drop table if exists UK_SIGNED_19366;
CREATE TABLE `UK_SIGNED_19366` (
`COL1` mediumint unsigned DEFAULT NULL COMMENT 'WITH DEFAULT',
`COL2` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`COL4` datetime DEFAULT NULL,
`COL3` bigint DEFAULT NULL,
`COL5` float DEFAULT NULL,
UNIQUE KEY `UK_COL1` (`COL1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
alter table UK_SIGNED_19366 set tiflash replica 1;
select sleep(3);
insert into UK_SIGNED_19366 values(1951614, "禸陛鎞灗聵枙颽摖鏔薰丿棞暪牱囍悎竊革鸱霺", "2871-01-14 16:48:22", 620923776300889260, 5.23202e37);
prepare stmt from 'SELECT /*+ read_from_storage(tikv[UK_SIGNED_19366] ) */ * FROM UK_SIGNED_19366 WHERE col1 = ? AND COL1 - ? < ?;';
set @a=1951614, @b=4493158, @c=14336206;
execute stmt using @a,@b,@c;
prepare stmt from 'SELECT /*+ read_from_storage(tiflash[UK_SIGNED_19366] ) */ * FROM UK_SIGNED_19366 WHERE col1 = ? AND COL1 - ? < ?;';
set @a=1951614, @b=4493158, @c=14336206;
execute stmt using @a,@b,@c;
SELECT /*+ read_from_storage(tiflash[UK_SIGNED_19366] ) */ * FROM UK_SIGNED_19366 WHERE col1 = 1951614 AND COL1 - 4493158 < 4493158;
```
### 2. What did you expect to see? (Required)
```
MySQL [test]> prepare stmt from 'SELECT /*+ read_from_storage(tiflash[UK_SIGNED_19366] ) */ * FROM UK_SIGNED_19366 WHERE col1 = ? AND COL1 - ? < ?;';
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> set @a=1951614, @b=4493158, @c=14336206;
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> execute stmt using @a,@b,@c;
ERROR 1690 (22003): BIGINT value is out of range in '(1951614 - 4493158)' or right result
```
### 3. What did you see instead (Required)
```
MySQL [test]> prepare stmt from 'SELECT /*+ read_from_storage(tikv[UK_SIGNED_19366] ) */ * FROM UK_SIGNED_19366 WHERE col1 = ? AND COL1 - ? < ?;';
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> set @a=1951614, @b=4493158, @c=14336206;
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> execute stmt using @a,@b,@c;
ERROR 1690 (22003): BIGINT value is out of range in '(1951614 - 4493158)'
MySQL [test]>
MySQL [test]> prepare stmt from 'SELECT /*+ read_from_storage(tiflash[UK_SIGNED_19366] ) */ * FROM UK_SIGNED_19366 WHERE col1 = ? AND COL1 - ? < ?;';
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> set @a=1951614, @b=4493158, @c=14336206;
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> execute stmt using @a,@b,@c;
Empty set (0.01 sec)
MySQL [test]>
MySQL [test]> SELECT /*+ read_from_storage(tiflash[UK_SIGNED_19366] ) */ * FROM UK_SIGNED_19366 WHERE col1 = 1951614 AND COL1 - 4493158 < 4493158;
+---------+--------------------------------------------------------------+---------------------+--------------------+------------+
| COL1 | COL2 | COL4 | COL3 | COL5 |
+---------+--------------------------------------------------------------+---------------------+--------------------+------------+
| 1951614 | 禸陛鎞灗聵枙颽摖鏔薰丿棞暪牱囍悎竊革鸱霺 | 2871-01-14 16:48:22 | 620923776300889260 | 5.23202e37 |
+---------+--------------------------------------------------------------+---------------------+--------------------+------------+
1 row in set (0.01 sec)
```
### 4. What is your TiDB version? (Required)
```
| Release Version: v6.3.0-alpha
Edition: Community
Git Commit Hash: 8b5b724d8a932239303a1d0ba547323eb0e5161b
Git Branch: heads/refs/tags/v6.3.0-alpha
UTC Build Time: 2022-08-19 14:31:05
GoVersion: go1.18.5
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
```
Contributor guide
Assessment
This issue has not been assessed yet.