planner: the optimizer returns 'cannot find column' error when aggregating view with json columns

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t (a int);
Query OK, 0 rows affected (0.03 sec)

mysql> create or replace algorithm=undefined view v (a,b) AS
    ->   select 1 as a, json_object('k', '0') as b from t;
Query OK, 0 rows affected (0.05 sec)

mysql> select sum(json_extract(b, '$.path')) from v group by a;
ERROR 1105 (HY000): Can't find column Column#4 in schema Column: [test.t._tidb_rowid] Unique key: []

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

No error

3. What did you see instead (Required)

ERROR 1105 (HY000): Can't find column Column#4 in schema Column: [test.t._tidb_rowid] Unique key: []

4. What is your TiDB version? (Required)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.6
Edition: Community
Git Commit Hash: 8445821f105477d5e31de9d8451b7fbf35a02cd1
Git Branch: HEAD
UTC Build Time: 2024-02-02 04:13:50
GoVersion: go1.21.0
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5. Analysis

When pushing Agg down through Projection, the optimizer needs to replace all columns produced by the Projection to its original expression, but it forgets to check whether this replacement succeeded or failed.
In this case, the replacement fails due to some collation check on JSON expressions, but the optimizer still pushes the Agg, then meet this error:

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
It worked fine in v7.3.0.

[kennedy8312]

It started to fail in v7.4.0.

[winoros]

@qw4990 said that 6.5 also met this problem in the customer's env. So the labels are starting from 6.5

[kennedy8312]

Regression Analysis
It works fine in d426bcd.

[kennedy8312]

Regression Analysis
It fails in 6fd1924.

[kennedy8312]

Regression Analysis
PR caused this regression:45650

[kennedy8312]

Regression Analysis
The above regression detected was with the testcase described in this bug.
The original testcase with plan_replay loaded seems to be a regression happening between v6.5.0 and v6.6.0. It is highly likely to be introduced by PR 38826.

[kennedy8312]

Regression Analysis
PR caused this regression: #38826
Commit 920f019 : Success
Commit 00617c9 : Failure