MySQL Bugs: #81200: User-defined variable changes value in SELECT assignment when it shouldn't

Bug #81200	User-defined variable changes value in SELECT assignment when it shouldn't
Submitted:	26 Apr 2016 12:52	Modified:	30 Apr 2016 10:16
Reporter:	Dean Trower	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have noticed that a SELECT statement of the form:

SELECT @x:=SomeIntegerValue,... WHERE...

Can change the value of @x from NULL to 0, EVEN WHEN IT RETURNS NO ROWS!
It appears to be typecasting @x to an integer, even though no actual assignment occurs.

Worse, it only happens occasionally, meaning it's definitely a bug and not just an "edge case".

How to repeat:
Here's the actual code I was testing.  I can't provide a reliable test case, because you'd need all the same table data, and because whether or not it happens seems to depend on the exact data you execute it with; I'm guessing it depends on the execution plan chosen for the SELECT.

## @p is a price (a decimal(8,2))

SELECT @x;   ## returns NULL initially

## This returns NO ROWS
SELECT @x:=s.CompanyID, IF(s.BadLimits,2,BIT_OR(COALESCE(s.SellPrice,p.SellPrice,@p)<a.MinItemPrice)), GROUP_CONCAT(CONCAT(a.AwardID,':[',a.MinItemPrice,',',a.MaxItemPrice,',',a.NominalItemPrice,']')) AS AwardData FROM s INNER JOIN companies c USING (CompanyID) INNER JOIN r USING (CompanyID) INNER JOIN awards a USING (CompanyID,AwardID) LEFT JOIN itemprices p ON (p.CompanyID=s.CompanyID AND p.ItemCode='S116') WHERE (s.BadLimits OR (COALESCE(s.SellPrice,p.SellPrice,@p)<a.MinItemPrice) OR (a.MaxItemPrice>0 AND COALESCE(s.SellPrice,p.SellPrice,@p)>a.MaxItemPrice)) GROUP BY 1 ORDER BY c.ShortName DESC;

SELECT @x;  ## Now this returns 0 (sometimes), despite no rows returned above!

Suggested fix:
Setting a variable in the output columns of a SELECT statement should have no effect if no rows are returned by the statement.  A NULL variable should not be converted to 0, or typecast to an integer by such a statement!

Hello David,

Thank you for the report.
I'm not seeing the issue which you have reported with dummy schema, could you please provide exact repeatable test case(table DDL, subset of data and query used, after uploading you can set it as private)? If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

