CODEWITHSUNDEEP
mysqlsqlsql-order-by
Accountant م
Accountant م

Reputation: 7483

same query with order clause, same dataset, but different result

I was testing my application(an ERP system) by programming a tester that will do a fixed scenario of 30 steps of things like this

//pseudo code(PHP)
public function runScenario1Test(){
    V2_1Tester::resetDatabase();
    V2_1Tester::insert60Companies();
    V2_1Tester::insert2000Items();
    V2_1Tester::insert100Purchases();
    V2_1Tester::insert100Sales();

    //do some other stuff

    V2_1Tester:checkResults();
}

Although every time I run the test of the same code, same data, all inputs were the same, I was getting different results sometimes!!!

My head was going to blow up, and after 4 days of investigations, tears, and even bad database dreams by night, it turns out that the bug was in a query that returns different results sometimes. It is something like this

+-----+--------------+-----------+------+--------+
| ID  |     date     | direction | col3 |  col4  |
+-----+--------------+-----------+------+--------+
|  1  |  2018-03-03  |     in    |   6  | 100.50 |
|  2  |  2018-03-03  |     in    |   6  | 350.75 |
+-----+--------------+-----------+------+--------+
-- more ~ 3000 rows

$query = "SELECT * FROM table ORDER BY date, direction, col3";

this query sometimes returns 1 then 2 and some other times 2 then 1.

I fixed the query by adding additional level for ordering ID

$query = "SELECT * FROM table ORDER BY date, direction, col3, ID";

But I don't understand why MySQL behaves like this ?, In other words what are the rules that MySQL will follow for the rows that are the same for all order-by columns ? and why it is changing ?

Upvotes: 0

Views: 176

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

In SQL, order by is not stable. That means that when the keys are the same, the ordering can be in any order.

This is actually obvious. SQL tables represent unordered sets. There is no ordering unless a column specifies the ordering.

So, you have done the right thing by including the id as the final key. This ensures that the order will be well-defined because there are no duplicate keys.

Upvotes: 1

Related Questions