handle more than one 'equal' or 'in' function for a column in ranger

[zz-jason]

Take this as an example:

drop table if exists t;
create table t(a bigint, b bigint, index idx(a, b));

Now let's issue a query on column 'a':

TiDB(localhost:4000) > explain select * from t where a in (1, 2) and a in (1, 3);
+---------------------+-------+------+-------------------------------------------------------------------------+
| id                  | count | task | operator info                                                           |
+---------------------+-------+------+-------------------------------------------------------------------------+
| IndexReader_10      | 0.04  | root | index:Selection_9                                                       |
| └─Selection_9       | 0.04  | cop  | in(test.t.a, 1, 2)                                                      |
|   └─IndexScan_8     | 20.00 | cop  | table:t, index:a, b, range:[1,1], [3,3], keep order:false, stats:pseudo |
+---------------------+-------+------+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)

As you can see, this results in a IndexScan_8 followed by a Selection_9, and the query range in IndexScan_8 is [1,1], [3,3].

If we could handle more than one 'equal' or 'in' function for a column in the ranger, we can optimize the query to only one IndexScan with the query range [1, 1].

In "util/ranger/detacher.go", we have to enhance the extractEqAndInCondition function to break this limit.

[laidahe]

Seems like what we need to do is combining multiple “in” into single "in".

[zz-jason]

@laidahe Um.. yes, much like that. You can take a look at the code and do some investigation to decide the best solution.

[eurekaka]

some more queries to be verified for the solution of this issue:

MySQL [test]> explain select * from t where a = 1 and a = 1;
+---------------------+-------+------+------------------------------------------------------------------+
| id                  | count | task | operator info                                                    |
+---------------------+-------+------+------------------------------------------------------------------+
| IndexReader_10      | 10.00 | root | index:Selection_9                                                |
| └─Selection_9       | 10.00 | cop  | eq(test.t.a, 1)                                                  |
|   └─IndexScan_8     | 10.00 | cop  | table:t, index:a, b, range:[1,1], keep order:false, stats:pseudo |
+---------------------+-------+------+------------------------------------------------------------------+

MySQL [test]> explain select * from t where a = 1 and a = 2;
+---------------------+-------+------+------------------------------------------------------------------+
| id                  | count | task | operator info                                                    |
+---------------------+-------+------+------------------------------------------------------------------+
| IndexReader_10      | 0.01  | root | index:Selection_9                                                |
| └─Selection_9       | 0.01  | cop  | eq(test.t.a, 1)                                                  |
|   └─IndexScan_8     | 10.00 | cop  | table:t, index:a, b, range:[2,2], keep order:false, stats:pseudo |
+---------------------+-------+------+------------------------------------------------------------------+