CODEWITHSUNDEEP
mysql
justin
justin

Reputation: 161

MySQL user variable used in expression defining it in SELECT

I'm trying to understand the following code snippet from this answer:

  SELECT   my_table.*,
           @f:=CONVERT(
             IF(@c<=>CatId AND @r<=>Rate AND DATEDIFF(Date, @d)=1, @f, Date), DATE
           ) AS Begin,
           @c:=CatId, @d:=Date, @r:=Rate
  FROM     my_table JOIN (SELECT @c:=NULL) AS init
  ORDER BY CatId, Rate, Date

My confusion is around how @f is being assigned. The name @f itself appears on both the left and right sides of the @f := expr statement. What does it mean when a user variable appears in the expression defining it?

Upvotes: 1

Views: 32

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562891

Have you ever used a statement in any programming language like this:

i = i + 1;

The current value if i is evaluated, then add one, then assign the result as the new value of i.

It's the same with assignment of @f in the SQL expression. The right side of the assignment is evaluated first, using the current value of @f. Then the result is assigned to the left side, which may be one of the variables involved in the expression.

Upvotes: 1

Related Questions