[MDEV-12819] order by ordering expression changed to empty string when creatin view with union Created: 2017-05-16  Updated: 2017-08-01  Resolved: 2017-06-22

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.1, 10.1.20, 10.1.22, 10.2
Fix Version/s: 10.1.25, 10.2.7

Type: Bug Priority: Major
Reporter: Marc Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux 2.6.32-696.1.1.el6.x86_64 #1 SMP Tue Mar 21 12:19:18 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux

Issue Links:
Relates
relates to MDEV-13420 views not compiling after installing ... Closed

 Description   

When creating a view containing union, order by ordering expression is changed to empty string if column position is used.

Test case to recreate:

create table if not exists t1 (t1col1 int, t1col2 int,t1col3 int );
 
create table if not exists t2 (t2col1 int, t2col2 int, t2col3 int);
 
 
 
create or replace view t_view as 
select t1col1,t1col2,t1col3 from t1
 
union all
 
select t2col1,t2col2,t2col3 from t2
 
order by 2,3;
 
 
 
show create view t_view;

Output shows order by with empty string `` instead of column position and view returns wrong result on execution:

CREATE ALGORITHM=UNDEFINED DEFINER=`marclang`@`10.45.53.%` SQL SECURITY DEFINER VIEW `t_view` AS select `t1`.`t1col1` AS `t1col1`,`t1`.`t1col2` AS `t1col2`,`t1`.`t1col3` AS `t1col3` from `t1` union all select `t2`.`t2col1` AS `t2col1`,`t2`.`t2col2` AS `t2col2`,`t2`.`t2col3` AS `t2col3` from `t2` order by '',''



 Comments   
Comment by Elena Stepanova [ 2017-05-16 ]

Thanks for the report and test case. Reproducible on 10.1 and 10.2.
The problem was introduced with this revision:

commit 4fdac6c07e1b896cbf6060d61b47669a48a1ebc9
 
Author: Oleksandr Byelkin
 
Date:   Wed Mar 16 19:49:17 2016 +0100
 
 
 
    MDEV-9701: CREATE VIEW with GROUP BY or ORDER BY and constant produces invalid definition
 
    
    Fixed printing integer constant in the ORDER clause (MySQL solution)
 
    Removed workaround for double resolving counter in the ORDER.

Comment by Oleksandr Byelkin [ 2017-05-17 ] 

create table t1 (t1col1 int, t1col2 int,t1col3 int );
 
create table t2 (t2col1 int, t2col2 int, t2col3 int);
 
 
create view v1 as 
select t1col1,t1col2,t1col3 from t1
 
union all
 
select t2col1,t2col2,t2col3 from t2
 
order by 2,3;
 
 
show create view v1;
 
 
 
drop view v1;
 
drop table t1,t2;

Comment by Oleksandr Byelkin [ 2017-05-17 ]

find_order_in_list() is not called for the union

Comment by Oleksandr Byelkin [ 2017-05-17 ]

global parameters (and so global ORDER BY) checked only for prepared statements (why!!!). Removing this check fix the issue but lead to crash because of absence of explain information in other UNIONs.

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