planner: bindings for in (?) can not match in (?, ... ?)

[qw4990]

Enhancement

See the case below, the binding for ... a in (1, 2, 3) can not match queries with only 1 element in the in-list ... a in (1) :

mysql> create table t (a int);
Query OK, 0 rows affected (0.09 sec)

mysql> create binding for select * from t where a in(1, 2, 3) using select * from t where a in (1, 2, 3);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t where a in (1, 2,3);
Empty set (0.01 sec)

mysql> select @@last_plan_from_binding;
+--------------------------+
| @@last_plan_from_binding |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t where a in (1, 2);
Empty set (0.01 sec)

mysql> select @@last_plan_from_binding;
+--------------------------+
| @@last_plan_from_binding |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t where a in (1);
Empty set (0.00 sec)

mysql> select @@last_plan_from_binding;
+--------------------------+
| @@last_plan_from_binding |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

[IAmUnbounded]

Can I give this a try ?

[isabella0428]

/assign

[qw4990]

Another issue needed to be solve:

mysql> create binding for select * from t where a in (1, 2, 3) using select * from t where a in (1);
ERROR 1105 (HY000): hinted sql and origin sql don't match when hinted sql erase the hint info, after erase hint info, originSQL:select * from `test` . `t` where `a` in ( ... ), hintedSQL:select * from `test` . `t` where `a` in ( ? )

[qw4990]

A new issue:

create binding for select * from t where a in (1) using select * from t where a in (1);
drop binding for select * from t where a in (1, 2, 3);
show bindings;

[qw4990]

A small test plan for this issue:

• Bindings for in (?) can work for in (?, ?, ?)
• Bindings for in (?, ?, ?) can work for in (?)
• Bindings with multiple in-lists, e.g. a in (?) and b in (?) can work for a in (?,?,?) and b in (?,?,?)
• Create bindings with in (?) and in (?,?,?) and only 1 bindings will be left
• create binding for ... in (?) using ... in (?, ?, ?) can succeed
• create binding for ... in (?, ?, ?) using ... in (?) can succeed
• Create binding with in(?) or in(?,?,?) and then drop binding by using in (?,?,?) or in(?)
• Create binding with in(?) or in(?,?,?) and then set binding status by using in (?,?,?) or in(?)
• Create fast binding from in (?) and it can work for in (?,?,?)
• Create a binding in (?) on an old TiDB and then upgrade it, and then this binding can work for in (?) and in (?,?,?)
• Create a binding in (?,?,?) on an old TiDB and then upgrade it, and then this binding can work for in (?) and in (?,?,?)