[MDEV-13420] views not compiling after installing 10.1.25 error on order clause Created: 2017-08-01  Updated: 2017-09-14  Resolved: 2017-09-14

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.1, 10.1.25, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Marc Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 1
Labels: regression
Environment:

linux red hat 6

Issue Links:
Relates
relates to MDEV-12819 order by ordering expression changed ... Closed

 Description   

Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works

having those 2 tables:

create table if not exists t1 (idcol1 int,t1col1 int, t1col2 int,t1col3 int );
 
create table if not exists t2 (idcol1 int,t2col1 int, t2col2 int, t2col3 int);

2 different cases:

create or replace view t2_view (col1,col2,col3) as
 
select t1col1,t1col2,t1col3 from t1
 
union all
 
select t2col1,t2col2,t2col3 from t2
 
order by col1,col2;

gives error

Code: 1054 SQL State: 42S22 --- Unknown column 'col1' in 'order clause'

case #2

create or replace view t4_view (idcol1,col2,col3,col4) as
 
select t1.idcol1,t1col2,t1col3,t2.idcol1 from t1 join t2 on (t1.idcol1 = t2.idcol1)
 
union all
 
select t2.idcol1,t2col2,t2col3,t1.idcol1 from t2 join t1 on (t2.idcol1 = t1.idcol1)
 
order by idcol1,col2;

gives error : 

Code: 1052 SQL State: 23000 --- Column 'idcol1' in order clause is ambiguous

Those cases were working on 10.1.22 before upgrade to 10.1.25

 Comments   
Comment by Elena Stepanova [ 2017-08-01 ]

Thanks for the report and test case.

Comment by Oleksandr Byelkin [ 2017-09-14 ]

Correct view is:

create view v1 (col1,col2,col3) as
 
select t1col1,t1col2,t1col3 from t1
 
union all
 
select t2col1,t2col2,t2col3 from t2
 
order by t1col1,t1col2;

Because the union just has no columns col1 and col2 and the select if it executed alone also return error. If it was working somehow before it was a bug. The same is in the second case, column in ORDER BY should be from result set of the select or union to which the ORDER BY belongs to.

Generated at Thu Feb 08 08:05:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.