1

I have two containers. Both are identical. Both are launched using the same docker-compose.yml file on two different servers. Both have the same data. Exactly the same (one is backed up and restored in the other one).

Yet I get different sorting for the same query.

This is my query:

SELECT `i`.`Id`, 
       `i`.`Guid`, 
       `i`.`Key`, 
       `i`.`Name`, 
       `i`.`Order`, 
       `i`.`PartTypeId`, 
       `i`.`PartTypeKey`, 
       `i`.`SectionId`, 
       `i`.`SectionKey`
FROM `ItemPartViews` AS `i`
WHERE `i`.`SectionId` = 7
ORDER BY `i`.`Order`

And these are images of the results:

enter image description hereenter image description here

I can't understand why do I get different sorts. Any explanation is appreciated.

Ali EXE
  • 215
  • 1
  • 7

1 Answers1

11

Why do I get different sorting for the same query on the same data in two identical MariaDB instances?

You are sorting on the field i.Order. In your example screenshots of the data, in the Order column, the values are all the same (they are all 0). That makes this a nondeterministic sort (pseudo-random).

Think of it this way, if you had a box of 20 red balls, each with a different shape printed on them, and you told someone to take them out of the box and sort them by color, it wouldn't matter which ball they placed first and which one they placed last, because they're all the same color. If you then put them all back in the box, and asked a different person to do the same (sort by color), the order they placed the balls in still wouldn't matter, and likely would be different than what the first person did.

That's what's happening here. When sorting on a non-unique field, it is a nondeterministic ordering, and there's no guarantee what order those rows will actually return in. There are some factors in the SQL Engine under the hood that drive what that order is actually, but it's too complex, not well enough known, and most importantly not guaranteed by design.

If you added the i.Id or some other unique field to your ORDER BY clause, then your rows will always return in the same order. Because ordering by a unique field is deterministic.

J.D.
  • 40,776
  • 12
  • 62
  • 141