planner: NullEQ cannot be used for composite indexes

[qw4990]

Enhancement

mysql> show create table t;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t where a<=>null and b=1;
+--------------------------+---------+-----------+------------------------+---------------------------------------------------+
| id                       | estRows | task      | access object          | operator info                                     |
+--------------------------+---------+-----------+------------------------+---------------------------------------------------+
| IndexReader_7            | 0.01    | root      |                        | index:Selection_6                                 |
| └─Selection_6            | 0.01    | cop[tikv] |                        | eq(test.t.b, 1)                                   |
|   └─IndexRangeScan_5     | 10.00   | cop[tikv] | table:t, index:a(a, b) | range:[NULL,NULL], keep order:false, stats:pseudo |
+--------------------------+---------+-----------+------------------------+---------------------------------------------------+
3 rows in set (0.00 sec)

The range should be [NULL 1, NULL 1].

[qw4990]

Please see https://github.com/pingcap/tidb/pull/19035/files#r468429550 for more details.

[qw4990]

The root cause is that TiKV can depend on the attributes unique and point to choose different storage access interfaces to speed up:

if Req.Index.IsUnique() && Req.Range.IsPoint() {
    store.Get(Req.Range.StartKey(), &results)
} else {
    store.Scan(Req.Range.Begin(), Range.End(), &results)
}

If the index is unique and the range is a point then it can assume that there is only 1 row to return and then it uses the faster interface Get instead of the general interface Scan.

But In MySQL, a unique index can have multiple duplicated records with NULL:

mysql> create table t (a int, b int, unique key(a, b));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (null, 1), (null, 1);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t;
+------+------+
| a    |  b   |
+------+------+
| NULL |  1   |
| NULL |  1   |
+------+------+
2 rows in set (0.00 sec)

So if the range has NULL, the 1 row assumption are broken.