Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • Donate
  • About
  • Toggle search form

Different ORDER BY behavior between MySql and MariaDB

Posted on June 30, 2018May 1, 2019 By

During the porting of an application from MySql to MariaDB, I found another curious fact: I get a different ORDER BY behaviour between MySql And MariaDB.

Look at this query:

SELECT DISTINCT Z.ID 
FROM (
        SELECT Field1, Field2, Field3....
        FROM Table1 ORD 
        INNER JOIN Table2 OC ON ORD.OrderCopID = OC.ID 
        LEFT JOIN Table3 NEG ON OC.NegID = NEG.ID 
        ....
        WHERE 1=1 
        ORDER BY OC.ID DESC, ORD.Progr
      ) AS Z DESC LIMIT 0, 25
) AS T

The expected result, and the one I get using MySql, is a table with one column containing a list of numeric IDs in descending order, due to the ORDER BY clause OC.ID DESC

The expected result should be something like this:

3010, 3009, 3008, 3007, and so on…

Well, the same query executed on the same database with the same data but running on MariaDB returns a different recordset:

3006, 2008, 3007, 500, and so on…

I was able to solve the problem simply adding and ORDER BY clause to the external subquery, as follow:

SELECT DISTINCT Z.ID 
FROM (
      SELECT Field1, Field2, Field3....
      FROM Table1 ORD 
      INNER JOIN Table2 OC ON ORD.OrdineCoppiaID = OC.ID 
      LEFT JOIN Table3 NEG ON OC.NegozioID = NEG.ID 
      ....
      WHERE 1=1 
      ORDER BY OC.ID DESC, ORD.Progressivo) AS Z ORDER BY Z.ID DESC LIMIT 0, 25
) AS T

Why this different beahviour? Who is right between MySql and MariaDB?

I don’t know, but the lesson is clear: if you expect a recordset in a certain order, always explicitly set this in your query, and don’t even trust an inner subquery order by!

MariaDB, MySql

Post navigation

Previous Post: phpMyAdmin: #2014 – Commands out of sync
Next Post: Oracle JRE 7 Update 51 (64-Bit) or higher is required for Polybase

Related Posts

Hello world! MySql
Configure a PHP development environment in Windows (part 4 of 4) MySql
phpMyAdmin: #2014 – Commands out of sync MariaDB

Search

Subscribe to our newsletter

Categories

  • .NET 6 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (6)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (2)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (5)

Copyright © 2022 The Dummy Programmer | Privacy Policy |

Powered by PressBook Masonry Dark