Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
qw4990 opened this issue Feb 2, 2024 · 9 comments · Fixed by #50932
Labels
affects-6.5 affects-7.1 affects-7.5 needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. needs-cherry-pick-release-7.1 Should cherry pick this PR to release-7.1 branch. needs-cherry-pick-release-7.5 Should cherry pick this PR to release-7.5 branch. severity/moderate sig/planner SIG: Planner type/bug The issue is confirmed as a bug. type/regression

Comments

@qw4990
Copy link
Contributor

qw4990 commented Feb 2, 2024

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:

image
@qw4990 qw4990 added type/bug The issue is confirmed as a bug. sig/planner SIG: Planner severity/moderate needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. needs-cherry-pick-release-7.1 Should cherry pick this PR to release-7.1 branch. needs-cherry-pick-release-7.5 Should cherry pick this PR to release-7.5 branch. labels Feb 2, 2024
@kennedy8312
Copy link

/type regression

@kennedy8312
Copy link

Regression Analysis
It worked fine in v7.3.0.
owp4OJcnLn

@kennedy8312
Copy link

It started to fail in v7.4.0.
c835uwtElu

@winoros winoros added needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. needs-cherry-pick-release-7.1 Should cherry pick this PR to release-7.1 branch. and removed needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. needs-cherry-pick-release-7.1 Should cherry pick this PR to release-7.1 branch. labels Feb 5, 2024
@winoros
Copy link
Member

winoros commented Feb 5, 2024

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

@kennedy8312
Copy link

Regression Analysis
It works fine in d426bcd.
vTdl1gSZBR

@kennedy8312
Copy link

Regression Analysis
It fails in 6fd1924.
pZw79cUT7s

@kennedy8312
Copy link

Regression Analysis
PR caused this regression:45650

@kennedy8312
Copy link

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
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
affects-6.5 affects-7.1 affects-7.5 needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. needs-cherry-pick-release-7.1 Should cherry pick this PR to release-7.1 branch. needs-cherry-pick-release-7.5 Should cherry pick this PR to release-7.5 branch. severity/moderate sig/planner SIG: Planner type/bug The issue is confirmed as a bug. type/regression
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants