Operator should be Batch_Point_Get_1, not TableDual

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists t;
create table t(a float, unique index uidx(a));
insert into t values(9.46347e37);

prepare stmt from 'select * from t where a in (?, ?, ?);';
set @a=-1.56018e38, @b=-1.96716e38, @c=9.46347e37;
execute stmt using @a,@b,@c;

explain select * from t where a in (-1.56018e38, -1.96716e38, 9.46347e37);
select * from t where a in (-1.56018e38, -1.96716e38, 9.46347e37);

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

mysql> execute stmt using @a,@b,@c;
+------------+
| a          |
+------------+
| 9.46347e37 |
+------------+
1 row in set (0.00 sec)

mysql> explain select * from t where a in (-1.56018e38, -1.96716e38, 9.46347e37);
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| Batch_Point_Get_1 ...
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)

mysql> select * from t where a in (-1.56018e38, -1.96716e38, 9.46347e37);
+------------+
| a          |
+------------+
| 9.46347e37 |
+------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

release-4.0、release-5.0、release-5.1 and master

mysql> execute stmt using @a,@b,@c;
+------------+
| a          |
+------------+
| 9.46347e37 |
+------------+
1 row in set (0.00 sec)

mysql> explain select * from t where a in (-1.56018e38, -1.96716e38, 9.46347e37);
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_5 | 0.00    | root |               | rows:0        |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)

mysql> select * from t where a in (-1.56018e38, -1.96716e38, 9.46347e37);
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.1.0-65-g168aab511
Edition: Community
Git Commit Hash: 168aab511f2ed5c21f776e4b0cd9a6161b8bf3f7
Git Branch: release-5.1
UTC Build Time: 2021-07-27 06:39:54
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[Reminiscent]

If we do not define the precision for the float type. We can not use the equal/ in condition to find the result. We can only use like to find the result. There is no such restriction for the double type.

[qw4990]

The root cause is not in plan-cache.
A minimal reproducible case:

mysql> select * from t where a in (9.46347e37);
+------------+
| a          |
+------------+
| 9.46347e37 |
+------------+
1 row in set (0.00 sec)

mysql> select * from t where a =9.46347e37;
Empty set (0.00 sec)

[Reminiscent]

In MySQL8.0

mysql> create table t(a float, unique index uidx(a));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into t values(9.46347e37), (1.1);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t where a = 1.1;
Empty set (0.02 sec)

mysql> select * from t where a in (1.1);
Empty set (0.01 sec)

mysql> select * from t where a in (1.1, 9.46347e37);
Empty set (0.00 sec)

In TiDB latest master

mysql> create table t(a float, unique index uidx(a));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(9.46347e37), (1.1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t where a = 1.1;
Empty set (0.00 sec)

mysql> select * from t where a in (1.1);
+------+
| a    |
+------+
|  1.1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t where a in (1.1, 9.46347e37);
+------------+
| a          |
+------------+
|        1.1 |
| 9.46347e37 |
+------------+
2 rows in set (0.00 sec)

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.