Unexpected multiple join result

[TheoristCoder]

Bug Report

Dear Developers,
I meet some unexpected result, and I analyze the root case. The optimizer wrongly choose inner join when handling left join in this query.

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
CREATE TABLE t2(c0 INT);
CREATE TABLE t3(c0 INT);
INSERT INTO t0 VALUES(0);
INSERT INTO t3 VALUES(3);

SELECT *
FROM t0
         LEFT JOIN (SELECT NULL AS col_2
                    FROM t2) as subQuery1
                   ON true
         INNER JOIN t3 ON (((((CASE 1
                                   WHEN subQuery1.col_2 THEN t3.c0
                                   ELSE NULL END)) AND (((t0.c0))))) < 1);
-- empty result, wrong result
-- expected result: 0, NULL, 3

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

expected result: 0, NULL, 3

3. What did you see instead (Required)

empty result

Additionally, we can see from the query plan that the optimizer select a inner join algorithm (CARTESIAN inner join) for left join evaluation.

Projection_13,10000.00,root,"","database15.t0.c0, Column#5, database15.t3.c0"
└─HashJoin_15,10000.00,root,"","CARTESIAN inner join, other cond:lt(and(case(eq(1, cast(Column#5, double BINARY)), database15.t3.c0, NULL), database15.t0.c0), 1)"
  ├─HashJoin_16(Build),1.00,root,"",CARTESIAN inner join
  │ ├─TableReader_21(Build),1.00,root,"",data:TableFullScan_20
  │ │ └─TableFullScan_20,1.00,cop[tikv],table:t3,"keep order:false, stats:pseudo"
  │ └─TableReader_19(Probe),1.00,root,"",data:TableFullScan_18
  │   └─TableFullScan_18,1.00,cop[tikv],table:t0,"keep order:false, stats:pseudo"
  └─Projection_22(Probe),10000.00,root,"",<nil>->Column#5
    └─TableReader_24,10000.00,root,"",data:TableFullScan_23
      └─TableFullScan_23,10000.00,cop[tikv],table:t2,"keep order:false, stats:pseudo"

4. What is your TiDB version? (Required)

Release Version: v9.0.0-beta.1.pre-801-gd7d62ce0d2
Edition: Community
Git Commit Hash: d7d62ce0d26d15b902361010aab2526a4e2c511f
Git Branch: HEAD
UTC Build Time: 2025-05-25 04:45:44
GoVersion: go1.23.9
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[TheoristCoder]

/label affects-9.0

[TheoristCoder]

/label affects-8.5

[TheoristCoder]

I also try this test case on the version of 7.5 and 6.5, and it also produce wrong result. @hawkingrei