mysql update statement behaves differently based on whether a user variable exists

Question

I'm reseting a sort column that has duplicate or missing values like so:

set @last='';
set @sort=NULL;
update conf_profile set sort=
    if(
        @last=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)),
        (@sort:=@sort+1),
        (@sort:=0)
    )
order by org_id,profile_type_id,page,col,sort,id;

(Go through all the rows sorted by a number of key fields assigning progressively incremented values to sort; whenever any of those fields change, restart at 0.)

It seems to work only if the @sort variable was created prior to doing the update (though it doesn't matter what it was set to). Without the 'set @sort', all the sort values are set to either 0 or NULL.

Any ideas why this would be so? MySQL version 5.0.51.

Update: To explain the logic more at length: on the first row, the @last=(@last:=...) will always be false, and thereafter it will be false when any of the key fields changes from the previous row. (N.B. none of the key fields being concat'd are ever NULL). When it's false, we start the sort counter over again at 0 (@sort:=0), otherwise, it is incremented (@sort:=@sort+1) and the new value is used.

In no case is @sort used before it is being set in the update statement, so whether or how it is set before the update statement should make no difference.

Answer 1

An unset user variable is treated as NULL if you reference it in an expression.

In SQL, NULL + 1 returns NULL. If you don't set @sort to a non-NULL value prior to this UPDATE, then it'll continue to be NULL no matter how many times you evaluate @sort:=@sort+1. Once you do @sort:=0, then it should increment normally.

Try this without doing it in an UPDATE:

mysql> set @sort := NULL;
mysql> SELECT @sort; -- returns NULL
mysql> set @sort := @sort + 1;
mysql> SELECT @sort; -- returns NULL again
mysql> set @sort := 0;
mysql> set @sort := @sort + 1;
mysql> SELECT @sort; -- returns 1

I would guess it's only a coincidence that you have no duplicates after the first time you set @sort:=0.

edit: The above is true, but as you point out, it doesn't explain the behavior you're seeing, since logically @sort should be guaranteed set to 0 during evaluation of the first row.

However, I notice if I change the order of the terms in the IF() expression, it all works, even if @sort is unset as we begin:

set @last='';
-- set @sort=NULL;
update conf_profile set sort=
    if(
        @last!=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)),
        (@sort:=0),
        (@sort:=@sort+1)
    )
order by org_id,profile_type_id,page,col,sort,id;

I'm not sure if I understand it well enough to explain exactly why this works, but there's some wacky stuff regarding when user variables are evaluated. See this blog for lots of examples and gory details: "Advanced MySQL user variable techniques".