User-Defined variable not working in MySQL 5.7, but does in v5.6

Question

I'm trying to work with a User-Defined variable, and it works as expected with MySQL 5.6.17, but gives the below error in MySQL 5.7.10.

I found a working example with this question, and it is failing with the same error.

I can't find any documentation or mention of the problem (rather broad terms to google), and am assuming that it is a MySQL configuration issue. Can anyone assist, or point me in the right direction.

Query: SELECT T2.* FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM pos_port_attachments WHERE id = _id) AS parent_id, @l := @l + ...
Error Code: 1054
Unknown column '_id' in 'where clause'

This is the SQL that I'm using

SELECT T2.*
FROM (
    SELECT
    @r AS _id,
    (SELECT @r := parent_id FROM mytable WHERE id = _id) AS parent_id,
    @l := @l + 1 AS lvl
    FROM
    (SELECT @r := 100001, @l := 0) vars,
    pos_port_attachments m
    WHERE @r <> 0) T1
JOIN mytable T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

@RyanVincent It says "Unknown column" - not "Syntax error". But I wouldn't really wonder if that helps. — Paul Spiegel, Mar 09 '17 at 20:09

score 1 · Answer 1 · answered Mar 09 '17 at 18:53

1

I think you need to change WHERE id=_id to

WHERE id=@r

Not sure why it works in 5.6

answered Mar 09 '17 at 18:53

PeterHe

2,766
1
8
7

score 0 · Accepted Answer · answered Mar 09 '17 at 19:51

I don't know why one could ever refer to an alias in a correlated subquery. If you use undocumented "features" you can't rely that it will work for feature versions. However - Assuming that ID is allways greater that PARENT_ID, this one still works on http://rextester.com with MySQL 5.7.12:

select *
from (
    select t.*,
        case when t.id = @pid 
            then (@pid := t.parent_id) + 1
            else 0
        end as filter
    from some_table t
    cross join (select @pid := 31)init
    order by id desc
) t
where filter > 0
order by id asc;

And this one also:

select t.*,
    case when t.id = @pid 
        then (@pid := t.parent_id) + 1
        else 0
    end as filter
from some_table t
cross join (select @pid := 31)init
having filter > 0
order by id desc;

http://rextester.com/DZJLY71092

User-Defined variable not working in MySQL 5.7, but does in v5.6

2 Answers2