Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.20, 10.1.22, 10.1(EOL), 10.2(EOL)
-
None
-
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
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 '','' |
Attachments
Issue Links
- relates to
-
MDEV-13420 views not compiling after installing 10.1.25 error on order clause
-
- Closed
-
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.