incorrect tableDual plan which would cause no result

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t5cb5d4a7` (   `col_12` binary(172) NOT NULL,   `col_13` bigint(20) unsigned NOT NULL,   KEY `idx_2` (`col_12`,`col_13`),   PRIMARY KEY (`col_13`,`col_12`)  ,   KEY `idx_5` (`col_13`,`col_12`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
desc SELECT 1 FROM `t5cb5d4a7` WHERE `t5cb5d4a7`.`col_13`>9223372036854775807 AND `t5cb5d4a7`.`col_13`>8900084226548014260;

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

TableRangeScan

3. What did you see instead (Required)

mysql> desc SELECT 1 FROM `t5cb5d4a7` WHERE `t5cb5d4a7`.`col_13`>9223372036854775807 AND `t5cb5d4a7`.`col_13`>8900084226548014260;
+-------------------+---------+------+---------------+---------------+
| id                | estRows | task | access object | operator info |
+-------------------+---------+------+---------------+---------------+
| Projection_4      | 3333.33 | root |               | 1->Column#3   |
| └─TableDual_5     | 3333.33 | root |               | rows:0        |
+-------------------+---------+------+---------------+---------------+
2 rows in set (0.00 sec)

mysql> desc SELECT 1 FROM `t5cb5d4a7` WHERE `t5cb5d4a7`.`col_13`>9223372036854775807;
+--------------------------+---------+-----------+-----------------+------------------------------------------------------------------+
| id                       | estRows | task      | access object   | operator info                                                    |
+--------------------------+---------+-----------+-----------------+------------------------------------------------------------------+
| Projection_4             | 3333.33 | root      |                 | 1->Column#3                                                      |
| └─TableReader_6          | 3333.33 | root      |                 | data:TableRangeScan_5                                            |
|   └─TableRangeScan_5     | 3333.33 | cop[tikv] | table:t5cb5d4a7 | range:(9223372036854775807,+inf], keep order:false, stats:pseudo |
+--------------------------+---------+-----------+-----------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> desc SELECT 1 FROM `t5cb5d4a7` WHERE `t5cb5d4a7`.`col_13`>8900084226548014260;
+--------------------------+---------+-----------+-----------------+------------------------------------------------------------------+
| id                       | estRows | task      | access object   | operator info                                                    |
+--------------------------+---------+-----------+-----------------+------------------------------------------------------------------+
| Projection_4             | 3333.33 | root      |                 | 1->Column#3                                                      |
| └─TableReader_6          | 3333.33 | root      |                 | data:TableRangeScan_5                                            |
|   └─TableRangeScan_5     | 3333.33 | cop[tikv] | table:t5cb5d4a7 | range:(8900084226548014260,+inf], keep order:false, stats:pseudo |
+--------------------------+---------+-----------+-----------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

33480e8

[onlyacat]

Problem comes from excludeToIncludeForIntPoint in detacher.go

https://github.com/pingcap/tidb/blob/c0fc3baf084ae3343328f30195c5e38f0fbe9ccf/pkg/util/ranger/detacher.go#L498C1-L500C5

When meeting a math.MaxInt64 for KindInt64 or 0 for KindUint64, simply an nil is returned.

Another corner case (0 for KindUint64) shows below:

CREATE TABLE `t5` (`d` int not null, `c` int not null, PRIMARY KEY (`d`, `c`));
CREATE TABLE `t6` (`d` bigint UNSIGNED not null);

insert into t5 values (-3, 6);
insert into t6 values (0), (1), (2), (3);

select * from t5 where d < (select min(d) from t6);

select * from t5 where d < (select min(d) from t6) and d < 3;

result follows (also can be reproduced on master branch):

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.01 sec)

mysql> select * from t5 where d < (select min(d) from t6);
+----+---+
| d  | c |
+----+---+
| -3 | 6 |
+----+---+
1 row in set (0.01 sec)

mysql> select * from t5 where d < (select min(d) from t6) and d < 3;
Empty set (0.01 sec)

mysql> explain select * from t5 where d < (select min(d) from t6) and d < 3;
+--------------+---------+------+---------------+---------------+
| id           | estRows | task | access object | operator info |
+--------------+---------+------+---------------+---------------+
| TableDual_31 | 0.33    | root |               | rows:0        |
+--------------+---------+------+---------------+---------------+
1 row in set (0.01 sec)

The function should deal with the boundary value correctly.

I guess it is not a bad idea to change the type of the point in this case.

Patch comes later