What boolean value return assign integer or string

Question

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 issue clear but at first was an empty string.

CROSS JOIN ( SELECT @rn := 0, @type := 'a', @ref := '') as var

SQL DEMO #1

CREATE TABLE t (
  `id` INTEGER,
  `wsf_ref` INTEGER,
  `status` VARCHAR(8),
  `type` VARCHAR(6),
  `wsf_progress` VARCHAR(5),
  `wsf_value` INTEGER
);

SELECT t.*, @rn := if(  @ref = `wsf_ref`,
                       if ( @type = `type`, 
                             @rn + 1,
                             if( @type := `type`, 1, 1)                     
                           ),
                       if ( (@ref := `wsf_ref`) and (@type := `type`), 1, 1)
                    ) as rn,
            @type,
            @ref
FROM t
CROSS JOIN ( SELECT @rn := 0, @type := 'a', @ref := '') as var
ORDER BY `wsf_ref`, `type`, `wsf_value` DESC;

You can see first row enter the last condition and set both variable correct:

OUTPUT

| id | wsf_ref |   status |   type | wsf_progress | wsf_value | rn |  @type | @ref |
|----|---------|----------|--------|--------------|-----------|----|--------|------|
|  6 |       1 | Approved |   blue |        Day 1 |        25 |  1 |   blue |    1 |
|  5 |       1 | Approved |   blue |        Day 1 |        10 |  2 |   blue |    1 |
|  3 |       1 | Approved | orange |        Day 1 |        20 |  1 | orange |    1 |

Buf if wsf_ref is a VARCHAR i got a different result

SQL DEMO #2

CREATE TABLE t (
  `id` INTEGER,
  `wsf_ref` VARCHAR(255),
  `status` VARCHAR(255),
  `type` VARCHAR(255),
  `wsf_progress` VARCHAR(5),
  `wsf_value` INTEGER
);

Here you can see first row the variable @type isnt set and still have a

OUTPUT

| id |  wsf_ref |   status |   type | wsf_progress | wsf_value | rn |  @type |     @ref |
|----|----------|----------|--------|--------------|-----------|----|--------|----------|
|  3 | WSF19-01 | Approved |  Perch |        Day 2 |        20 |  1 |      a | WSF19-01 |
|  4 | WSF19-01 | Approved |  Perch |        Day 2 |        10 |  1 |  Perch | WSF19-01 |

After some debuging I found the problem is with the last assignment

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

On first case when wsf_ref is integer the assignment evaluate to true and then the second condition is also checked. On the second case when wsf_ref is string the result is false and the second condition is ignored.

I change the condition to:

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

So even if the first condition is false still try to evaluate the second condition and now both query are working ok.

So why assign @ref a number get a different boolean than assign a string?

Answer 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:

SELECT t.*,
       (@rn := if(@tr = CONCAT_WS(':', wsf_ref, type),
                  @rn + 1,
                  if(@tr := CONCAT_WS(':', wsf_ref, type), 1, 1
                    )                     
                 )
       ) as rn
FROM (SELECT t.*
      FROM t 
      ORDER BY `wsf_ref`, `type`, `wsf_value` DESC
     ) t CROSS JOIN
     (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.