MySQL Bugs: #91800: Incorrect calculation (logical operation) when using variables

Bug #91800	Incorrect calculation (logical operation) when using variables
Submitted:	26 Jul 2018 12:05	Modified:	1 Aug 2018 13:35
Reporter:	Jaspreet Badwal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7	OS:	Ubuntu (16.04 LTS)
Assigned to:		CPU Architecture:	x86 (i5 Sandy Bridge)

Description:
Im currently executing the following test query using Workbench 6.3.10 Community as well as from the command line on Ubuntu 16.04 LTS.

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

SELECT 
    (CASE WHEN @v2 >= @v1 THEN @x1 ELSE @x2 END) as amount
FROM 
    (SELECT @v1 := 5, @v2 := 15, @x1 := 1, @x2 := 2) as v

I would expect that for every executing, amount = 1. 

However, When I first connect to the db and execute the query, I get 2. Every subsequent executing of the query on the same connection, results in a value of 1. Only the first execution of the query right after connecting with a client is the value of amount returned as 2. 

mysql> SELECT 
    ->     (CASE WHEN @v2 >= @v1 THEN @x1 ELSE @x2 END) as amount
    -> FROM 
    ->     (SELECT @v1 := 5, @v2 := 15, @x1 := 1, @x2 := 2) as v
    -> ;
+--------+
| amount |
+--------+
| 2      |
+--------+
1 row in set (0.00 sec)

mysql> SELECT      (CASE WHEN @v2 >= @v1 THEN @x1 ELSE @x2 END) as amount FROM      (SELECT @v1 := 5, @v2 := 15, @x1 := 1, @x2 := 2) as v;
+--------+
| amount |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT      (CASE WHEN @v2 >= @v1 THEN @x1 ELSE @x2 END) as amount FROM      (SELECT @v1 := 5, @v2 := 15, @x1 := 1, @x2 := 2) as v;
+--------+
| amount |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

How to repeat:
Close or reset your connection from the client to the server.

Execute the test query provided.

Now execute the test query again.

Hi,

Our Reference Manual says loud and clear then when assigning user variables within SELECT statement, then the order of the evaluation and comparison is unreliable and unknown. 

Try this instead:

SET @v1 = 5;
SET @v2 = 15;
SET @x1 = 1;
SET @x2 = 2;

then run several times:

SELECT  (CASE WHEN @v2 >= @v1 THEN @x1 ELSE @x2 END) as amount FROM   (SELECT @v1, @v2,  @x1, @x2) as v;

if you get the same result on each run, then this is expected behaviour and not a bug.

I was able to work around this by nesting the variable initialisation. It seems that by nesting, they are guaranteed to be executed prior to the query in the wrapper being executed. 

I did finally notice that reference in the manual which lead me to try nesting the init.

Not a bug