[MDEV-31995] Bogus error executing PS for query using CTE with renaming of columns - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4.31, 11.2.1
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2
Component/s: Optimizer - CTE, Server
Labels:
None

create table t1 (a int, b int);

with cte (c1,c2) as (select a as col1, sum(b) as col2 from t1 group by col1) select * from cte;

so far so good.

prepare st from 'with cte (c1,c2) as (select a as col1, sum(b) as col2 from t1 group by col1) select * from cte';

execute st;

ERROR 1054 (42S22): Unknown column 'col1' in 'group statement'

swapping the original column name back in...

prepare st from 'with cte (c1,c2) as (select a as col1, sum(b) as col2 from t1 group by a) select * from cte';

execute st;

is OK

order by clause is similar

with cte (c1,c2) as (select a as col1, sum(b) as col2 from t1 order by col1) select * from cte;

is OK.

prepare st from 'with cte (c1,c2) as (select a as col1, sum(b) as col2 from t1 order by col1) select * from cte';

execute st;

ERROR 1054 (42S22): Unknown column 'col1' in 'order clause'

interestingly (by design) the where clause is treated differently

select a as col1, sum(b) as col2 from t1 where col1 is not null;

ERROR 1054 (42S22): Unknown column 'col1' in 'where clause'

relates to

MDEV-32183 Incorrect name resolution in CTE

MDEV-32708 Assertion Failed at /mariadb-11.3.0/sql/filesort.cc:1194

MDEV-32829 Crash when executing PS for query with eliminated subquery using view

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.