MySQL incorrect ordering

Question

I have a table for example: id | name | date_add

With content:

1| Mark | 2018-02-01 10:00:00

2| Andrew | 2018-02-01 10:00:00

When I try to do this request: select * from table order by date_add

I get rows in the next order: 2, 1

It occurs in Percona MySQL docker

With the same Percona on my host machine I got in 1, 2 order

How to enable logic in Docker as at my host machine?

The date is the same for both records... there is no ordering you can expect in that case. Add `Order BY date_add, id` instead if you need to insure the order when two dates are the same. — JNevill, May 14 '18 at 15:31
Since in that case the `date_add` field is the same value, the ordering is somewhat arbitrary and may come out differently on a different implementation. What do you require? Do you require the `id` to be in ascending order if the `date_add` is the same? If so, just specify: `select * from table order by date_add, id`. There is no default ordering. — lurker, May 14 '18 at 15:33
No. There is no default ordering in any RDBMS. It must always be specified. — JNevill, May 14 '18 at 15:33
It's strange why I've got this result in my docker but in another 3 host machine I've got another result. — user3169724, May 14 '18 at 15:34
@user3169724 this different results is strange, are the tables in the same engine (myisam, innodb) in all these hosts? Are you performing the queries direct throught the console or using a programming language like php, java? — justcode, May 14 '18 at 15:40
@arturios I checked. Engines are the same. I did these requests in mysql terminal client. And responses are not the same. By the way, I did about 10 the same requests in docker and in 2 of 10 I've got rows in straight order : 1, 2 — user3169724, May 14 '18 at 15:46
@arturios There is nothing strange here. The order is undefined beyond what the ORDER BY clause specifies. Any implementation is free to do whatever it likes. — user207421, May 14 '18 at 15:52
@EJP when you mean any implementation you are refering to a fork of the source code? Or just a compilation/package to another architecture, system? — justcode, May 14 '18 at 17:43

Marc L. · Accepted Answer · 2018-05-14T15:43:32.947

Unless specified in and ORDER BY clause, row ordering is indeterminate. There is no default ordering (or sub-ordering) that you should expect to obtain.

In this case, you should specify an ordering clause:

ORDER BY date_add, id

This behavior may be influenced by Percona or Docker, but again, you should not expect any apparent "default" ordering to appear, even between queries made minutes apart. See also What is the default Order By of queries?

MySQL incorrect ordering

1 Answers1