What is this query supposed to do? (and why does it fail?)

I’m tasked with revive this old piece of legacy software. It used to run on an old server (2012) which has died the ugly way (hard disk failure). Before this server died, the code worked without problems.

I’ve rebuild the MySQL database and data from backups.

However, one query is does not work and fails with error: Query preparation failed: Unknown column '_operationId' in 'where clause'. The query in question is:

SELECT
  @r AS _operationId
, @r := (
    SELECT
      operationId
    FROM operations
    WHERE operationId = _operationId
  ) AS includesOperationId
FROM (SELECT  @r := %i) AS tmp
INNER JOIN operations
WHERE @r > 0 AND @r IS NOT NULL

From what I understand, the query tries to join back onto itself building a tree of some sort??

For some reason, this query must have worked on some previous version of MySQL (5.0??) but with the current version (MySQL 5.7) the query fails.

Is there any ‘mysql whisperer’ out there who can explain to me:

1. what the query attempts to do?
2. why it worked on some previous version but not anymore?
3. how to change the query to make it work again?

thanks a million in advance.

Update:
The operations table definition and data:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| operationId | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| operation   | varchar(40)         | NO   | UNI | NULL    |                |
| description | text                | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

+-------------+-----------+-------------+
| operationId | operation | description |
+-------------+-----------+-------------+
|           1 | add       | NULL        |
|           2 | delete    | NULL        |
|           3 | edit      | NULL        |
|           4 | view      | NULL        |
|           5 | disable   | NULL        |
|           6 | execute   | NULL        |
+-------------+-----------+-------------+

Answer

The query is attempting to do some sort of tree traversal. I don’t know that it would work in any version of MySQL, but my best guess is that the intention is something like this:

SELECT @r AS _operationId,
       @r := (SELECT operationId
              FROM operations
              WHERE operationId = @r
             ) AS includesOperationId
FROM operations CROSS JOIN
     (SELECT @r := %i) params
WHERE @r > 0 AND @r IS NOT NULL;

Having said that, if this happens to work, there is no guarantee that it will work again or in another version of MySQL. This violates two rules of using variables:

• A variable assigned in one expression in a SELECT should not be used in another. The order of evaluation of expressions is not defined, so the expressions could be evaluated in any order.
• There is not guarantee on when the conditions in the WHERE clause using variables are evaluated and definitely no guarantee about some sort of “sequential” evaluation with respect to the SELECT.

The subquery is also problematic.

The good news is that if operations has no column called _operationId, then the query should fail on all versions of MySQL with an undefined column type of error (although perhaps older versions did something funky).

The bad news is that if you want to walk through a hierarchy in MySQL, you either need to change the data structure or use a stored procedure.