mysql. Why does it come like this?

Question

MySQL

Good afternoon. Tell. Why is this happening?

SET @foo:=0;
SELECT @foo FROM test t1 LEFT JOIN test t2 ON t2.id=t1.id AND (@foo:[email protected]+1);

Outputs 0,0,0,0,0,0,0…

and with

SET @foo:=0;
SELECT @foo FROM test t1 JOIN test t2 ON t2.id=t1.id AND (@foo:[email protected]+1);

outputs 1,2,3,4,5,6...

Answer 1

Your query outputs the values ​​of the @foo variable. In the first case, its value is always 0 and does not change, so zeros are output. In the second case, when processing each line, the condition is checked, and in it there is an assignment of a new value to the @foo variable. Therefore, in each subsequent line, the value of @foo increases by one.

Answer 2

To clarify: the variable foo should not be in the join condition.
Need: SELECT @foo := @foo + 1 FROM test t1 LEFT JOIN test t2 ON t2.id = t1.id

Answer 3

Simultaneous reading and assignment of a variable within the same request is not recommended, this is (very mildly) described in the manual. Still more fun, even an expression like @a>0 and @a<0 can work