Can't find XXX in schema Column (CTE with order by)

[bindsang]

Bug Report

1. Minimal reproduce step (Required)

create table t1 (id bigint, is_removed boolean, year int, status varchar(50), primary key (id));

/* execute query sql */ 
with main as (select * from t1 where is_removed = false), 
min_not_submitted as (select id from main where status = 'unsubmitted' order by year limit 1),
max_submitted as (select max(id) id from main)
select 
  main.id = min_not_submitted.id AS is_submit,
  if(main.id = max_submitted.id and main.status='submitted', true, false) as is_revoke
from main left join min_not_submitted on 1=1 left join max_submitted on 1=1

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

return with no error

3. What did you see instead (Required)

receive an unexcepted error

Query: with main as (select * from t1 where is_removed = false), min_not_submitted as (select id from main where status = 'unsubmitted'...

Error Code: 1105
Can't find column test.t1.id in schema Column: [test.t1.id,test.t1.status,Column#30] Unique key: []

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.004 sec

the stack is:

Can't find column test.t1.id in schema Column: [test.t1.id,test.t1.status,Column#30] Unique key: []
github.com/pingcap/tidb/pkg/expression.(*Column).resolveIndices
	/root/workspace/github/tidb/pkg/expression/column.go:686
github.com/pingcap/tidb/pkg/expression.(*ScalarFunction).resolveIndices
	/root/workspace/github/tidb/pkg/expression/scalar_function.go:749
github.com/pingcap/tidb/pkg/expression.(*ScalarFunction).ResolveIndices
	/root/workspace/github/tidb/pkg/expression/scalar_function.go:743
github.com/pingcap/tidb/pkg/planner/core.(*PhysicalProjection).ResolveIndicesItself
	/root/workspace/github/tidb/pkg/planner/core/resolve_indices.go:29
github.com/pingcap/tidb/pkg/planner/core.(*PhysicalProjection).ResolveIndices
	/root/workspace/github/tidb/pkg/planner/core/resolve_indices.go:48
github.com/pingcap/tidb/pkg/planner/core.physicalOptimize
	/root/workspace/github/tidb/pkg/planner/core/optimizer.go:1059
github.com/pingcap/tidb/pkg/planner/core.doOptimize
	/root/workspace/github/tidb/pkg/planner/core/optimizer.go:271
github.com/pingcap/tidb/pkg/planner/core.DoOptimize
	/root/workspace/github/tidb/pkg/planner/core/optimizer.go:318
  ......

---

BUT when I remove order by clause or replace order by field 'year' to 'id', the result is successful

4. What is your TiDB version? (Required)

Release Version: v8.5.1
Edition: Community
Git Commit Hash: fea86c8
Git Branch: HEAD
UTC Build Time: 2025-01-16 07:38:34
GoVersion: go1.23.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[hawkingrei]

if we transfer this sql into non-cte style, it still has problem.

SELECT 
  t1.id = min_not_submitted.id AS is_submit,
  IF(t1.id = max_submitted.id AND t1.status = 'submitted', TRUE, FALSE) AS is_revoke
FROM 
  t1
  LEFT JOIN (
    SELECT id 
    FROM t1 
    WHERE is_removed = false AND status = 'unsubmitted' 
    ORDER BY year 
    LIMIT 1
  ) AS min_not_submitted ON 1=1
  LEFT JOIN (
    SELECT MAX(id) AS id 
    FROM t1 
    WHERE is_removed = false
  ) AS max_submitted ON 1=1
WHERE 
  t1.is_removed = false;

[hawkingrei]

simple sql again.

SELECT 
  t1.id = min_not_submitted.id AS is_submit
FROM 
  t1
  LEFT JOIN (
    SELECT id 
    FROM t1 
    ORDER BY year 
  ) AS min_not_submitted ON 1=1
  LEFT JOIN (
    SELECT MAX(id) AS id 
    FROM t1 
  ) AS max_submitted ON 1=1

[hawkingrei]

func TestIssue59145(t *testing.T) {
	store := testkit.CreateMockStore(t)
	tk := testkit.NewTestKit(t, store)
	tk.MustExec("use test;")
	tk.MustExec("create table t1 (id bigint, is_removed boolean, year int, status varchar(50), primary key (id));")
	tk.MustExec(`
SELECT
  t1.id = min_not_submitted.id AS is_submit
FROM
  t1
  LEFT JOIN (
    SELECT id
    FROM t1
    ORDER BY year
  ) AS min_not_submitted ON 1=1
  LEFT JOIN (
    SELECT MAX(id) AS id
    FROM t1
  ) AS max_submitted ON 1=1
`)
}