Inconsistent query result after changing the order of cte statements

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `h1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `position_date` date NOT NULL,
  `asset_id` varchar(32) DEFAULT NULL,
  `portfolio_code` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`,`position_date`) /*T![clustered_index] NONCLUSTERED */,
  UNIQUE KEY `uidx_posi_asset_balance_key` (`position_date`,`portfolio_code`,`asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30002
PARTITION BY RANGE COLUMNS(`position_date`)
(PARTITION `p202401` VALUES LESS THAN ('2024-02-01'));
create table h2 like h1;
insert into h1 values(1,'2024-01-01',1,1);
insert into h2 values(1,'2024-01-01',1,1);
analyze table h1;

Query A:

with assetBalance AS 
    (SELECT asset_id,
         portfolio_code
    FROM h1 pab
    WHERE pab.position_date = '2024-01-01' ), 
cashBalance AS 
    (SELECT portfolio_code,
         asset_id
    FROM h2 pcb
    WHERE pcb.position_date = '2024-01-01' ), 
assetIdList AS 
    (SELECT DISTINCT asset_id AS assetId
    FROM assetBalance )
SELECT main.portfolioCode
FROM 
    (SELECT DISTINCT balance.portfolio_code AS portfolioCode
    FROM assetBalance balance
    LEFT JOIN assetIdList
        ON balance.asset_id = assetIdList.assetId ) main;

Query B:

with cashBalance AS 
    (SELECT portfolio_code,
         asset_id
    FROM h2 pcb
    WHERE pcb.position_date = '2024-01-01' ), 
assetBalance AS 
    (SELECT asset_id,
         portfolio_code
    FROM h1 pab
    WHERE pab.position_date = '2024-01-01' ), 
assetIdList AS 
    (SELECT DISTINCT asset_id AS assetId
    FROM assetBalance )
SELECT main.portfolioCode
FROM 
    (SELECT DISTINCT balance.portfolio_code AS portfolioCode
    FROM assetBalance balance
    LEFT JOIN assetIdList
        ON balance.asset_id = assetIdList.assetId ) main;

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

The same result for Query A and Query B.

3. What did you see instead (Required)

Query A:
Empty set, 1 warning (0.00 sec)

Query B:
+---------------+
| portfolioCode |
+---------------+
| 1 |
+---------------+
1 row in set, 1 warning (0.01 sec)

The warning shows disable dynamic pruning due to h2 has no global stats, and after collecting global stats of table h2, the result of Query A is the same as Query B.

4. What is your TiDB version? (Required)

v6.5.3

[qw4990]

Reproduce this case on Master:

mysql> with assetBalance AS 
    ->     (SELECT asset_id,
    ->          portfolio_code
    ->     FROM h1 pab
    ->     WHERE pab.position_date = '2024-01-01' ), 
    -> cashBalance AS 
    ->     (SELECT portfolio_code,
    ->          asset_id
    ->     FROM h2 pcb
    ->     WHERE pcb.position_date = '2024-01-01' ), 
    -> assetIdList AS 
    ->     (SELECT DISTINCT asset_id AS assetId
    ->     FROM assetBalance )
    -> SELECT main.portfolioCode
    -> FROM 
    ->     (SELECT DISTINCT balance.portfolio_code AS portfolioCode
    ->     FROM assetBalance balance
    ->     LEFT JOIN assetIdList
    ->         ON balance.asset_id = assetIdList.assetId ) main;
+---------------+
| portfolioCode |
+---------------+
| 1             |
+---------------+
1 row in set (0.01 sec)

mysql> set @@tidb_skip_missing_partition_stats=0;
Query OK, 0 rows affected (0.00 sec)

mysql> with assetBalance AS 
    ->     (SELECT asset_id,
    ->          portfolio_code
    ->     FROM h1 pab
    ->     WHERE pab.position_date = '2024-01-01' ), 
    -> cashBalance AS 
    ->     (SELECT portfolio_code,
    ->          asset_id
    ->     FROM h2 pcb
    ->     WHERE pcb.position_date = '2024-01-01' ), 
    -> assetIdList AS 
    ->     (SELECT DISTINCT asset_id AS assetId
    ->     FROM assetBalance )
    -> SELECT main.portfolioCode
    -> FROM 
    ->     (SELECT DISTINCT balance.portfolio_code AS portfolioCode
    ->     FROM assetBalance balance
    ->     LEFT JOIN assetIdList
    ->         ON balance.asset_id = assetIdList.assetId ) main;
Empty set, 1 warning (0.01 sec)

[qw4990]

The partition pruning result under static mode is wrong:

[kennedy8312]

/type regression

[kennedy8312]

Regression Anaylsis
v5.4.0-v6.2.0 : Both query A and query B return 1.
v6.3.0-v6.5.0 : Both query A and query B return empty.
v6.6.0-v7.6.0 : Query A returns empty and query B returns 1.
Version v5.4.0 : Success
Version v6.0.0 : Success
Version v6.1.0 : Success
Version v6.2.0 : Success
Version v6.3.0 : Failure
Version v6.4.0 : Failure
Version v6.5.0 : Failure
Version v6.6.0 : Failure
Version v7.0.0 : Failure
Version v7.1.0 : Failure
Version v7.2.0 : Failure
Version v7.3.0 : Failure
Version v7.4.0 : Failure
Version v7.5.0 : Failure
Version v7.6.0 : Failure
Version nightly : Success

[kennedy8312]

Regression Analysis
PR caused this regression: #37712
Query B returns 1 and query A returns empty starting from #40402.

[seiya-annie]

/found customer