Why ELSEIF is not reached?

Question

I have prepared an before update trigger in which I have encoded hierarchical relation:

CREATE DEFINER=`%`@`%` TRIGGER setToClosed
    BEFORE UPDATE ON issues
    FOR EACH ROW 
BEGIN
    declare project_id1 int;
    set @project_id1 = (select id from projects where name = 'XXX');
    IF (new.done_ratio=100) THEN
        IF (new.project_id =  @project_id1) THEN
            SET new.status_id=4;
        ELSEIF new.project_id in (
                        select  id
                        from    (select * from projects) projects_sorted,
                                (select @pv := @project_id1) initialisation
                        where   find_in_set(parent_id, @pv) > 0
                        and     @pv := concat(@pv, ',', id)) THEN
            SET new.status_id=4;

        ELSE
            SET new.status_id=5;
        END IF;
    END IF;

END

I would like to ask why ELSEIF is not reached? I have tested a standalone query from ELSEIF ant it returns list of ids.

After changes in row which status_id is present in ELSEIF query a trigger enters to ELSE.

How should I proceed ?

Because the conditions in the `IF` are always satisfied? That seems like the logical conclusion. — Gordon Linoff, Dec 22 '16 at 12:33
No, project_id1 has a specified value, and new.project_id may have different values. Beside that, as I wrote trigger enters to ELSE, to to IF — fafnir1990, Dec 22 '16 at 12:36
How do you conclude that `elseif` is not reached? Maybe it is, and it just evaluates to `false` all the time - have you tried a simpler condition in your `elseif`? I'm pretty sure that what you are trying to do in that query only works if you have a very specific ordering of your tree in your `projects`-table (and since you don't even have an `order by` there, it is impossible to guarantee that). It might work for your test cases, but not in general. So test this query with ids that you think "don't reach the elseif" (and, even then, without `order by` it COULD still be different!) — Solarflare, Dec 22 '16 at 13:24
@Solarflare I concluded by debugging (I used an signal statement to throw an exception insides IF, ELSEIF and ELSE) Thanks for suggestion. I have prepared a function returning BOOLEAN with that condition and added it to trigger. That works :) — fafnir1990, Dec 22 '16 at 13:31

Why ELSEIF is not reached?

0 Answers0