MySQL CASE WHEN is not working as expected with user-defined variables?

Posted on

Question :

i need help please with the following query. I am pulling zip codes (plz) along with user id (uid) from DB and ORDER them by zip (plz) and uid. Also I use a user-defined variable @prev to check if previous plz is different from current plz. In this CASE column rownum should decrease starting user-defined variable @row by 1.

Here is my query for this ..

SELECT   
  @prev as previous, @prev := u.plz as current, 
  CASE 
    WHEN @prev is null THEN @row := @row -1 
    WHEN @prev <> u.plz THEN @row := @row -1
    ELSE @row END AS rownum, 
  uid

FROM             
  (SELECT @prev := NULL, @row := 1001) as r, user as u

LEFT JOIN 
  geodb_locations ON u.plz = geodb_locations.id 
WHERE 
  u.plz IN(29386,30013,29271,30406) AND
ORDER BY 
  FIELD(u.plz,29386,30013,29271,30406), 
  uid DESC

Output

+----------+---------+--------+-------+
| previous | current | rownum | uid   |
+----------+---------+--------+-------+
| NULL     |   29386 |   1001 | 92014 | <-- @prev <> u.plz why NOT decreasing `rownum`
| 29386    |   29386 |   1001 | 87223 |
| 29386    |   29386 |   1001 | 83160 |
| 29386    |   29386 |   1001 | 81322 |
| 29386    |   29386 |   1001 | 81293 |
| 29386    |   29386 |   1001 |  3152 |
| 29386    |   29386 |   1001 |  1939 |
| 29386    |   30013 |   1001 | 65193 | <-- @prev <> u.plz why NOT decreasing `rownum`
| 30013    |   29384 |   1001 | 12883 |
| 29384    |   29385 |   1001 | 32667 |
| 29385    |   29232 |   1001 | 86641 |
| 29232    |   29481 |   1001 | 94521 |
| 29481    |   29481 |   1001 | 33950 |
| 29481    |   29481 |   1001 | 33926 |
| 29481    |   29667 |   1001 | 16027 |
| 29667    |   29667 |   1001 |   496 |
| 29667    |   29355 |   1001 | 96013 |
+----------+---------+--------+-------+

Expected output

+----------+---------+--------+-------+
| previous | current | rownum | uid   |
+----------+---------+--------+-------+
| NULL     |   29386 |   1000 | 92014 | <-- decrease @row `rownum` by one
| 29386    |   29386 |   1000 | 87223 |
| 29386    |   29386 |   1000 | 83160 |
| 29386    |   29386 |   1000 | 81322 |
| 29386    |   29386 |   1000 | 81293 |
| 29386    |   29386 |   1000 |  3152 |
| 29386    |   29386 |   1000 |  1939 |
| 29386    |   30013 |    999 | 65193 | <-- decrease @row `rownum` by one 
| 30013    |   29384 |    998 | 12883 |
| 29384    |   29385 |    997 | 32667 |
| 29385    |   29232 |    996 | 86641 |
| 29232    |   29481 |    995 | 94521 |
| 29481    |   29481 |    995 | 33950 |
| 29481    |   29481 |    995 | 33926 |
| 29481    |   29667 |    994 | 16027 |
| 29667    |   29667 |    994 |   496 |
| 29667    |   29355 |    993 | 96013 |
+----------+---------+--------+-------+

Why is CASE WHEN not working like expected? Where is my mistake in thinking?
Thanks in advance ..

Answer :

The order of output expressions evaluation is not documented or specified. In general the evaluating order matches the text.

Look. You assign @prev := u.plz and then try to compare these values – but they are already equal due to assigning.

SELECT   
  @prev as previous,                          -- @prev stores "old" value
  @prev := u.plz as current,                  -- @prev is re-assigned to u.plz
  CASE 
    WHEN @prev is null THEN @row := @row -1   -- @prev is equal to u.plz, FALSE
    WHEN @prev <> u.plz THEN @row := @row -1  -- @prev is equal to u.plz, FALSE
    ELSE @row END AS rownum,                  -- this alternative is evaluated always
  uid

Place this assign AFTER the CASE expression – in this case CASE will use old (taken from previous row calculations) value.

SELECT   
  @prev as previous,                          -- @prev stores "old" value
  CASE 
    WHEN @prev is null THEN @row := @row -1   -- "old" value is used for conditions
    WHEN @prev <> u.plz THEN @row := @row -1 
    ELSE @row END AS rownum,                 
  @prev := u.plz as current,                  -- and now @prev is re-assigned to u.plz
  uid

Leave a Reply

Your email address will not be published.