MySql - @Var Is Not Null failing

Question

If I run this stored procedure as is, it gives incorrect return value, and when I examine my log, it never logs the else condition:

CREATE DEFINER=`root`@`localhost` PROCEDURE `rpt_PlayerOnlineTime`(IN ucid VARCHAR(128), INOUT totaltime INT)
BEGIN

DECLARE v_finished INTEGER DEFAULT 0;

DECLARE v_event varchar(20) DEFAULT "";
DECLARE v_time BIGINT DEFAULT 0;
DECLARE v_server_id INT DEFAULT 0;
DECLARE v_session_id INT DEFAULT 0;

DECLARE v_start BIGINT DEFAULT 0;
DECLARE v_end BIGINT;


DEClARE con_cursor CURSOR FOR 
SELECT type, real_time, server_id, session_id FROM ki.raw_connection_log WHERE player_ucid = ucid;

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished = 1;

CALL ki.log("rpt_PlayerOnlineTime", CONCAT("totaltime: ", totaltime));

OPEN con_cursor;

online_time: LOOP
    CALL ki.log("rpt_PlayerOnlineTime", "In Loop");

    FETCH con_cursor INTO v_event, v_time, v_server_id, v_session_id;

    IF v_finished = 1 THEN 
        CALL ki.log("rpt_PlayerOnlineTime", "Finished Loop");
        LEAVE online_time;
    END IF;

    IF v_event = "CONNECTED" THEN


        IF v_start != 0 then
            CALL ki.log("rpt_PlayerOnlineTime", "CONNECTED EVENT TWICE IN A ROW");
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_server_id - ", v_server_id));
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_session_id - ", v_session_id));
            -- If the next event is not a disconnect event, assume that the player or server crashed, and get the time from the session
            SELECT session.real_time_end INTO v_end FROM ki.session WHERE server_id = v_server_id AND session_id = v_session_id;
            IF v_end IS NOT NULL THEN
                CALL ki.log("rpt_PlayerOnlineTime", "SETTING v_end TO SESSION END TIME");
            ELSE
                SELECT session.last_heartbeat INTO v_end FROM ki.session WHERE session.server_id = v_server_id AND session.session_id = v_session_id;
                CALL ki.log("rpt_PlayerOnlineTime", "SETTING v_end To Last HeartBeat Received from session");
                CALL ki.log("rpt_PlayerOnlineTime", CONCAT("totaltime : ", totaltime));
                CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_start : ", v_start));

                IF v_end IS NULL THEN
                    CALL ki.log("rpt_PlayerOnlineTime", "Last Heartbeat is NULL - defaulting to start");
                    SET v_end = v_start; -- if the last_heartbeat is null, then assume there was no end date
                END IF;
                CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_end : ", v_end));
            END IF;

            SET totaltime = totaltime + (v_end - v_start);
        END IF;
        SET v_start = v_time;
    ELSEIF v_event = "DISCONNECTED" THEN
        CALL ki.log("rpt_PlayerOnlineTime", "DISCONNECTED EVENT - CALCULATING END TIME");
        SET totaltime = totaltime + (v_time - v_start);
        SET v_start = 0;
    END IF;

    CALL ki.log("rpt_PlayerOnlineTime", CONCAT("IN LOOP: Total Time: ", totaltime));
END LOOP online_time;

CLOSE con_cursor;

CALL ki.log("rpt_PlayerOnlineTime", CONCAT("Total Time: ", totaltime));
END

The problem is here in this section of code - v_end may be NULL, but using the current if statement it never is true (I never see the else information logged)

        -- If the next event is not a disconnect event, assume that the player or server crashed, and get the time from the session
        SELECT session.real_time_end INTO v_end FROM ki.session WHERE server_id = v_server_id AND session_id = v_session_id;
        IF v_end IS NOT NULL THEN
            CALL ki.log("rpt_PlayerOnlineTime", "SETTING v_end TO SESSION END TIME");
        ELSE
            SELECT session.last_heartbeat INTO v_end FROM ki.session WHERE session.server_id = v_server_id AND session.session_id = v_session_id;
            CALL ki.log("rpt_PlayerOnlineTime", "SETTING v_end To Last HeartBeat Received from session");
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("totaltime : ", totaltime));
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_start : ", v_start));

            IF v_end IS NULL THEN
                CALL ki.log("rpt_PlayerOnlineTime", "Last Heartbeat is NULL - defaulting to start");
                SET v_end = v_start; -- if the last_heartbeat is null, then assume there was no end date
            END IF;
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_end : ", v_end));
        END IF;

