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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.20, 10.1.22, 10.1, 10.2
    • Fix Version/s: 10.1.25, 10.2.7
    • Component/s: Views
    • 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

      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

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

                Dates

                • Created:
                  Updated:
                  Resolved: