enhance range calculation upon user variables

[laizhebujum]

Description

Compare two sqls
1、
SET @execDate = DATE_FORMAT(NOW(),'%Y-%m-%d');
SET @start_time = DATE_ADD(NOW(), INTERVAL -1 DAY);
SET @end_time = @execDate;
desc SELECT count(*) FROM GameActiveLogDB.wendao_active_log WHERE active_time >= @Start_time AND active_time < @End_time;

2、desc SELECT count(*) FROM GameActiveLogDB.wendao_active_log WHERE active_time >= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY),'%Y-%m-%d') AND active_time < DATE_FORMAT(NOW(),'%Y-%m-%d');

Score

900

SIG slack channel（must）:

Contact us in channel #sig-planner of TiDB Community

Mentor(must)

• @lzmhhh123

[lysu]

Thanks for your feedback, we will take a look @laizhebujum

[zz-jason]

confirmed that this problem still exists in release 4.0 and the latest master branch at this time:

TiDB(root@127.0.0.1:test) > create table t(a bigint, b bigint, index idx(a));
Query OK, 0 rows affected (0.00 sec)

TiDB(root@127.0.0.1:test) > desc select * from t where a > 1 and a < 10;
+-------------------------------+---------+-----------+----------------------------------------------------------------+
| id                            | estRows | task      | operator info                                                  |
+-------------------------------+---------+-----------+----------------------------------------------------------------+
| IndexLookUp_10                | 250.00  | root      |                                                                |
| ├─IndexRangeScan_8(Build)     | 250.00  | cop[tikv] | table:t, index:a, range:(1,10), keep order:false, stats:pseudo |
| └─TableRowIDScan_9(Probe)     | 250.00  | cop[tikv] | table:t, keep order:false, stats:pseudo                        |
+-------------------------------+---------+-----------+----------------------------------------------------------------+
3 rows in set (0.00 sec)

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

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

TiDB(root@127.0.0.1:test) > desc select * from t where a > @a and a < @b;
+-------------------------+----------+-----------+------------------------------------------------------------------------------+
| id                      | estRows  | task      | operator info                                                                |
+-------------------------+----------+-----------+------------------------------------------------------------------------------+
| Selection_5             | 8000.00  | root      | gt(cast(test.t.a), cast(getvar("a"))), lt(cast(test.t.a), cast(getvar("b"))) |
| └─TableReader_7         | 10000.00 | root      | data:TableFullScan_6                                                         |
|   └─TableFullScan_6     | 10000.00 | cop[tikv] | table:t, keep order:false, stats:pseudo                                      |
+-------------------------+----------+-----------+------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

[eurekaka]

This is because GetVar in the filter is not folded into a constant, so ranger cannot extract it as access condition and build range for it. Currently, GetVar is marked as unFoldableFunctions on purpose, to handle the cases like:

select @a := @a +1 from t, (select @a := 0) var;

We can only fold the GetVar into a constant if the query contains no SetVar for the same user variable. We'd better try to optimize this after #18973 is merged.

[asiafrank]

/pick-up