##
// 5.6.30

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.30: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>  create table t1(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql>  select @x:=id from t1;
Empty set (0.01 sec)

mysql>  select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>

// 5.7.12

[root@cluster-repo ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> use test
ERROR 1049 (42000): Unknown database 'test'
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> create table t1(id int not null);
Query OK, 0 rows affected (0.01 sec)

mysql> select @x:=id from t1;
Empty set (0.00 sec)

mysql> select @x:=id from t1;
Empty set (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>

Thanks,
Umesh

Umesh, I am thoroughly underwhelmed by your attempts to reproduce this bug.

I specifically said it occurs *sometimes* based on data in the tables, and I provided the statement that was causing the bug, which contained several joins, a GROUP BY, and an ORDER BY clause, all of which (now) appear to be necessary to reproduce the problem (see below).

Attempting to reproduce the problem by issuing a

SELECT @x:=id FROM t1

With an empty t1 was an entirely pathetic attempt to investigate the bug report.  Does Oracle just not care about bugs in MySQL, or is it just you?  Frankly I am insulted.

However, I have gone ahead and boiled down a test case to something I can easily post here.  Try this, exactly as shown:

CREATE TEMPORARY TABLE t1 (a int unsigned NOT NULL, b tinyint unsigned NOT NULL, PRIMARY KEY (a,b)) ENGINE=MEMORY;
CREATE TEMPORARY TABLE t2 (a int unsigned NOT NULL, b tinyint unsigned NOT NULL, c tinyint unsigned NOT NULL, PRIMARY KEY (a,b)) ENGINE=MEMORY;
INSERT INTO t1 VALUES (446,141),(520,7);
INSERT INTO t2 VALUES (446,141,0),(520,7,0);

SET @x:=NULL;
SELECT @x:=a,GROUP_CONCAT(t2.b) FROM t1 INNER JOIN t2 USING (a,b) WHERE t2.c>100 GROUP BY 1 ORDER BY a;  #### WHERE clause guarantees no rows match
SELECT @x;  ## returns 0

SET @x:=NULL;
SELECT @x:=a,GROUP_CONCAT(t2.b) FROM t1 INNER JOIN t2 USING (a,b) WHERE t2.c>100 GROUP BY 1;  #### WHERE clause guarantees no rows match
SELECT @x;  ## returns 520

DROP TEMPORARY TABLE t2,t1;

...in both cases, obviously, given that no rows match, the returned value of @x should remain NULL.

You will have to change the bug status back to "Open" yourself, the system won't let me do that - it only shows options "Can't repeat" and "Closed".

Oh yeah, also my name is "Dean", not "David", which you might have noticed if you were actually paying attention.

Hi Dean, thanks for the testcase. 

Verified on 5.0, 5.1, 5.5, 5.6, 5.7, 5.8 using this:
-- -------
drop table if exists t2,t1;
create table t1 (a int)engine=memory;
create table t2 (a int, b int,c int,key(a))engine=memory;
insert into t1 values (1),(2);
insert into t2 values (1,1,0),(2,2,0);
set @x:=null;
select @x:=t1.a,group_concat(t2.a) from t1 inner join t2 using(a) where t2.c>2 group by 1 order by t1.a;
select @x;
set @x:=null;
select @x:=t1.a,group_concat(t2.a) from t1 inner join t2 using(a) where t2.c>2 group by 1;
select @x;
-- --------

mysql> set @x:=null;
Query OK, 0 rows affected (0.00 sec)

mysql> select @x:=t1.a,group_concat(t2.a) from t1 inner join t2 using(a) where t2.c>2 group by 1 order by t1.a;
Empty set (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> set @x:=null;
Query OK, 0 rows affected (0.00 sec)

mysql> select @x:=t1.a,group_concat(t2.a) from t1 inner join t2 using(a) where t2.c>2 group by 1;
Empty set (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

there is some warning text in the manual about using and assigning user variables in the same statement, so let's see what devs say about this case.

http://dev.mysql.com/doc/refman/5.7/en/user-variables.html
"In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:"

Thanks Shane, your interest & prompt response is much appreciated!

Well yes, I guess technically the "GROUP BY 1" involves grouping on "@x:=t1.a"... but it's far from obvious:  Firstly, the intent here is clearly to behave the same as if "GROUP BY t1.a" had been used, since the value of the := expression is the same as its RHS, and secondly, I was under the (perhaps mistaken) impression that GROUP BY <column number> would group using the already-calculated-and stored column output data, rather than duplicating the output expression and re-calculating it.

In any case, it still doesn't make much sense:  No rows ever match the WHERE clause or are ever sent to the client, so @x:=... should never have been evaluated, either in the output stage or by the GROUP BY, that I can see.  And certainly it should never wind up with the value 0, which isn't one of the t1.a values anyhow!

Q:  What happens if you do this instead:

select t1.a,group_concat(t2.a) from t1 inner join t2 using(a) where t2.c>2 group by @x:=t1.a order by t1.a;

Also, the test case doesn't make it obvious, but in my real DB query, MOST OF THE TIME it worked as expected, with @x remaining NULL, so the behavior was inconsistent as well as unexpected; I haven't figured out what it depends on exactly. 

The query was meant to vet any update/insert operation that violated certain business rules; the following INSERT OR UPDATE included "WHERE @x IS NULL" to ensure it didn't happen if any such rule violation was detected by the SELECT (with the actual SELECT output being used to generate output telling the user what went wrong).  Doing it that way avoided a round trip to PHP in the middle of a transaction.
The users complained that just every once in a while, an operation appeared to work fine, but on reloading the page nothing had actually happened!
...Which is to say, this bug has had real-world consequences, at least for me.

A related question that occurs to me is what would happen if I put a user-defined function WITH SIDE EFFECTS into column 1, e.g. my_fn(a) instead of @x:=a?  Or a user-defined function that (say) involved division by a?  (Is it ever called with an argument of 0?).  I'll leave investigating that to you, I think.