How can this MySQL query return NULL?

Question

SET @last_nid=-1; 
SELECT vid,
     IF(@last_nid = nid, @last_status, 0) AS last_status,
     @last_status := status AS status,
     @last_nid := nid AS nid 
FROM node_field_revision 
ORDER BY vid;

nid and vid are both positive integers and status is either 0 or 1.

I am at a complete loss at how can last_status result in a NULL -- but it sure does.

MySQL manual says:

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

Well, at first iteration @last_nid can not be equal to nid because one is negative and the other is positive. Therefore, 0 is returned. Yes, @last_status is NULL here but it can not be returned. Then it fills in @last_status with the value of status which is either 0 or 1.

In further iterations, @last_status will never be made NULL again.

And yet, an overwhelming majority of rows has NULL as its @last_status -- if I give @last_status an initial value then that doesn't happen but then again, this means my thinking and my query is bogus but why/how?

please read https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query and make a [mre] — nbk, Nov 12 '20 at 13:16
This is actually a duplicate of https://stackoverflow.com/q/11589439/308851 , MySQL (obviously?) runs the calculations in the wrong order, forcing it with `CASE` works. — chx, Nov 12 '20 at 13:28
Does this answer your question? [Subtracting the value from the last row using variable assignment in MySQL](https://stackoverflow.com/questions/11589439/subtracting-the-value-from-the-last-row-using-variable-assignment-in-mysql) — nbk, Nov 12 '20 at 13:29

score 0 · Answer 1 · answered Nov 12 '20 at 13:19

You do not initialize @last_status variable so server assumes it is a constant of NULL.

Init ALL variables before the query execution, not only one of them, or do it in the query:

SELECT vid,
     IF(@last_nid = nid, @last_status, 0) AS last_status,
     @last_status := status AS status,
     @last_nid := nid AS nid 
FROM node_field_revision 
CROSS JOIN (SELECT @last_nid:=-1, @last_status:=0) AS initialize_variables
ORDER BY vid;

How can this MySQL query return NULL?

1 Answers1