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

views not compiling after installing 10.1.25 error on order clause

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.1.25, 10.1(EOL), 10.2(EOL)
    • N/A
    • Views
    • linux red hat 6

    Description

      Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works

      having those 2 tables:

      create table if not exists t1 (idcol1 int,t1col1 int, t1col2 int,t1col3 int );
      		 
      create table if not exists t2 (idcol1 int,t2col1 int, t2col2 int, t2col3 int);

      2 different cases:

      create or replace view t2_view (col1,col2,col3) as
      		 
      select t1col1,t1col2,t1col3 from t1
      		 
      union all
      		 
      select t2col1,t2col2,t2col3 from t2
      		 
      order by col1,col2;

      gives error

      Code: 1054 SQL State: 42S22 --- Unknown column 'col1' in 'order clause'

      case #2

      create or replace view t4_view (idcol1,col2,col3,col4) as
      		 
      select t1.idcol1,t1col2,t1col3,t2.idcol1 from t1 join t2 on (t1.idcol1 = t2.idcol1)
      		 
      union all
      		 
      select t2.idcol1,t2col2,t2col3,t1.idcol1 from t2 join t1 on (t2.idcol1 = t1.idcol1)
      		 
      order by idcol1,col2;

      gives error : 

      Code: 1052 SQL State: 23000 --- Column 'idcol1' in order clause is ambiguous

      Those cases were working on 10.1.22 before upgrade to 10.1.25

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12819 order by ordering expression changed to empty string when creatin view with union

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marc.langevin@usherbrooke.ca Marc created issue -
            marc.langevin@usherbrooke.ca Marc made changes -
            Field Original Value New Value
            marc.langevin@usherbrooke.ca Marc made changes -
            Description Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works

            having those 2 tables:
            create table if not exists t1 (idcol1,t1col1 int, t1col2 int,t1col3 int );
            create table if not exists t2 (idcol1,t2col1 int, t2col2 int, t2col3 int);

            2 different cases:
            create or replace view t2_view (col1,col2,col3) as
            select t1col1,t1col2,t1col3 from t1
            union all
            select t2col1,t2col2,t2col3 from t2
            order by col1,col2;

            gives error
            Code: 1054 SQL State: 42S22 --- Unknown column 'col1' in 'order clause'

            case #2
            create or replace view t4_view (idcol1,col2,col3,col4) as
            select t1.idcol1,t1col2,t1col3,t2.idcol1 from t1 join t2 on (t1.idcol1 = t2.idcol1)
            union all
            select t2.idcol1,t2col2,t2col3,t1.idcol1 from t2 join t1 on (t2.idcol1 = t1.idcol1)
            order by idcol1,col2;

            gives error :
            Code: 1052 SQL State: 23000 --- Column 'idcol1' in order clause is ambiguous


            Those cases were working on 10.1.22 before upgrade to 10.1.25


            marc.langevin@usherbrooke.ca Marc made changes -
            Description Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works

            having those 2 tables:
            create table if not exists t1 (idcol1,t1col1 int, t1col2 int,t1col3 int );
            create table if not exists t2 (idcol1,t2col1 int, t2col2 int, t2col3 int);

            2 different cases:
            create or replace view t2_view (col1,col2,col3) as
            select t1col1,t1col2,t1col3 from t1
            union all
            select t2col1,t2col2,t2col3 from t2
            order by col1,col2;

            gives error
            Code: 1054 SQL State: 42S22 --- Unknown column 'col1' in 'order clause'

            case #2
            create or replace view t4_view (idcol1,col2,col3,col4) as
            select t1.idcol1,t1col2,t1col3,t2.idcol1 from t1 join t2 on (t1.idcol1 = t2.idcol1)
            union all
            select t2.idcol1,t2col2,t2col3,t1.idcol1 from t2 join t1 on (t2.idcol1 = t1.idcol1)
            order by idcol1,col2;

            gives error :
            Code: 1052 SQL State: 23000 --- Column 'idcol1' in order clause is ambiguous


            Those cases were working on 10.1.22 before upgrade to 10.1.25


            		Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works

            having those 2 tables:
            create table if not exists t1 (idcol1 int,t1col1 int, t1col2 int,t1col3 int );
            create table if not exists t2 (idcol1 int,t2col1 int, t2col2 int, t2col3 int);

            2 different cases:
            create or replace view t2_view (col1,col2,col3) as
            select t1col1,t1col2,t1col3 from t1
            union all
            select t2col1,t2col2,t2col3 from t2
            order by col1,col2;

            gives error
            Code: 1054 SQL State: 42S22 --- Unknown column 'col1' in 'order clause'

            case #2
            create or replace view t4_view (idcol1,col2,col3,col4) as
            select t1.idcol1,t1col2,t1col3,t2.idcol1 from t1 join t2 on (t1.idcol1 = t2.idcol1)
            union all
            select t2.idcol1,t2col2,t2col3,t1.idcol1 from t2 join t1 on (t2.idcol1 = t1.idcol1)
            order by idcol1,col2;

            gives error :
            Code: 1052 SQL State: 23000 --- Column 'idcol1' in order clause is ambiguous


            Those cases were working on 10.1.22 before upgrade to 10.1.25


            elenst Elena Stepanova made changes -
            Description Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works

            having those 2 tables:
            create table if not exists t1 (idcol1 int,t1col1 int, t1col2 int,t1col3 int );
            create table if not exists t2 (idcol1 int,t2col1 int, t2col2 int, t2col3 int);

            2 different cases:
            create or replace view t2_view (col1,col2,col3) as
            select t1col1,t1col2,t1col3 from t1
            union all
            select t2col1,t2col2,t2col3 from t2
            order by col1,col2;

            gives error
            Code: 1054 SQL State: 42S22 --- Unknown column 'col1' in 'order clause'

            case #2
            create or replace view t4_view (idcol1,col2,col3,col4) as
            select t1.idcol1,t1col2,t1col3,t2.idcol1 from t1 join t2 on (t1.idcol1 = t2.idcol1)
            union all
            select t2.idcol1,t2col2,t2col3,t1.idcol1 from t2 join t1 on (t2.idcol1 = t1.idcol1)
            order by idcol1,col2;

            gives error :
            Code: 1052 SQL State: 23000 --- Column 'idcol1' in order clause is ambiguous


            Those cases were working on 10.1.22 before upgrade to 10.1.25


            		Views that were changed to workaround bug MDEV-12819 are not compiling anymore after installing 10.1.25 and gives error on the order clause. But changing them again to use numeral position works

            having those 2 tables:
            {code:sql}
            create table if not exists t1 (idcol1 int,t1col1 int, t1col2 int,t1col3 int );
            create table if not exists t2 (idcol1 int,t2col1 int, t2col2 int, t2col3 int);
            {code}

            2 different cases:
            {code:sql}
            create or replace view t2_view (col1,col2,col3) as
            select t1col1,t1col2,t1col3 from t1
            union all
            select t2col1,t2col2,t2col3 from t2
            order by col1,col2;
            {code}

            gives error
            {noformat}
            Code: 1054 SQL State: 42S22 --- Unknown column 'col1' in 'order clause'
            {noformat}

            case #2
            {code:sql}
            create or replace view t4_view (idcol1,col2,col3,col4) as
            select t1.idcol1,t1col2,t1col3,t2.idcol1 from t1 join t2 on (t1.idcol1 = t2.idcol1)
            union all
            select t2.idcol1,t2col2,t2col3,t1.idcol1 from t2 join t1 on (t2.idcol1 = t1.idcol1)
            order by idcol1,col2;
            {code}

            gives error :
            {noformat}
            Code: 1052 SQL State: 23000 --- Column 'idcol1' in order clause is ambiguous
            {noformat}

            Those cases were working on 10.1.22 before upgrade to 10.1.25


            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova added a comment -

            Thanks for the report and test case.

            elenst Elena Stepanova added a comment - Thanks for the report and test case.
            elenst Elena Stepanova made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.2 [ 14601 ]
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.2 [ 14601 ]
            Assignee Oleksandr Byelkin [ sanja ]
            Labels regression
            sanja Oleksandr Byelkin added a comment -

            Correct view is:

            create view v1 (col1,col2,col3) as
            		 
            select t1col1,t1col2,t1col3 from t1
            		 
            union all
            		 
            select t2col1,t2col2,t2col3 from t2
            		 
            order by t1col1,t1col2;

            Because the union just has no columns col1 and col2 and the select if it executed alone also return error. If it was working somehow before it was a bug. The same is in the second case, column in ORDER BY should be from result set of the select or union to which the ORDER BY belongs to.

            sanja Oleksandr Byelkin added a comment - Correct view is: create view v1 (col1,col2,col3) as select t1col1,t1col2,t1col3 from t1 union all select t2col1,t2col2,t2col3 from t2 order by t1col1,t1col2; Because the union just has no columns col1 and col2 and the select if it executed alone also return error. If it was working somehow before it was a bug. The same is in the second case, column in ORDER BY should be from result set of the select or union to which the ORDER BY belongs to.
            sanja Oleksandr Byelkin made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Not a Bug [ 6 ]
            Status Confirmed [ 10101 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 81866 ] MariaDB v4 [ 152549 ]

            People

              sanja Oleksandr Byelkin
              marc.langevin@usherbrooke.ca Marc
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.