However, if I modify the if statement to use != NULL, it works.

        -- If the next event is not a disconnect event, assume that the player or server crashed, and get the time from the session
        SELECT session.real_time_end INTO v_end FROM ki.session WHERE server_id = v_server_id AND session_id = v_session_id;
        IF v_end != NULL THEN
            CALL ki.log("rpt_PlayerOnlineTime", "SETTING v_end TO SESSION END TIME");
        ELSE
            SELECT session.last_heartbeat INTO v_end FROM ki.session WHERE session.server_id = v_server_id AND session.session_id = v_session_id;
            CALL ki.log("rpt_PlayerOnlineTime", "SETTING v_end To Last HeartBeat Received from session");
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("totaltime : ", totaltime));
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_start : ", v_start));

            IF v_end IS NULL THEN
                CALL ki.log("rpt_PlayerOnlineTime", "Last Heartbeat is NULL - defaulting to start");
                SET v_end = v_start; -- if the last_heartbeat is null, then assume there was no end date
            END IF;
            CALL ki.log("rpt_PlayerOnlineTime", CONCAT("v_end : ", v_end));
        END IF;

Why does MySQL work this way? I was looking at other stackoverflow posts about the proper way to check if a variable is null, and they recommend using IS NOT NULL instead of != NULL.

How to check if a variable is NULL, then set it with a MySQL stored procedure?

I'm using MySql 5.7.20 - is this a bug in MySql?

is this an integer value `session.real_time_end`? – Vijunav Vastivch Jan 04 '18 at 00:52 — Vijunav Vastivch, Jan 04 '18 at 00:52
no both are bigints and they come from a third party game. – Igneous01 Jan 04 '18 at 01:03 — Igneous01, Jan 04 '18 at 01:03

score 1 · Accepted Answer · answered Jan 04 '18 at 03:00

expr != NULL can never be true. It is always going to evaluate as NULL.

I would suggest that it's not actually working, you only believe that it is, because you have another bug, here:

SELECT session.real_time_end INTO v_end FROM ki.session WHERE server_id = v_server_id AND session_id = v_session_id;

Don't use this without understandIng exactly what it does... and once you do understand, you probably won't want to use it.

Instead...

SET v_end = (SELECT session.real_time_end FROM ki.session WHERE server_id = v_server_id AND session_id = v_session_id);

The problem is that the first form will not do what you expect when no row matches the query, because there is no result returned, so the INTO is never actually reached. It doesn't set the value of the variable to NULL as you would expect.

The value from the previous iteration of the loop is still in the variable.

The second form is explicitly scalar, always returning exactly one value, which is set to NULL when no row is matched.

See expanded examples at https://dba.stackexchange.com/a/35207/11651. It uses user-defined variables rather than program variables, but the behavior it illustrates is the same for both.

Alternately, explicitly set v_end to NULL inside the loop immediately before you SELECT ... INTO it.

Repeat for all instances of SELECT ... INTO.

Excellent explanation, now I know to avoid select into for these reasons (or reset the variable to NULL each time in the loop). — Igneous01, Jan 04 '18 at 05:43

Vijunav Vastivch · Answer 2 · 2018-01-04T01:37:01.700

0

You declare v_end as BIGINT

If this session.real_time_end is NULL or 0

You can use:

IF v_end != NULL or v_end = 0 THEN

I am not sure your session.real_time_end but you can try it.

As i observed you can use LENGTH FUNCTION like:

IF LENGTH(v_end)>0

it will accept NULL == 0 otherwise it return the Length itself.

edited Jan 04 '18 at 01:37

answered Jan 04 '18 at 00:57

Vijunav Vastivch

4,153
1
16
30

I know how to fix it, my question was why using IS NOT NULL does not work, yet using != NULL does? – Igneous01 Jan 04 '18 at 01:08
what is the content of your `session.real_time_end` – Vijunav Vastivch Jan 04 '18 at 01:16
it is a bigint, it can have a value like 63013593 or NULL as an example. – Igneous01 Jan 04 '18 at 01:18
`!=` null is not advisable to use in MYSQL it could not return the real condition although it will accept your code without any error return. Instead use `IS NOT NULL` or `IS NULL` – Vijunav Vastivch Jan 04 '18 at 01:43
But for succeed of your problem i recommend you to use `LENGHT`. – Vijunav Vastivch Jan 04 '18 at 01:45
1

@reds That's the whole point of the question. He tried to use `IS NOT NULL` and it didn't work. – Barmar Jan 04 '18 at 01:45

MySql - @Var Is Not Null failing

2 Answers2