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

    XMLWordPrintable

Details

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

            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.