What Boolean Value Return Assign Integer Or String To A Variable

Im using user variables to emulate ROW_NUMBER() OVER (PARTITION BY `wsf_ref`, `type` ORDER BY `wsf_value` DESC) Pay attention to the @type variable. I set it to a to make the iss

Solution 1:

Your expression is:

if ( (@ref := `wsf_ref`) and (@type := `type`), 1, 1)

MySQL does not necessarily evaluate both conditions. It only needs to evaluate the "second" one if the "first" evaluates to true. (I put "first" and "second" in quotes because the order of evaluation is not determined, but the idea is the same regardless.)

When these are strings, the result of @ref := wsf_rf is a string. The string is converted to a boolean, via a number. The value is 0 -- which is false -- unless the string happens to start with digit.

Hence, both conditions are not evaluated and the second is not assigned.

I would write this as:

SELECTt.*,
       (@rn := if(@tr = CONCAT_WS(':', wsf_ref, type),
                  @rn + 1,
                  if(@tr := CONCAT_WS(':', wsf_ref, type), 1, 1
                    )                     
                 )
       ) asrnFROM (SELECT t.*
      FROM t 
      ORDER BY `wsf_ref`, `type`, `wsf_value` DESC
     ) tCROSSJOIN
     (SELECT @rn := 0, @tr := '') params;

I moved the ORDER BY to a subquery because more recent versions of MySQL don't handle ORDER BY and variables very well.