jeudi 13 juin 2019

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 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.

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

Aucun commentaire:

Enregistrer un commentaire