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

SQL Parsing Error - UNION AND ORDER BY WITH JOIN

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.3
    • 10.1.6
    • Optimizer
    • centos 6
    • 10.1.6-1

    Description

      This test case is not working on MariaDB 10.1.3:

      CREATE TABLE
      		 
      	test ( id INT, type_id INT, PRIMARY KEY(id) );
      		 
      	
      INSERT INTO 
      	test ( id, type_id )
      		 
      VALUES 
      	( 1, 1 ),
      		 
      	( 2, 1 ),
      		 
      	( 3, 2 ),
      		 
      	( 4, 5 );
      		 
      	
      SELECT
      		 
      	*
      		 
      FROM
      		 
      	test T
      		 
      	JOIN (
      		 
      		(
      		 
      			SELECT 1 AS some_another_type_id
      		 
      		)
      		 
      		UNION ALL
      		 
      		(
      		 
      			SELECT 2 AS some_another_type_id
      		 
      		)
      		 
      		ORDER BY
      		 
      			some_another_type_id DESC
      		 
      	) AAA
      		 
      WHERE
      		 
      	T.type_id = 1;

      Same query on clean Mysql 5.6 working with no problems.
      Workaround: delete unneeded "order by".

      Attachments

        Issue Links

          links to

          Web Link Github revision

          Activity

            Ascending order - Click to sort in descending order
            m.rygiel m.rygiel created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description *This test case is not working on MariaDB 10.1.3:*

            CREATE TABLE
            test ( id INT, type_id INT, PRIMARY KEY(id) );

            INSERT INTO
            test ( id, type_id )
            VALUES
            ( 1, 1 ),
            ( 2, 1 ),
            ( 3, 2 ),
            ( 4, 5 );

            SELECT
            *
            FROM
            test T
            JOIN (
            (
            SELECT 1 AS some_another_type_id
            )
            UNION ALL
            (
            SELECT 2 AS some_another_type_id
            )
            ORDER BY
            some_another_type_id DESC
            ) AAA
            WHERE
            T.type_id = 1


            Same query on clean Mysql 5.6 working with no problems.
            Workaround: delete unneeded "order by".             		*This test case is not working on MariaDB 10.1.3:*
            {code:sql}
            CREATE TABLE
            test ( id INT, type_id INT, PRIMARY KEY(id) );

            INSERT INTO
            test ( id, type_id )
            VALUES
            ( 1, 1 ),
            ( 2, 1 ),
            ( 3, 2 ),
            ( 4, 5 );

            SELECT
            *
            FROM
            test T
            JOIN (
            (
            SELECT 1 AS some_another_type_id
            )
            UNION ALL
            (
            SELECT 2 AS some_another_type_id
            )
            ORDER BY
            some_another_type_id DESC
            ) AAA
            WHERE
            T.type_id = 1;
            {code}

            Same query on clean Mysql 5.6 working with no problems.
            Workaround: delete unneeded "order by".
            elenst Elena Stepanova added a comment -

            Thanks for the report and the test case.

            The problem appeared along with UNION ALL optimization:

            commit 3c4bb0e8720b84a14fe4822d1986d01290b9ab44 fec5ab5a56cb9a45c621207620cc85079cddf537
            		 
            Author: Igor Babaev <igor@askmonty.org>
            		 
            Date:   Tue Oct 14 09:36:50 2014 -0700
            		 
             
            		 
                MDEV-334: Backport of UNION ALL optimization from mysql-5.7.
            		 
                
                Although the original code of mysql-5.7 was adjusted
            		 
                to the current MariaDB code the main ideas of the optimization
            		 
                were preserved.
            		 
             

            The same test case works all right on the current 5.7 tree.

            elenst Elena Stepanova added a comment - Thanks for the report and the test case. The problem appeared along with UNION ALL optimization: commit 3c4bb0e8720b84a14fe4822d1986d01290b9ab44 fec5ab5a56cb9a45c621207620cc85079cddf537 Author: Igor Babaev <igor@askmonty.org> Date: Tue Oct 14 09:36:50 2014 -0700   MDEV-334: Backport of UNION ALL optimization from mysql-5.7. Although the original code of mysql-5.7 was adjusted to the current MariaDB code the main ideas of the optimization were preserved.   The same test case works all right on the current 5.7 tree.
            elenst Elena Stepanova made changes -
            Fix Version/s 10.1 [ 16100 ]
            Assignee Igor Babaev [ igor ]
            Labels verified
            igor Igor Babaev (Inactive) added a comment -

            Some time ago we removed ORDER BY from any subqueries.
            ORDER BY in subqueries does not make any sense.

            igor Igor Babaev (Inactive) added a comment - Some time ago we removed ORDER BY from any subqueries. ORDER BY in subqueries does not make any sense.
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 60142 ] MariaDB v3 [ 66160 ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            Assignee Igor Babaev [ igor ]
            svoj Sergey Vojtovich made changes -
            Assignee Sergey Vojtovich [ svoj ]
            serg Sergei Golubchik made changes -
            Sprint 10.1.6-1 [ 6 ]
            svoj Sergey Vojtovich added a comment -

            serg, please review fix for this bug. Note that I'm completely unsure about correctness of this fix, since it was done with assumption that the purpose of context change was intended for the duration of gathering field list for ORDER BY.

            Or was it intended to be changed for different duration? In this case should we rather pop_context()?

            svoj Sergey Vojtovich added a comment - serg , please review fix for this bug. Note that I'm completely unsure about correctness of this fix, since it was done with assumption that the purpose of context change was intended for the duration of gathering field list for ORDER BY. Or was it intended to be changed for different duration? In this case should we rather pop_context()?
            svoj Sergey Vojtovich made changes -
            Assignee Sergey Vojtovich [ svoj ] Sergei Golubchik [ serg ]
            Status Confirmed [ 10101 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Oleksandr Byelkin [ sanja ]
            sanja Oleksandr Byelkin added a comment -

            CREATE TABLE t1 (
            a INT
            );

            INSERT INTO t1 VALUES ( 2 );

            SELECT *
            FROM ( (SELECT a FROM t1 ORDER BY a) UNION (SELECT 1 as b ORDER BY b ) ) AS a1
            WHERE a1.a = 1 OR a1.a = 2;

            DROP TABLE t1;

            sanja Oleksandr Byelkin added a comment - CREATE TABLE t1 ( a INT ); INSERT INTO t1 VALUES ( 2 ); SELECT * FROM ( (SELECT a FROM t1 ORDER BY a) UNION (SELECT 1 as b ORDER BY b ) ) AS a1 WHERE a1.a = 1 OR a1.a = 2; DROP TABLE t1;
            sanja Oleksandr Byelkin made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            svoj Sergey Vojtovich added a comment -

            The statement you're referring to was fixed in MySQL revision 5948561, which is worthy, but doesn't directly relate to this bug.

            svoj Sergey Vojtovich added a comment - The statement you're referring to was fixed in MySQL revision 5948561, which is worthy, but doesn't directly relate to this bug.
            svoj Sergey Vojtovich made changes -
            Assignee Oleksandr Byelkin [ sanja ] Sergey Vojtovich [ svoj ]
            svoj Sergey Vojtovich made changes -
            svoj Sergey Vojtovich made changes -
            Fix Version/s 10.1.6 [ 19401 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 66160 ] MariaDB v4 [ 148905 ]

            People

              svoj Sergey Vojtovich
              m.rygiel m.rygiel
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.