Really strange behavior using variables in MySql query

Question

I was answering a Question on Stack Overflow forum and the OP notice a really strange behavior that I could reproduce using the SQLFiddle site

So lets go to it.

We have this setup:

CREATE TABLE inventory
    (`animal` varchar(6), `date` date, `quantity` int);
INSERT INTO inventory
    (`animal`, `date`, `quantity`)
VALUES
    ('dog', '2015-01-01', 400),
    ('cat', '2015-01-01', 300),
    ('dog', '2015-01-02', 402),
    ('rabbit', '2015-01-01', 500),
    ('cat', '2015-01-02', 304),
    ('rabbit', '2015-01-02', 508),
       ('rabbit', '2015-01-03', 524),
       ('rabbit', '2015-01-04', 556),
       ('rabbit', '2015-01-05', 620),
    ('rabbit', '2015-01-06', 748);

The requirement of the OP is to have this result:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      500 |             8 |
| rabbit | 2015-01-02 |      508 |            16 |
| rabbit | 2015-01-03 |      524 |            32 |
| rabbit | 2015-01-04 |      656 |            64 |
| rabbit | 2015-01-05 |      620 |           128 |
| rabbit | 2015-01-06 |      748 |          null |
+--------+------------+----------+---------------+

So I did it as you can see it here: http://sqlfiddle.com/#!9/c77d8/182

It is working fine, but the OP said that on his side he was experiencing a strange behavior, the very first time that he runs the SQL it returns all null for the quantity_diff column and on the second time it returns -248 for the last quantity_diff.

So I asked what was that he did different and the only thing that I noticed is the MySql version. In my answer I've create the setup on the 5.6 version (5.6.21 on the above fiddle) and on his version he created the setup on the 5.5 (5.5.44-0ubuntu0.14.04.1) his fiddle

The more strange thing is that I've tested the same setup here on my machine with mysql version 5.6.21 (windows 7) and it behaves exactly as the user mentioned with the MySql Worckbench as client, but I could solve the problem adding the variable definition prior to the query execution as this:

set @qt:=null;
select animal,
       `date`,
       quantity,
       lead-quantity quantity_diff 
from ( select i.animal, 
              i.`date`,
              @qt as lead,
              @qt := i.quantity as quantity
        from inventory i
       where i.animal = 'rabbit' 
       order by `date` desc
     ) tab
order by `date`;

So Is this some weird behavior? Or some known bug? Or (most probably) a server configuration regarding the use of variables @something in a query?

Answer 1

http://sqlfiddle.com/#!9/c77d8/189 - so after the query runs once in the current session, the last value is reused. That's expected behavior. MySQL user variables are session-specific.

Now why the 5.5 fiddle does not start with NULL - I suppose the fiddle runs EXPLAIN on the query before actual execution, and because of https://bugs.mysql.com/bug.php?id=44802 the derived table is materialized during that EXPLAIN (the subquery is executed) and so the variable already has a value in the actual run.

I tried to verify it in http://sqlfiddle.com/#!2/c77d8/16 - you can see that I am not running the query at all, only the EXPLAIN and it is enough for @qt to get the value of 500. That does not happen in the 5.6 version http://sqlfiddle.com/#!9/c77d8/190

So it is not exactly a bug, more a design/implementation limitation of optimizer before 5.6 which is documented (even when the case with user variables is not made clear, it is just a side effect of the documented behavior).