Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12819

order by ordering expression changed to empty string when creatin view with union

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.20, 10.1.22, 10.1(EOL), 10.2(EOL)
    • 10.1.25, 10.2.7
    • Views
    • 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

          Activity

            elenst Elena Stepanova added a comment - - edited

            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.
            

            elenst Elena Stepanova added a comment - - edited 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.

            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;
            

            sanja Oleksandr Byelkin added a comment - 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;

            find_order_in_list() is not called for the union

            sanja Oleksandr Byelkin added a comment - find_order_in_list() is not called for the union

            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.

            sanja Oleksandr Byelkin added a comment - 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.

            People

              sanja Oleksandr Byelkin
              marc.langevin@usherbrooke.ca Marc
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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