I have the following query:
SET @VAR = 'N';
SELECT 'TESTE1','OI',@VAR := 'S' AS T
UNION ALL
SELECT 'TESTE2','OI',@VAR AS T
UNION ALL
SELECT * FROM ( SELECT 'TESTE3','OI',@VAR AS T ) AS TAB
UNION ALL
SELECT 'TESTE4','OI',@VAR AS T
Can anyone explain to me why the value of "TEST3" is "N" and for the others, "S"? And also, how to get around the problem?
The reason is simple, regardless of the order you have the select, which is within the kinship will be executed first, therefore, will not have undergone the change made externally. In this case, the database works in a similar way to the ones in the mathematics executing what is inside the parenthesis first. Remembering this happens due to the execution plan calculated by the bank deciding to execute the query in this order.
You can test this by doing the following select:
SET @VAR = 'Z';
SELECT 'TESTE1','OI',@VAR AS T
UNION ALL
SELECT 'TESTE2','OI',@VAR AS T
UNION ALL
SELECT * FROM ( SELECT 'TESTE3','OI',@VAR := 'N' AS T ) AS TAB
UNION ALL
SELECT 'TESTE4','OI',@VAR AS T
That would return:
TESTE1 OI N
TESTE2 OI N
TESTE3 OI N
TESTE4 OI N
It will help you to notice that the change made within the parenthesis has been executed first, so the other records undergo the same change of the variable.
Having said that the way to get around the problem, is to understand this concept and passing the changes to the internal scope, as I did in the example to show you the solution, this should be enough to correct the problem.