optimizer can not derive ranges on multi-column index conditions like (a1,b1) >= (v1,v2) and (a2,b2) <= (v3,b4)

[ghazalfamilyusa]

Enhancement

This issue is covered in details under tikv/tikv#16798 which is listed under TiKV.
The enhancement here belongs to the optimizer and we created this issue to track it. Please refer to tikv/tikv#16798 for the details of the problem.

For completeness, see the first comment below repeated from the original PR.

[ghazalfamilyusa]

Q1 is ((a1>1) or (a1=1 and b1 > 10)) and we get the correct range:(1 10,1 +inf], (1,+inf]
Q2 is ((a1<10) or (a1=10 and b1 < 20)) and we also get the correct range:[-inf,10), [10 -inf,10 20)
Q3 is Q1 and Q2 combined. ((a1>1) or (a1=1 and b1 > 10)) and ((a1<10) or (a1=10 and b1 < 20)) and we get correct intersection but not optimal range:[1,1], (1,10), [10,10]
Correct result for Q3 is Q4 with range range:(1 10,1 +inf], (1,10), [10 -inf,10 20)

drop table if exists t1
create table t1 (a1 int, b1 int, c1 int, primary key pkx (a1,b1))

explain select /*+ USE_INDEX(t1,PKX) */ count(*) from t1 where  
   ((a1>1) or (a1=1 and b1 > 10))
--------------
Q1:
id      estRows task    access object   operator info
HashAgg_12      1.00    root            funcs:count(Column#5)->Column#4
└─TableReader_13        1.00    root            data:HashAgg_6
  └─HashAgg_6   1.00    cop[tikv]               funcs:count(1)->Column#5
    └─TableRangeScan_11 3366.67 cop[tikv]       table:t1        range:(1 10,1 +inf], (1,+inf], keep order:false, stats:pseudo

--------------
explain select /*+ USE_INDEX(t1,PKX) */ count(*) from t1 where
   ((a1<10) or (a1=10 and b1 < 20))
--------------
Q2
id      estRows task    access object   operator info
HashAgg_12      1.00    root            funcs:count(Column#5)->Column#4
└─TableReader_13        1.00    root            data:HashAgg_6
  └─HashAgg_6   1.00    cop[tikv]               funcs:count(1)->Column#5
    └─TableRangeScan_11 3356.57 cop[tikv]       table:t1        range:[-inf,10), [10 -inf,10 20), keep order:false, stats:pseudo

--------------
explain select /*+ USE_INDEX(t1,PKX) */ count(*) from t1 where
   ((a1>1) or (a1=1 and b1 > 10)) and ((a1<10) or (a1=10 and b1 < 20))
--------------
Q3
id      estRows task    access object   operator info
HashAgg_13      1.00    root            funcs:count(Column#5)->Column#4
└─TableReader_14        1.00    root            data:HashAgg_6
  └─HashAgg_6   1.00    cop[tikv]               funcs:count(1)->Column#5
    └─Selection_12      1122.61 cop[tikv]               or(gt(test.t1.a1, 1), and(eq(test.t1.a1, 1), gt(test.t1.b1, 10))), or(lt(test.t1.a1, 10), and(eq(test.t1.a1, 10), lt(test.t1.b1, 20)))
      └─TableRangeScan_11       1403.26 cop[tikv]       table:t1        range:[1,1], (1,10), [10,10], keep order:false, stats:pseudo

--------------
explain select /*+ USE_INDEX(t1,PKX) */ count(*) from t1 where
   (a1>1 and a1 < 10) or (a1=1 and b1>10) or (a1=10 and b1<20)
--------------
Q4
id      estRows task    access object   operator info
StreamAgg_17    1.00    root            funcs:count(Column#6)->Column#4
└─TableReader_18        1.00    root            data:StreamAgg_9
  └─StreamAgg_9 1.00    cop[tikv]               funcs:count(1)->Column#6
    └─TableRangeScan_16 316.57  cop[tikv]       table:t1        range:(1 10,1 +inf], (1,10), [10 -inf,10 20), keep order:false, stats:pseudo