planner: incorrect est-row of IndexMerge upon MVIndex

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a int, j json, index i_int((cast(j->'$.int' as signed array))));

select * from t where json_overlaps((j->'$.int'), '[1, 2, 3]');

          "Selection 8000.00 root  json_overlaps(json_extract(test.t.j, \"$.int\"), cast(\"[1, 2, 3]\", json BINARY))",
          "└─IndexMerge 10.00 root  type: union",
          "  ├─IndexRangeScan(Build) 10.00 cop[tikv] table:t, index:i_int(cast(json_extract(`j`, _utf8mb4'$.int') as signed array)) range:[1,1], keep order:false, stats:pseudo",
          "  ├─IndexRangeScan(Build) 10.00 cop[tikv] table:t, index:i_int(cast(json_extract(`j`, _utf8mb4'$.int') as signed array)) range:[2,2], keep order:false, stats:pseudo",
          "  ├─IndexRangeScan(Build) 10.00 cop[tikv] table:t, index:i_int(cast(json_extract(`j`, _utf8mb4'$.int') as signed array)) range:[3,3], keep order:false, stats:pseudo",
          "  └─TableRowIDScan(Probe) 10.00 cop[tikv] table:t keep order:false, stats:pseudo"

2. What did you expect to see? (Required)

The est-row of Selection shouldn't be larger than its child IndexMerge's 10.00.

3. What did you see instead (Required)

8000 > 10

4. What is your TiDB version? (Required)

Release Version: v6.6.0-alpha-150-g09a7bce24d-dirty
Edition: Community
Git Commit Hash: 09a7bce24d7ffe888f09f1c562d60f2a5f181827
Git Branch: mvindex-estimation
UTC Build Time: 2023-01-05 11:09:17
GoVersion: go1.19.1
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |

[qw4990]

I'll fix it later.

[qw4990]

Another case:

 CREATE TABLE `pi` (
  `a` varchar(100) DEFAULT NULL,
  `fid` varchar(100) DEFAULT NULL,
  `at` json DEFAULT NULL,
  `at._id` json DEFAULT NULL,
  `ab` json DEFAULT NULL,
  `ab._id` json DEFAULT NULL,

  `_id` varchar(100) NOT NULL,
  PRIMARY KEY (`_id`) /*T![clustered_index] NONCLUSTERED */,
  KEY `idx_at` ((cast(json_extract(`at`, _utf8mb4'$[*]._id') as char(100) array)))
);

EXPLAIN SELECT  * 
FROM pi
WHERE 
    JSON_OVERLAPS(at->'$[*]._id', JSON_ARRAY("User32", "flobot")) AND 
    NOT JSON_OVERLAPS(ab->'$[*]._id', JSON_ARRAY("User32", "flobot")) AND 
    pi.fid = 'PurchaseOrder' AND 
    pi.a != "";

+----------------------------------+---------+-----------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                               | estRows | task      | access object                                                                           | operator info                                                                                                                                                                                                                                                                   |
+----------------------------------+---------+-----------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Selection_5                      | 5.33    | root      |                                                                                         | json_overlaps(json_extract(largeaccount001.pi.at, "$[*]._id"), json_array(cast("User32", json BINARY), cast("flobot", json BINARY))), not(json_overlaps(json_extract(largeaccount001.pi.ab, "$[*]._id"), json_array(cast("User32", json BINARY), cast("flobot", json BINARY)))) |
| └─IndexMerge_13                  | 0.01    | root      |                                                                                         | type: union                                                                                                                                                                                                                                                                     |
|   ├─IndexRangeScan_9(Build)      | 10.00   | cop[tikv] | table:pi, index:idx_at(cast(json_extract(`at`, _utf8mb4'$[*]._id') as char(100) array)) | range:["User32","User32"], keep order:false, stats:pseudo                                                                                                                                                                                                                       |
|   ├─IndexRangeScan_10(Build)     | 10.00   | cop[tikv] | table:pi, index:idx_at(cast(json_extract(`at`, _utf8mb4'$[*]._id') as char(100) array)) | range:["flobot","flobot"], keep order:false, stats:pseudo                                                                                                                                                                                                                       |
|   └─Selection_12(Probe)          | 0.01    | cop[tikv] |                                                                                         | eq(largeaccount001.pi.fid, "PurchaseOrder"), ne(largeaccount001.pi.a, "")                                                                                                                                                                                                       |
|     └─TableRowIDScan_11          | 19.99   | cop[tikv] | table:pi                                                                                | keep order:false, stats:pseudo                                                                                                                                                                                                                                                  |
+----------------------------------+---------+-----------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The est-row of Selection_5 should be larger than the IndexMerge_13's.

[qw4990]

The reason is that the optimizer inserts a new Selection during the Physical Optimization Phase and updates the IndexMerge's est-row, but the est-row of the Selection above has already been calculated and fixed before the Physical Optimization Phase and the optimizer can't update the Selection's est-row again during Physical Optimization: