incorrect plan produced by cascades planner when range is empty

[zz-jason]

Let's say we have the following table:

drop table if exists t;
create table t(a decimal primary key);

The following query leads to an empty range, which indicates no data will be read from the storage:

TiDB(localhost:4000) > desc select * from t where a>5 and a<3;
+-------------------+------+------------------------------------+-------+
| id                | task | operator info                      | count |
+-------------------+------+------------------------------------+-------+
| IndexReader_9     | root | index:IndexScan_8                  | 0.00  |
| └─IndexScan_8     | cop  | table:t, index:a, keep order:false | 0.00  |
+-------------------+------+------------------------------------+-------+
2 rows in set (0.00 sec)

We can replace the whole operator DAG with a TableDual to further reduce the execution overhead.

For further optimization, we can add a rule which operates on TableDual to remove the unnecessary execution overhead. For example, we can replace an inner join with a TableDual when one of its child is a TableDual, that is to say, the following DAG can be optimized to a single TableDual operator as well:

TiDB(localhost:4000) > desc select * from t join t1 where t.a>5 and t.a<3;
+------------------------+------+-----------------------------------------------+--------------+
| id                     | task | operator info                                 | count        |
+------------------------+------+-----------------------------------------------+--------------+
| HashRightJoin_9        | root | inner join, inner:IndexReader_14              | 100000000.00 |
| ├─IndexReader_14       | root | index:IndexScan_13                            | 0.00         |
| │ └─IndexScan_13       | cop  | table:t, index:a, keep order:false            | 0.00         |
| └─TableReader_16       | root | data:TableScan_15                             | 10000.00     |
|   └─TableScan_15       | cop  | table:t1, range:[-inf,+inf], keep order:false | 10000.00     |
+------------------------+------+-----------------------------------------------+--------------+
5 rows in set (0.00 sec)

[shenli]

We have a related optimization here. Doesn't it work?

[winoros]

@shenli It works but not enough. It's range may become nil in future optimization.

[zz-jason]

confirmed that this issue has been improved:

TiDB(root@127.0.0.1:test) > desc select * from t where a>5 and a<3;
+-------------+---------+------+---------------+
| id          | estRows | task | operator info |
+-------------+---------+------+---------------+
| TableDual_5 | 0.00    | root | rows:0        |
+-------------+---------+------+---------------+
1 row in set (0.00 sec)

[zz-jason]

But the execution plan is incorrect when cascades planner is enabled:

TiDB(root@127.0.0.1:test) > set @@tidb_enable_cascades_planner = 1;
Query OK, 0 rows affected (0.00 sec)

TiDB(root@127.0.0.1:test) > desc select * from t where a>5 and a<3;
+------------------------+---------+-----------+--------------------------------------------------+
| id                     | estRows | task      | operator info                                    |
+------------------------+---------+-----------+--------------------------------------------------+
| IndexReader_14         | 8000.00 | root      | index:IndexFullScan_15                           |
| └─IndexFullScan_15     | 0.00    | cop[tikv] | table:t, index:a, keep order:false, stats:pseudo |
+------------------------+---------+-----------+--------------------------------------------------+
2 rows in set (0.00 sec)

[lzmhhh123]

confirm it has been supported:

tidb(127.0.0.1:4000) > desc select * from t where a>5 and a<3;
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_5 | 0.00    | root |               | rows:0        |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)

[ti-srebot]

Please edit this comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA)

2. Symptom

3. All Trigger Conditions

4. Workaround (optional)

5. Affected versions

6. Fixed versions