CTE hang or result wrong when multiple Apply is used

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
``` sql
use test;
drop table if exists table_a, table_b, table_c, table_d, table_e;
CREATE TABLE `table_a` (
  `col_1` varchar(40) DEFAULT NULL,
  `col_2` varchar(40) DEFAULT NULL,
  `col_3` varchar(500) DEFAULT NULL,
  `col_4` varchar(500) DEFAULT NULL,
  `col_5` varchar(500) DEFAULT NULL,
  `col_6` varchar(500) DEFAULT NULL,
  `col_7` decimal(38,6) DEFAULT NULL,
  `col_8` decimal(38,6) DEFAULT NULL,
  `col_9` decimal(38,6) DEFAULT NULL,
  `col_10` decimal(38,6) DEFAULT NULL,
  `col_11` decimal(38,6) DEFAULT NULL,
  `col_12` decimal(38,6) DEFAULT NULL,
  `col_13` decimal(38,6) DEFAULT NULL,
  `col_14` decimal(38,6) DEFAULT NULL,
  `col_15` decimal(38,6) DEFAULT NULL,
  `col_16` decimal(38,6) DEFAULT NULL,
  `col_17` decimal(38,6) DEFAULT NULL,
  `col_18` decimal(38,6) DEFAULT NULL,
  `col_19` varchar(40) DEFAULT NULL,
  `col_20` varchar(100) DEFAULT NULL,
  `col_21` varchar(100) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  KEY `index_col_1` (`col_1`),
  KEY `index_col_2_3` (`col_2`,`col_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `table_b` (
  `col_1` varchar(8) NOT NULL,
  `col_2` varchar(100) NOT NULL,
  `col_3` varchar(200) DEFAULT NULL,
  `col_4` varchar(10) NOT NULL,
  `col_5` decimal(38,6) DEFAULT NULL,
  `col_6` decimal(38,6) DEFAULT NULL,
  `col_7` decimal(38,6) DEFAULT NULL,
  `col_8` decimal(38,6) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  KEY `index_col_1_2` (`col_1`,`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table_c` (
  `col_1` varchar(50) DEFAULT NULL,
  `col_2` varchar(50) DEFAULT NULL,
  `col_3` varchar(30) DEFAULT NULL,
  `col_4` varchar(100) DEFAULT NULL,
  `col_5` varchar(50) DEFAULT NULL,
  `col_6` varchar(100) DEFAULT NULL,
  `col_7` decimal(38,6) DEFAULT NULL,
  `col_8` decimal(38,6) DEFAULT NULL,
  `col_9` decimal(38,6) DEFAULT NULL,
  `col_10` decimal(38,6) DEFAULT NULL,
  `col_11` decimal(38,6) DEFAULT NULL,
  `col_12` decimal(38,6) DEFAULT NULL,
  `col_13` decimal(38,6) DEFAULT NULL,
  `col_14` decimal(38,6) DEFAULT NULL,
  `col_15` decimal(38,6) DEFAULT NULL,
  `col_16` decimal(38,6) DEFAULT NULL,
  `col_17` varchar(50) DEFAULT NULL,
  `col_18` varchar(50) DEFAULT NULL,
  `col_19` varchar(50) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  KEY `index_col_1_3` (`col_1`,`col_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table_d` (
  `col_1` decimal(10,0) DEFAULT NULL,
  `col_2` varchar(1) DEFAULT NULL,
  `col_3` date NOT NULL,
  `col_4` varchar(13) DEFAULT NULL,
  `col_5` varchar(6) DEFAULT NULL,
  `col_6` varchar(255) DEFAULT NULL,
  `col_7` decimal(10,0) DEFAULT NULL,
  `col_8` varchar(12) DEFAULT NULL,
  `col_9` decimal(10,0) DEFAULT NULL,
  `col_10` varchar(17) DEFAULT NULL,
  `col_11` decimal(10,0) DEFAULT NULL,
  `col_12` varchar(7) DEFAULT NULL,
  `col_13` date DEFAULT NULL,
  `col_14` date DEFAULT NULL,
  `col_15` date DEFAULT NULL,
  `col_16` date DEFAULT NULL,
  `col_17` date DEFAULT NULL,
  `col_18` date DEFAULT NULL,
  `col_19` date DEFAULT NULL,
  `col_20` varchar(1) DEFAULT NULL,
  `col_21` varchar(1) DEFAULT NULL,
  `col_22` decimal(1,0) DEFAULT NULL,
  `col_23` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table_e` (
  `col_1` varchar(8) NOT NULL,
  `col_2` varchar(100) NOT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  `col_4` varchar(100) NOT NULL,
  `col_5` varchar(100) DEFAULT NULL,
  `col_6` varchar(100) DEFAULT NULL,
  `col_7` decimal(38,12) DEFAULT NULL,
  `col_8` varchar(100) DEFAULT NULL,
  `col_9` varchar(100) DEFAULT NULL,
  `col_10` varchar(100) DEFAULT NULL,
  `col_11` varchar(100) DEFAULT NULL,
  `col_12` varchar(8) DEFAULT NULL,
  `col_13` decimal(38,12) DEFAULT NULL,
  `col_14` varchar(100) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `col_15` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`col_2`, `col_1`, `col_4`),
  KEY `index_col_5_6` (`col_5`, `col_6`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `table_a`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`,
 `col_14`, `col_15`, `col_16`, `col_17`, `col_18`, `col_19`,
 `col_20`, `col_21`, `created_at`)
VALUES
('20230628', '20230628', 'Portfolio A', 'Product B', 'Direct', 'USD',
 200000, 150000, 50000, 100000,
 50000, 10000, 5000, 1.2, 0.1,
 0.15, 0.08, 0.02, '2023-06-28',
 '2023-06-28', '2025-06-28', CURRENT_TIMESTAMP);

 INSERT INTO `table_b`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`, `col_8`, `created_at`)
VALUES
('20240628', 'DR201800093', 'Product A', '申购', 1000, 100000, 95000, 1.1, CURRENT_TIMESTAMP);

INSERT INTO `table_c`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`,
 `col_14`, `col_15`, `col_16`, `col_17`, `col_18`, `col_19`, `created_at`)
VALUES
('20230628', 'Dept A', 'DR201800093', 'Product A', '孵化', 'Strategy 1',
 100000, 100000, 120000, 100, 1.2,
 0.2, 0.15, 0.1, 0.05, 0.08,
 '2023-06-28', '2025-06-28', '2Y', CURRENT_TIMESTAMP);

INSERT INTO `table_d`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`, `col_14`,
 `col_15`, `col_16`, `col_17`, `col_18`, `col_19`, `col_20`, `col_21`,
 `col_22`, `col_23`)
VALUES
('20240628', '1', '2024-06-28', 'Friday', '28', 'End of Month', 202406,
 'June', 20242, 'Q2', 2024, '2024', '2024-06-27', '2024-05-28',
 '2024-03-28', '2023-06-28', '2024-06-27', '2024-06-27', '2024-06-27',
 '1', '1', '1', '1');

 INSERT INTO `table_e`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`, `col_14`,
 `created_at`, `col_15`)
VALUES
('20230628', 'CFETS_MID', 'Mid', 'USD/CNY', 'USD', 'CNY',
 7.0, 'Source A', 'Unit A', 'Region A', '2023-06-28 15:00:00', '20230627',
 6.9, 'user_001', CURRENT_TIMESTAMP, 'Exchange rate on 2023-06-28');

```

``` sql
use test;
desc analyze WITH date_table AS (
  SELECT
        d.col_1 AS date,
        (SELECT MAX(col_1)
            FROM table_c a
           WHERE col_1 <=
                 CONCAT(YEAR(DATE_SUB(d.col_1, INTERVAL 1 YEAR)),
                        '1231')
             AND EXISTS (SELECT 1
                    FROM table_d d
                   WHERE a.col_1 = d.col_1
                     AND d.col_2 = 1)) AS date1,
        (SELECT MAX(col_1)
            FROM table_a a
           WHERE col_1 <= CONCAT(YEAR(DATE_SUB(d.col_1, INTERVAL 1 YEAR)),
                                '1231')
             AND EXISTS (SELECT 1
                    FROM table_d d
                   WHERE a.col_1 = d.col_1
                     AND d.col_2 = 1)) AS date2,
        (SELECT MAX(col_1)
            FROM table_c
           WHERE col_1 <= d.col_1) AS date3,
        (SELECT MAX(col_1)
            FROM table_a
           WHERE col_1 <= d.col_1) AS date4
    FROM table_d d
   WHERE d.col_1 = '20240628'
),
rm_am_champs_ex_risk_portfolio_seed_money_1 AS (
  SELECT b.col_2
    FROM table_a b
    LEFT JOIN table_e rb
      ON rb.col_1 = b.col_19
     AND b.col_6 = rb.col_3
   WHERE b.col_2 = (SELECT date4 FROM date_table)
),

rm_am_champs_ex_risk_portfolio_seed_money_2 AS (
  SELECT b.col_2
  FROM table_a b
  LEFT JOIN table_e rb
      ON rb.col_1 = b.col_19
     AND b.col_6 = rb.col_3
),

product_base AS (
  SELECT DISTINCT t.col_3, col_4, 'ML' AS is_do
    FROM table_c t
),

product_detail AS (
  SELECT t.col_4,
        "3集合" AS nature_investment
    FROM product_base t
    LEFT JOIN date_table dt
      ON 1 = 1
    LEFT JOIN table_c a
      ON t.col_4 = a.col_4
     AND a.col_1 = dt.date3
)

SELECT col_4
  FROM (
        SELECT col_4
          FROM product_detail
        UNION ALL
        SELECT col_4
          FROM product_detail
) a;

```
### 2. What did you expect to see? (Required)
query execute successfully

### 3. What did you see instead (Required)
query hang
```
6 @ 0x189d5ae 0x18af1f8 0x18af1cf 0x18cdd85 0x18e25c8 0x428962b 0x41a6fe8 0x41a68ad 0x41bd22c 0x428978f 0x41c77bb 0x4119824 0x439b610 0x443bf96 0x1c97b65 0x442e9c2 0x442d115 0x44288c5 0x44251ba 0x445d6e7 0x18d2461
# labels: {"plan_digest":"rzj\xeeY\"\xaf\x1c\xd9K\a\xe1\x0f\xa3\x90\x18\x92\x8en1K\x1a\xfa\xf3\xe6\xce\x1d\x96\xb7\xe72\xc1", "sql_digest":"\xd9W\xa4\xd5=5O0\xc0\xa6^r\xec\x04\x92R^\xa3\x12\xef/\xfa2:\x8bd\xb9(\xad\x02\xb4\xe3"}
#	0x18cdd84	sync.runtime_Semacquire+0x24						/home/xhy/.gvm/gos/go1.21.5/src/runtime/sema.go:62
#	0x18e25c7	sync.(*WaitGroup).Wait+0x47						/home/xhy/.gvm/gos/go1.21.5/src/sync/waitgroup.go:116
#	0x428962a	github.com/pingcap/tidb/executor.(*ProjectionExec).Close+0x6a		/home/xhy/Development/github.com/pingcap/tidb/executor/projection.go:313
#	0x41a6fe7	github.com/pingcap/tidb/executor.(*cteProducer).closeProducer+0x27	/home/xhy/Development/github.com/pingcap/tidb/executor/cte.go:218
#	0x41a68ac	github.com/pingcap/tidb/executor.(*CTEExec).Close+0x4c			/home/xhy/Development/github.com/pingcap/tidb/executor/cte.go:119
#	0x41bd22b	github.com/pingcap/tidb/executor.(*baseExecutor).Close+0x6b		/home/xhy/Development/github.com/pingcap/tidb/executor/executor.go:214
#	0x428978e	github.com/pingcap/tidb/executor.(*ProjectionExec).Close+0x1ce		/home/xhy/Development/github.com/pingcap/tidb/executor/projection.go:334
#	0x41c77ba	github.com/pingcap/tidb/executor.(*UnionExec).Close+0x11a		/home/xhy/Development/github.com/pingcap/tidb/executor/executor.go:1936
#	0x4119823	github.com/pingcap/tidb/executor.(*recordSet).Close+0x23		/home/xhy/Development/github.com/pingcap/tidb/executor/adapter.go:184
#	0x439b60f	github.com/pingcap/tidb/session.(*execStmtResult).Close+0x2f		/home/xhy/Development/github.com/pingcap/tidb/session/session.go:2455
#	0x443bf95	github.com/pingcap/tidb/server.(*tidbResultSet).Close+0x35		/home/xhy/Development/github.com/pingcap/tidb/server/driver_tidb.go:476
#	0x1c97b64	github.com/pingcap/tidb/parser/terror.Call+0x24				/home/xhy/Development/github.com/pingcap/tidb/parser/terror/terror.go:306
#	0x442e9c1	github.com/pingcap/tidb/server.(*clientConn).handleStmt+0x461		/home/xhy/Development/github.com/pingcap/tidb/server/conn.go:2168
#	0x442d114	github.com/pingcap/tidb/server.(*clientConn).handleQuery+0x8f4		/home/xhy/Development/github.com/pingcap/tidb/server/conn.go:1985
#	0x44288c4	github.com/pingcap/tidb/server.(*clientConn).dispatch+0xf84		/home/xhy/Development/github.com/pingcap/tidb/server/conn.go:1416
#	0x44251b9	github.com/pingcap/tidb/server.(*clientConn).Run+0x239			/home/xhy/Development/github.com/pingcap/tidb/server/conn.go:1165
#	0x445d6e6	github.com/pingcap/tidb/server.(*Server).onConn+0xa86			/home/xhy/Development/github.com/pingcap/tidb/server/server.go:648 
```

### 4. What is your TiDB version? (Required)
v6.5.6


Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

CTE hang or result wrong when multiple Apply is used #55551

Bug Report

1. Minimal reproduce step (Required)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

CTE hang or result wrong when multiple Apply is used #55551

Description

Bug Report

1. Minimal reproduce step (Required)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions