Mysql SQLselect user-variable in if

Question

Is is quite wired that the user-defined variable in the following sentence,

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

As show in here: http://sqlfiddle.com/#!2/fddbd/83, and my_table is given by:

CREATE TABLE my_table (
  Id    INT(6) UNSIGNED ZEROFILL,
  CatId INT,
  Date  DATE,
  Rate  INT
);

INSERT INTO my_table
VALUES
  (000001, 12, '2009-07-07', 1),
  (000002, 12, '2009-07-08', 1),
  (000003, 12, '2009-07-09', 1),
  (000004, 12, '2009-07-10', 2),
  (000005, 12, '2009-07-15', 1),
  (000006, 12, '2009-07-16', 1),
  (000007, 13, '2009-07-08', 1),
  (000008, 13, '2009-07-09', 1),
  (000009, 14, '2009-07-07', 2),
  (000010, 14, '2009-07-08', 1),
  (000010, 14, '2009-07-10', 1);

more precisely, the variable @f is not pre-defined, and what's the logic mysql will do for the DateDiff?

It's the null-safe equality operator. dev.mysql.com/doc/refman/5.0/en/… — Barmar
– Barmar, Commented Jan 23, 2015 at 5:55
@Barmar I see this, in fact, at first I think it is equal to <>, what a big misunderstanding! Thanks! — van abel
– van abel, Commented Jan 23, 2015 at 5:59

Barmar · Accepted Answer · 2015-01-23 07:56:05Z

1

On the first row it will use NULL for the values of all the variables. So it's equivalent to the following query that initializes them all explicitly

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

It's generally not recommended to put the ORDER BY clause in the same level of the query as the processing. MySQL performs this differently depending on whether the columns in the ORDER BY are named in the SELECT clause. If they are, it performs the ordering after producing all the results, otherwise it uses it to order the rows in the table before processing them. The way to get predictable results is to use a subquery:

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

Since you're seeing different results on different MySQL versions, this might also depend on the version. Using the subquery should make it work consistently.

edited Jan 23, 2015 at 7:56

answered Jan 23, 2015 at 6:00

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

11 Comments

Barmar Over a year ago

CROSS JOIN is a join with no conditions. It just performs a full cross product between the two tables. In this case, the subquery just returns one row.

van abel Over a year ago

And what's happens on the second row to @f,@c,@d?

Barmar Over a year ago

On the second row they all have the values that were assigned during the first row. That's the point of using variables, they allow you to carry values from one row to the next.

Barmar Over a year ago

I thought your question was just about what happens when you use the variables before they're assigned, not a general misunderstanding of what variables are for.

Barmar Over a year ago

If you take out ORDER BY, you'll get different results: sqlfiddle.com/#!2/7e6edb/2

|

Collectives™ on Stack Overflow

Mysql SQLselect user-variable in if

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related