index merge AND case will have redundant partial paths

[AilinKid]

Enhancement

mysql> explain format="verbose" SELECT  /*+ nth_plan(5) */ * FROM   items WHERE   15975127 member of (feed_profile_ids)   AND "OC8p0106XTkt.org/s/link" member of (normalized_short_link_clusters)   AND json_overlaps(     product_sources,     '["FEED_MERCHANT","MERCHANT_UI","CAROUSEL_CRAWLER"]'   ) LIMIT   10;
+------------------------------------+-----------+--------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows   | estCost      | task      | access object                                                                                                                        | operator info                                                                                                                                                              |
+------------------------------------+-----------+--------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Limit_10                           | 0.01      | 46166433.47  | root      |                                                                                                                                      | offset:0, count:10                                                                                                                                                         |
| └─Selection_11                     | 0.01      | 46166433.47  | root      |                                                                                                                                      | json_overlaps(test.items.product_sources, cast("["FEED_MERCHANT","MERCHANT_UI","CAROUSEL_CRAWLER"]", json BINARY))                                                         |
|   └─IndexMerge_31                  | 0.00      | 46166433.47  | root      |                                                                                                                                      | type: intersection                                                                                                                                                         |
|     ├─IndexRangeScan_27(Build)     | 8.62      | 2230.43      | cop[tikv] | table:items, index:feed_profile_ids(cast(`feed_profile_ids` as unsigned array))                                                      | range:[15975127,15975127], keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized]                                   |
|     ├─IndexRangeScan_28(Build)     | 500511.00 | 157143935.35 | cop[tikv] | table:items, index:normalized_short_link_clusters_old(cast(`normalized_short_link_clusters` as char(1000) array))                    | range:["OC8p0106XTkt.org/s/link","OC8p0106XTkt.org/s/link"], keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized] |
|     ├─IndexRangeScan_29(Build)     | 100608.05 | 31839295.38  | cop[tikv] | table:items, index:normalized_short_link_clusters(cast(`normalized_short_link_clusters` as char(1000) array), point_of_sale_country) | range:["OC8p0106XTkt.org/s/link","OC8p0106XTkt.org/s/link"], keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized] |
|     └─TableRowIDScan_30(Probe)     | 0.00      | 0.00         | cop[tikv] | table:items                                                                                                                          | keep order:false, stats:partial[normalized_short_link_clusters:unInitialized, feed_profile_ids:unInitialized]                                                              |
+------------------------------------+-----------+--------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set, 40 warnings (0.07 sec)

let's look at ─IndexRangeScan_28(Build) and ─IndexRangeScan_29(Build) they have the same index range based on similar index info normalized_short_link_clusters_old and normalized_short_link_clusters, derived from same accessFilter "OC8p0106XTkt.org/s/link" member of (normalized_short_link_clusters), which means one of them is not necessary.