MySQL user variable used in expression defining it in SELECT

Question

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?

Bill Karwin · Accepted Answer · 2017-02-26 23:47:12Z

1

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.

answered Feb 26, 2017 at 23:47

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

justin Over a year ago

Okay--how does it start though? i.e., what is the initial value of @f?

Bill Karwin Over a year ago

If you had set @f to some other value previously in the current session, it'll have that value. Otherwise NULL.

Collectives™ on Stack Overflow

MySQL user variable used in expression defining it in SELECT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related