Different variable value in sub-select

Question

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 "TESTE3" is "N" and for others, "S"? And also, how to get around the problem?

Answer 1

The reason is simple, regardless of the order in which you dispose of the select, what this within the parenthesis will be executed first, so you will not have suffered the change made externally. In this case database works similarly to us in mathematics by executing what is within parenthesis first. Remembering that this happens due to execution plan calculated by the bank deciding to execute the query in this order.

You can test this by making 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

Who had returned:

TESTE1  OI  N
TESTE2  OI  N
TESTE3  OI  N
TESTE4  OI  N

It will help you to notice that the change made within parenthesis was performed first, so the other records suffer the same change of the variable.

That said the way to get around the problem, is understanding this concept and passing the changes to the internal escopro, as I did in the example to show you the solution, this should be enough to fix the problem.