Question posted in 
I have a query, that selects the lowest available id free in a table. It works perfect in my tests, but however I realized, that it always returns 9 in production. So I deleted id number 6 (which is lower) to check, what will happen. (Actually I hoped, I might show phpmyadmin that way, caching is not a smart idea) And indeed, it gave me back id 6 as free. So I gladly occupied it again and tried to get the next maximum Id, which was: 6. As you might guess, I was not happy at all.
My sql query is the following:
set @i=0;
select max(if(@i=SMS_Id,@i:=SMS_Id+1,@i)) AS Min_ID from sms_obtenir order by SMS_Id
And what it returns:
Well, as you can clearly see, the return is "6".
But my data looks like that (phone numbers censored for privacy reasons):
So as you can see, the correct answer would be 10. So I thought, "what happens, if I remove max from the query?" And so I did:
set @i=0;
select if(@i=SMS_Id,@i:=SMS_Id+1,@i) AS Min_ID from sms_obtenir order by SMS_Id
Result:
So, in fact, the whole if-case is correct, and the last number, that is returned, is 10.
So you would expect, as soon as you put a max(…) around it, the return is 10. But it´s not. It´s 6.
Anybody has an idea, how the heck the DB produces that result? (Shouldn´t be an issue of phpmyadmin, also happening in PHP code.)
EDIT:
I have tried out the query, that Barmar suggested and it didn´t solve the problem. However, it had an interesting side effect, that might lead to the result of 6. So, the query is the following:
set @i=0;
select max(if(@i=SMS_Id,@i:=SMS_Id+1,@i)) AS Min_ID
from (
SELECT SMS_Id
FROM sms_obtenir
order by SMS_Id
) AS SMS_Id
The result of the most inner part of the query
SELECT SMS_Id
FROM sms_obtenir
ORDER BY SMS_Id
returns, as you might guess, 0,1,2,3,4,5,6,7,8,9,11,….
However, as soon as I remove the max of the query it does not return (as it should) 1,2,3,4,5,6,7,8,9,10,10,10,10,…
but the following query
set @i=0;
select if(@i=SMS_Id,@i:=SMS_Id+1,@i) AS Min_ID
from (
SELECT SMS_Id
FROM sms_obtenir
order by SMS_Id
) AS SMS_Id LIMIT 0,75
returns 36 times 0 and then 1,2,3,4,5,6,6,6,6,6,….
So there the max-function seems to return the right value, however I might not have so much knowledge of procedures, but I am pretty sure, that the above result of the procedure is not, what should be returned according to the result of the inner function.
So, I decided to select the SMS_Id too, so I would see, what happened with the inner-statement, and I have to say, I am flustered, what amazing definition Maria DB has from ordering:
Here´s the result:
2
Answers
Okay, so Barmars answer brought me to the right solution for my problem. (Wasn´t aware, that ordering can happen after setting the variable) However, the problem with the solution is, that I can´t guarantee, it works. It works in my case so far, because MariaDB somehow seems to execute the statement in a very special way (wrote a test, that adds ids in an unordered combination, so the ids are not automatically ordered in the DB in the beginning), but as Barmar pointed out, normally the correct way should be to first order the SMS_Ids in a subquery and then set the variable. However, exactly that does not work in my version of Maria DB: It orders the SMS_Ids in the subquery, then gives up at the order and sets the variables on the unordered list. It also gives up on the order, when you directly select max from the variable. So, to "ensure" it is ordered (more precisely hope, it never does something different), the correct solution was to make a subquery, that first sets all the Min_Ids for the variable and THEN select the maximum of that subquery. The query is:
You should not set the variable in the same query that has
ORDER BY, because ordering can be done after assigning the variables in theSELECTlist. You need to do the ordering in a subquery.