optimizer does not consider using ordered index scan when `is null` is used

[mzhang77]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `testnull` (
       `object_id` bigint(20) unsigned NOT NULL,
       `a` bigint(20) DEFAULT NULL,
       `b` bigint(20) DEFAULT NULL,
       PRIMARY KEY (`object_id`) /*T![clustered_index] CLUSTERED */,
       KEY `ab` (`a`,`b`)
);

explain select * from testnull where a is null order by b;

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

execution plan use index ab and keep order on b to avoid sorting

3. What did you see instead (Required)

mysql> explain select * from testnull where a is null order by b;
+--------------------------+---------+-----------+--------------------------------+---------------------------------------------------+
| id                       | estRows | task      | access object                  | operator info                                     |
+--------------------------+---------+-----------+--------------------------------+---------------------------------------------------+
| Sort_5                   | 10.00   | root      |                                | test.testnull.b                                   |
| └─IndexReader_9          | 10.00   | root      |                                | index:IndexRangeScan_8                            |
|   └─IndexRangeScan_8     | 10.00   | cop[tikv] | table:testnull, index:ab(a, b) | range:[NULL,NULL], keep order:false, stats:pseudo |
+--------------------------+---------+-----------+--------------------------------+---------------------------------------------------+
3 rows in set (0.01 sec)

mysql> explain select /*+ order_index(testnull, ab) */ * from testnull where a is null order by b;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query

4. What is your TiDB version? (Required)

mysql> select @@version\g;
+--------------------+
| @@version          |
+--------------------+
| 8.0.11-TiDB-v8.1.0 |
+--------------------+
1 row in set (0.00 sec)

[winoros]

ISNULL is not treated as point range currently.

[seiya-annie]

/found customer