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

order operator not working with 'UNION' expression

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.0.17
    • Fix Version/s: N/A
    • Component/s: Parser
    • Labels:
      None
    • Environment:
      Linux

      Description

      CREATE DATABASE `test9`CHARACTER SET utf8 COLLATE utf8_general_ci; 
      USE `test9`; 
      CREATE TABLE `test`( `ordering` INT UNSIGNED ); 
      INSERT INTO test(ordering) VALUES (5),(2),(4),(3),(9),(8); 
      
      SELECT 0
      UNION ALL
      (SELECT ordering FROM test ORDER BY ordering)
      

      Expecting:

      ordering  
      ----------
               0
               2
               3
               4
               5
               8
               9
      

      But server return:

           0  
      --------
             0
             5
             2
             4
             3
             9
             8
      

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            mikhail Mikhail Gavrilov added a comment - - edited

            Sergei, thank you for joining the discussion of the problem.
            Yes, my particular problem can be solved with your workaround.
            But not everything can be rewritten so.
            For example the case when the UNION must unite two SELECT with different directions of the sort.

            (SELECT ordering FROM test ORDER BY ordering LIMIT 3)
            UNION ALL
            (SELECT ordering FROM test ORDER BY ordering DESC LIMIT 3)
            

            I just do not understand from whom this protection. As I said above, developers which needed in merging large dataset without ordering simple will not use ORDER statement in subqueries.

            Show
            mikhail Mikhail Gavrilov added a comment - - edited Sergei, thank you for joining the discussion of the problem. Yes, my particular problem can be solved with your workaround. But not everything can be rewritten so. For example the case when the UNION must unite two SELECT with different directions of the sort. ( SELECT ordering FROM test ORDER BY ordering LIMIT 3) UNION ALL ( SELECT ordering FROM test ORDER BY ordering DESC LIMIT 3) I just do not understand from whom this protection. As I said above, developers which needed in merging large dataset without ordering simple will not use ORDER statement in subqueries.
            Hide
            serg Sergei Golubchik added a comment -

            Exactly. With different sort directions you can do something like

            (SELECT 1 number, ordering as sort, ordering FROM test)
            UNION ALL
            (SELECT 2, -ordering as sort, ordering FROM test)
            ORDER BY number, sort
            

            It is not a "protection". It is simply how SQL standard defines the syntax.
            Here (simplified):

            <direct select statement: multiple rows> ::= <cursor specification>
            
            <cursor specification> ::= <query expression> [ <order by clause> ] 
            
            <query expression body> ::= <query term>
                                      | <query expression body> UNION [ ALL | DISTINCT ] <query term>
            
            <query term> ::= <query primary>
            
            <query primary> ::= <simple table>
            
            <simple table> ::= <query specification>
            
            <query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>
            

            As you can see, according to the SQL standard, ORDER BY applies to the whole <query expression>. And on both sides of UNION one can only put <query term>s. SELECT clause starts a <query specification> which is a <simple table>. And tables do not have any specific ordering of rows, so UNION works with two unordered sets of rows.

            That's the standard.

            Show
            serg Sergei Golubchik added a comment - Exactly. With different sort directions you can do something like ( SELECT 1 number, ordering as sort, ordering FROM test) UNION ALL ( SELECT 2, -ordering as sort, ordering FROM test) ORDER BY number, sort It is not a "protection". It is simply how SQL standard defines the syntax. Here (simplified): <direct select statement: multiple rows> ::= <cursor specification> <cursor specification> ::= <query expression> [ <order by clause> ] <query expression body> ::= <query term> | <query expression body> UNION [ ALL | DISTINCT ] <query term> <query term> ::= <query primary> <query primary> ::= <simple table> <simple table> ::= <query specification> <query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression> As you can see, according to the SQL standard, ORDER BY applies to the whole <query expression> . And on both sides of UNION one can only put <query term>s . SELECT clause starts a <query specification> which is a <simple table> . And tables do not have any specific ordering of rows, so UNION works with two unordered sets of rows. That's the standard.
            Hide
            mikhail Mikhail Gavrilov added a comment -

            Nice try, but even not work with my data set:

            (SELECT 1 number, ordering AS sort, ordering FROM test)
            UNION ALL
            (SELECT 2, -ordering AS sort, ordering FROM test)
            ORDER BY number, sort
            
            number    sort  ordering  
            ------  ------  ----------
                 1       2           2
                 1       3           3
                 1       4           4
                 1       5           5
                 1       8           8
                 1       9           9
                 2       0           8
                 2       0           5
                 2       0           2
                 2       0           4
                 2       0           3
                 2       0           9
            
            CREATE DATABASE `test9`CHARACTER SET utf8 COLLATE utf8_general_ci; 
            USE `test9`; 
            CREATE TABLE `test`( `ordering` INT UNSIGNED ); 
            INSERT INTO test(ordering) VALUES (5),(2),(4),(3),(9),(8); 
            

            I also respect the standards, but seems this issue cannot be solved without temporary tables and stored procedures.

            What about sub-queries?

            SELECT number, ordering FROM (SELECT 1 number, ordering FROM test ORDER BY ordering) t1
            UNION ALL
            SELECT number, ordering FROM (SELECT 2 number, ordering FROM test ORDER BY ordering DESC) t2
            

            I have always believed that all sub-queries are treated as separate requests.

            Show
            mikhail Mikhail Gavrilov added a comment - Nice try, but even not work with my data set: ( SELECT 1 number, ordering AS sort, ordering FROM test) UNION ALL ( SELECT 2, -ordering AS sort, ordering FROM test) ORDER BY number, sort number sort ordering ------ ------ ---------- 1 2 2 1 3 3 1 4 4 1 5 5 1 8 8 1 9 9 2 0 8 2 0 5 2 0 2 2 0 4 2 0 3 2 0 9 CREATE DATABASE `test9`CHARACTER SET utf8 COLLATE utf8_general_ci; USE `test9`; CREATE TABLE `test`( `ordering` INT UNSIGNED ); INSERT INTO test(ordering) VALUES (5),(2),(4),(3),(9),(8); I also respect the standards, but seems this issue cannot be solved without temporary tables and stored procedures. What about sub-queries? SELECT number, ordering FROM (SELECT 1 number, ordering FROM test ORDER BY ordering) t1 UNION ALL SELECT number, ordering FROM (SELECT 2 number, ordering FROM test ORDER BY ordering DESC) t2 I have always believed that all sub-queries are treated as separate requests.
            Hide
            serg Sergei Golubchik added a comment -

            why do you get "sort" as 0 in the second part of the result set? may be 'sort' is unsigned? try casting it to signed (in the first select, of course).

            You don't understand. see how the standard defines it (above). ORDER BY applies to the result set. It cannot be applied to a SELECT inside a UNION, it cannot be applied to a subquery. According to the standard iy is a syntax error to use ORDER BY the way you did.

            Show
            serg Sergei Golubchik added a comment - why do you get "sort" as 0 in the second part of the result set? may be 'sort' is unsigned? try casting it to signed (in the first select, of course). You don't understand. see how the standard defines it (above). ORDER BY applies to the result set . It cannot be applied to a SELECT inside a UNION, it cannot be applied to a subquery. According to the standard iy is a syntax error to use ORDER BY the way you did.
            Hide
            mikhail Mikhail Gavrilov added a comment - - edited

            why do you get "sort" as 0 in the second part of the result set? may be 'sort' is unsigned? try casting it to signed (in the first select, of course).

            I specifically show that this variant is completely dependent on the type of sorting field. And will be not worked with VARCHAR data.

            You don't understand. see how the standard defines it (above). ORDER BY applies to the result set.

            Why can not consider that the result set and also have a sub-query?

            All sub-query having result set and we can sort, group and make calculations in this result sets.

            I retest my test case in Postgre SQL 9.4 and construction

            SELECT 0
            UNION ALL
            (SELECT ordering FROM test ORDER BY ordering)
            

            work as expected.

            select VERSION()
            
            "PostgreSQL 9.4.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2 20150107 (Red Hat 4.9.2-5), 64-bit"
            

            Can you accept this improvement for MariaDB?

            When added "(" ")" to query, this query must be treat as subquery.

            Of course, I am agree that this statement

            SELECT 0
            UNION ALL
            SELECT ordering FROM test ORDER BY ordering
            

            erroneously.

            Show
            mikhail Mikhail Gavrilov added a comment - - edited why do you get "sort" as 0 in the second part of the result set? may be 'sort' is unsigned? try casting it to signed (in the first select, of course). I specifically show that this variant is completely dependent on the type of sorting field. And will be not worked with VARCHAR data. You don't understand. see how the standard defines it (above). ORDER BY applies to the result set. Why can not consider that the result set and also have a sub-query? All sub-query having result set and we can sort, group and make calculations in this result sets. I retest my test case in Postgre SQL 9.4 and construction SELECT 0 UNION ALL ( SELECT ordering FROM test ORDER BY ordering) work as expected. select VERSION() "PostgreSQL 9.4.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2 20150107 (Red Hat 4.9.2-5), 64-bit" Can you accept this improvement for MariaDB? When added "(" ")" to query, this query must be treat as subquery. Of course, I am agree that this statement SELECT 0 UNION ALL SELECT ordering FROM test ORDER BY ordering erroneously.

              People

              • Assignee:
                Unassigned
                Reporter:
                mikhail Mikhail Gavrilov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: