planner: invalid column error when building IndexJoin for sub-query with multiple Agg

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE B (
  ROW_NO bigint NOT NULL AUTO_INCREMENT,
  RCRD_NO varchar(20) NOT NULL,
  FILE_NO varchar(20) DEFAULT NULL,
  BSTPRTFL_NO varchar(20) DEFAULT NULL,
  MDL_DT date DEFAULT NULL,
  TS varchar(19) DEFAULT NULL,
  LD varchar(19) DEFAULT NULL,
  MDL_NO varchar(50) DEFAULT NULL,
  TXN_NO varchar(90) DEFAULT NULL,
  SCR_NO varchar(20) DEFAULT NULL,
  DAM decimal(25, 8) DEFAULT NULL,
  DT date DEFAULT NULL,
  PRIMARY KEY (ROW_NO),
  KEY `IDX1_ETF_FLR_PRCHRDMP_TXN_DTL` (BSTPRTFL_NO, DT, MDL_DT, TS, LD),
  KEY `IDX2_ETF_FLR_PRCHRDMP_TXN_DTL` (BSTPRTFL_NO, MDL_DT, SCR_NO, TXN_NO),
  KEY `IDX1_ETF_FLR_PRCHRDMP_TXNDTL` (FILE_NO, BSTPRTFL_NO),
  KEY `IDX_ETF_FLR_PRCHRDMP_TXN_FIX` (MDL_NO, BSTPRTFL_NO, MDL_DT),
  UNIQUE `UI_ETF_FLR_PRCHRDMP_TXN_DTL TB` (RCRD_NO),
  KEY `IDX3_ETF_FLR_PRCHRDMP_TXN_DTL` (DT)
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE utf8mb4_bin AUTO_INCREMENT = 2085290754;


CREATE TABLE `A` (
  ROW_NO bigint NOT NULL AUTO_INCREMENT,
  TEMP_NO varchar(20) NOT NULL,
  VCHR_TPCD varchar(19) DEFAULT NULL,
  LD varchar(19) DEFAULT NULL,
  BSTPRTFL_NO varchar(20) DEFAULT NULL,
  DAM decimal(25, 8) DEFAULT NULL,
  DT date DEFAULT NULL,
  CASH_RPLC_AMT decimal(19, 2) DEFAULT NULL,
  PCSG_BTNO_NO varchar(20) DEFAULT NULL,
  KEY `INX_TEMP_NO` (`TEMP_NO`),
  PRIMARY KEY (ROW_NO),
  KEY `idx2_ETF_FNDTA_SALE_PA` (PCSG_BTNO_NO, DT, VCHR_TPCD)
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE utf8mb4_bin AUTO_INCREMENT = 900006;

set tidb_opt_index_join_build_v2=off; 

explain SELECT *
FROM A A
JOIN 
    (SELECT CASH_RPLC_AMT,
         S.BSTPRTFL_NO
    FROM 
        (SELECT BSTPRTFL_NO,
         SUM(CASE
            WHEN LD IN ('03') THEN
            DAM
            ELSE 0 END) AS CASH_RPLC_AMT
        FROM 
            (SELECT B.LD,
         SUM(B.DAM) DAM,
         B.BSTPRTFL_NO
            FROM B B
            GROUP BY  B.LD, B.BSTPRTFL_NO) ff
            GROUP BY  BSTPRTFL_NO) S ) f
            ON A.BSTPRTFL_NO = f.BSTPRTFL_NO
    WHERE A.PCSG_BTNO_NO = 'MXUU2022123043502318';

Root Cause:
1. An optimizer bug, when constructing the inner operators under an IndexJoin in constructIndexJoinInnerSideTaskWithAggCheck, we assume there is at most 1 Agg.
2. In this SQL with 2 nested sub-queries, there are 2 Agg under IndexJoin, which breaks our assumption, and causes some errors.

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

No error

3. What did you see instead (Required)

ERROR 1105 (HY000): Some columns of IndexJoin_20 cannot find the reference from its child(ren)

4. What is your TiDB version? (Required)

Master