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

Sorting Bug

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Critical
        • Resolution: Not a Bug
        • Affects Version/s: None
        • Fix Version/s: N/A
        • Component/s: Optimizer
        • Labels:
          None

          Description

          The following initial situation:

          A small selection:
          A Table (see at dump.sql):

          user datum ereignis
          1 2007-02-13 Eintritt Maßnahme
          1 2007-03-13 Eintritt
          2 2007-04-04 Eintritt Maßnahme
          2 2007-05-02 Eintritt
          2 2008-01-31 Kündigung am
          2 2008-02-29 Austritt

          When I execute the following Command:

          SELECT * FROM
	(SELECT * FROM `userhitory`
	ORDER BY user,datum ASC) s
GROUP BY user

          With Maria DB 10.0.17 I get the following result:

          user datum ereignis
          1 2007-02-13 Eintritt Maßnahme
          2 2007-04-04 Eintritt Maßnahme
          3 2014-07-01 Eintritt Maßnahme
          4 2014-08-27 Eintritt Maßnahme

          With MySQL Server 5.5 I get the following result:

          user datum ereignis
          1 2013-08-15 Austritt
          2 2008-02-29 Austritt
          3 2015-01-29 Austritt
          4 2015-02-26 Austritt

          The expected result is the MySQL 5.5 result.

          Is there a bug?

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Please see this article which will hopefully answer your question: https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/

                It describes the general situation with ORDER BY in subqueries.
                In your particular case, there is yet another problem – you are using partial GROUP BY, so the result of your query is indeterminate. MySQL manual describes it and specifically mentions that ORDER BY does not make a difference here:
                https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

                MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

                So, the fact that what you are getting with MySQL now meets your expectations is pure luck and cannot be relied upon. This behavior can change any time, even in a minor release upgrade (and even more likely in the next major version), and it will not be considered as breaking compatibility.

                Show
                elenst Elena Stepanova added a comment - Please see this article which will hopefully answer your question: https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/ It describes the general situation with ORDER BY in subqueries. In your particular case, there is yet another problem – you are using partial GROUP BY, so the result of your query is indeterminate. MySQL manual describes it and specifically mentions that ORDER BY does not make a difference here: https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses. So, the fact that what you are getting with MySQL now meets your expectations is pure luck and cannot be relied upon. This behavior can change any time, even in a minor release upgrade (and even more likely in the next major version), and it will not be considered as breaking compatibility.
                Hide
                Permalink
                oxi Alexander Pentermann added a comment -

                ok thank you!

                Show
                oxi Alexander Pentermann added a comment - ok thank you!

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    oxi Alexander Pentermann
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: