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 meta.stackoverflow.com/questions/333952/… and make a minimal reproducible example — nbk
– nbk, Commented Nov 12, 2020 at 13:16
This is actually a duplicate of stackoverflow.com/q/11589439/308851 , MySQL (obviously?) runs the calculations in the wrong order, forcing it with CASE works. — chx
– chx, Commented Nov 12, 2020 at 13:28
Does this answer your question? Subtracting the value from the last row using variable assignment in MySQL — nbk
– nbk, Commented Nov 12, 2020 at 13:29

Akina · Accepted Answer · 2020-11-12 13:19:17Z

0

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;

answered Nov 12, 2020 at 13:19

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can this MySQL query return NULL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related