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 made changes -
            Fix Version/s 10.1 [ 16100 ]
            Assignee Igor Babaev [ igor ]
            Labels verified
            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 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 made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            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.