[fix](mtmv) Fix getting related partition table wrongly when multi base partition table exists

[seawinde]

Proposed changes

Fix getting related partition table wrongly when multi base partition table exists such as base table def is as following:

CREATE TABLE `test1` (
`pre_batch_no` VARCHAR(100) NULL COMMENT 'pre_batch_no',
`batch_no` VARCHAR(100) NULL COMMENT 'batch_no',
`vin_type1` VARCHAR(50) NULL COMMENT 'vin',
`upgrade_day` date COMMENT 'upgrade_day'
) ENGINE=OLAP
unique KEY(`pre_batch_no`,`batch_no`, `vin_type1`, `upgrade_day`)
COMMENT 'OLAP'
PARTITION BY RANGE(`upgrade_day`)
(
FROM ("2024-03-20") TO ("2024-03-31") INTERVAL 1 DAY
)
DISTRIBUTED BY HASH(`vin_type1`) BUCKETS 10
PROPERTIES (
       "replication_num" = "1"
);

CREATE TABLE `test2` (
`batch_no` VARCHAR(100) NULL COMMENT 'batch_no',
`vin_type2` VARCHAR(50) NULL COMMENT 'vin',
`status` VARCHAR(50) COMMENT 'status',
`upgrade_day` date  not null COMMENT 'upgrade_day' 
) ENGINE=OLAP
Duplicate KEY(`batch_no`,`vin_type2`)
COMMENT 'OLAP'
PARTITION BY RANGE(`upgrade_day`)
(
FROM ("2024-01-01") TO ("2024-01-10") INTERVAL 1 DAY
)
DISTRIBUTED BY HASH(`vin_type2`) BUCKETS 10
PROPERTIES (
       "replication_num" = "1"
);

if you create partition mv as following it will be successful

select 
  t1.upgrade_day, 
  t1.batch_no, 
  t1.vin_type1 
from 
  (
    SELECT 
      batch_no, 
      vin_type1, 
      upgrade_day 
    FROM 
      test1 
    where 
      batch_no like 'c%' 
    group by 
      batch_no, 
      vin_type1, 
      upgrade_day
  ) t1 
  left join (
    select 
      batch_no, 
      vin_type2, 
      status 
    from 
      test2 
    group by 
      batch_no, 
      vin_type2, 
      status
  ) t2 on t1.vin_type1 = t2.vin_type2;

Further comments

If this is a relatively large or complex change, kick off the discussion at [email protected] by explaining why you chose the solution you did and what alternatives you considered, etc...