TiFlash daily test failed after PR 58261 due to resolveColumnIndex failure

[yibin87]

Reproduce sqls:
test.txt
The last sql:

explain SELECT  ( 4, 5 )  IN ( SELECT 8 , 0 UNION  SELECT 8, 8 ) AS field1 FROM ( D AS table1 RIGHT OUTER JOIN V AS table2 ON (table2 . col_varchar_binary = table1 . col_varchar_binary_key  ) ) WHERE (   EXISTS ( SELECT   SUBQUERY2_t1 . col_decimal_key AS SUBQUERY2_field1 FROM K AS SUBQUERY2_t1    ) ) OR ( table1 . col_varchar_binary_key >= 'm' OR table1 . col_varchar_binary_key  IN ('m') )  GROUP BY field1;

Failed to compile due to the error:
Can't find column Column#81 in schema Column: [Column#142] PKOrUK: [] NullableUK: []

Use the method of bisection and locate that the issue is introduced by #58261

[yibin87]

/cc @ghazalfamilyusa

[yibin87]

/sig planner

[ti-chi-bot]

@yibin87: The label(s) component/planner cannot be applied, because the repository doesn't have them.

In response to this:

/component planner

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[ghazalfamilyusa]

Problem can be re-produced by test below

drop table if exists t1;
create table t1 (a1 int, b1 int);

explain
SELECT (4,5) IN (SELECT 8,0 UNION SELECT 8, 8) AS field1
FROM t1 AS table1
WHERE (EXISTS (SELECT SUBQUERY2_t1.a1 AS SUBQUERY2_field1 FROM t1 AS SUBQUERY2_t1)) OR table1.b1 >= 55
GROUP BY field1;

insert into t1 values(1,1);

explain
SELECT (4,5) IN (SELECT 8,0 UNION SELECT 8, 8) AS field1
FROM t1 AS table1
WHERE (EXISTS (SELECT SUBQUERY2_t1.a1 AS SUBQUERY2_field1 FROM t1 AS SUBQUERY2_t1)) OR table1.b1 >= 55
GROUP BY field1;

[ghazalfamilyusa]

We need to tighten the check for the pattern in #58261.
In this case, the Apply operator carries a projection for some constant expression which is weird but seems possible. The projection is needed for upstream and removing Apply cause a problem. The fix can be:

• Make sure the schema of select does not need any column in apply which is not in the child of apply.
• A more general solution is to replace redundant Apply with Projection on top of left child. That could fit in the join elimination code.

P.S : I am travelling today and will not have time to fix the issue. I asked @winoros or @AilinKid if they can fix it. cc @fixdb