Bug #2675 SELECT (null subquery) INTO ... fails in stored procedure
Submitted: 8 Feb 2004 11:38 Modified: 24 Nov 2004 14:32
Reporter: Peter Gulutzan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.0-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[8 Feb 2004 11:38] Peter Gulutzan
Description:
If the SELECT of a subquery has no rows, then its result should be NULL. However, inside 
a stored procedure, "SELECT (subquery which returns no rows) INTO parameter FROM 
table" doesn't cause the parameter to be set to NULL. The parameter keeps its original 
value. 
 

How to repeat:
mysql> create table t (s1 int)// 
Query OK, 0 rows affected (0.31 sec) 
 
mysql> create procedure p (out par int) begin set par = 1; select (select s1 from t) into par 
from t; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p(@x)// 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> select @x// 
+------+ 
| @x   | 
+------+ 
| 1    | 
+------+ 
1 row in set (0.00 sec)
[17 Feb 2004 11:31] Dean Ellis
Verified against 5.0.1-alpha-log.
[26 Sep 2004 14:50] Per-Erik Martin
It appears that this is not a stored procedure specific problem:

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

mysql> select (select s1 from t) into @x from t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------+
| Level   | Code | Message          |
+---------+------+------------------+
| Warning | 1329 | No data to FETCH |
+---------+------+------------------+
1 row in set (0.00 sec)

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

mysql> set @x = (select s1 from t);
Query OK, 0 rows affected (0.00 sec)

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

mysql>
[28 Sep 2004 16:27] Oleksandr Byelkin
Thank you for bugreport, but it is not a bug, because table t is empty, so value of 
subquery is not even calculated.
[24 Nov 2004 14:32] Sergei Golubchik
just to clarify - the table in the outer select is empty, so no assignment ever takes place. If the table in the outer select is not empty and the table in the subquery is, then the variable is correctly set to NULL.