Support constant substitution on read-only user-define variables

[tangenta]

Feature Request

Is your feature request related to a problem? Please describe:

Table definition:

drop table if exists t;
create table t (added_time datetime);

-- query 1
SET @startTime = '2021-04-07 16:00:00';
SET @endTime = '2021-04-08 16:00:00';
explain select count(1) AS total, 0 AS now from t where added_time >= @startTime and added_time < @endTime;

-- query 2
explain select count(1) AS total, 0 AS now from t where added_time >= '2021-04-07 16:00:00' and added_time < '2021-04-08 16:00:00';

Query 1 result:

+-----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                                                                                                            |
+-----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_5                | 1.00     | root      |               | Column#3, 0->Column#4                                                                                                                    |
| └─HashAgg_6                 | 1.00     | root      |               | funcs:count(1)->Column#3                                                                                                                 |
|   └─Selection_8             | 8000.00  | root      |               | ge(test.t.added_time, cast(getvar("starttime"), datetime(6) BINARY)), lt(test.t.added_time, cast(getvar("endtime"), datetime(6) BINARY)) |
|     └─TableReader_10        | 10000.00 | root      |               | data:TableFullScan_9                                                                                                                     |
|       └─TableFullScan_9     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                                                           |
+-----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------+

Query 2 result:

+--------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------+
| id                             | estRows  | task      | access object | operator info                                                                                        |
+--------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------+
| Projection_5                   | 1.00     | root      |               | Column#3, 0->Column#4                                                                                |
| └─StreamAgg_20                 | 1.00     | root      |               | funcs:count(Column#7)->Column#3                                                                      |
|   └─TableReader_21             | 1.00     | root      |               | data:StreamAgg_9                                                                                     |
|     └─StreamAgg_9              | 1.00     | cop[tikv] |               | funcs:count(1)->Column#7                                                                             |
|       └─Selection_19           | 250.00   | cop[tikv] |               | ge(test.t.added_time, 2021-04-07 16:00:00.000000), lt(test.t.added_time, 2021-04-08 16:00:00.000000) |
|         └─TableFullScan_18     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                       |
+--------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------+

It shows that query 1 Selection operator cannot be pushed down to the TiKV, because the user-define variables cannot be pushed down.

Describe the feature you'd like:

Since the user-defined variables are read-only in this query, there is no way to change them in the same session during statement execution. We can safely substitute them with their value early to apply predicate-push-down optimization.

Describe alternatives you've considered:

• Use SQL template in the application code.
• Use prepare ? statements instead.

Teachability, Documentation, Adoption, Migration Strategy:

This brings less confusion to users.

Note: This can be done in the preprocessing stage. However, some corner cases need to be considered, for example, prepare plan cache should ignore these substituted statements because the values may change between different statements.