Bug #64338 Variable assigment should not output results
Submitted: 15 Feb 2012 12:53 Modified: 16 Feb 2012 12:37
Reporter: Roberto Caiola Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Parser Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: assignment, hide, Output, query, recordsets, results, suppress, variable

[15 Feb 2012 12:53] Roberto Caiola
Description:
Variable assigment should not output results.

In the example, only the instruction "SELECT @ids as ids;" should output results.

How to repeat:
SET @ids = '';

SELECT @ids := CONCAT(@ids,CAST(id_agency AS CHAR),',') as id
FROM (
SELECT 1 as id_agency
UNION
SELECT 2 as id_agency
UNION
SELECT 3 as id_agency
) as agencies;

SET @ids := LEFT(@ids,LENGTH(@ids)-1);

SELECT @ids as ids;
[15 Feb 2012 13:56] Valeriy Kravchuk
This is what I see:

mysql> SET @ids = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @ids := CONCAT(@ids,CAST(id_agency AS CHAR),',') as id
    -> FROM (
    -> SELECT 1 as id_agency
    -> UNION
    -> SELECT 2 as id_agency
    -> UNION
    -> SELECT 3 as id_agency
    -> ) as agencies;
+--------+
| id     |
+--------+
| 1,     |
| 1,2,   |
| 1,2,3, |
+--------+
3 rows in set (0.07 sec)

mysql> SET @ids := LEFT(@ids,LENGTH(@ids)-1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @ids as ids;
+-------+
| ids   |
+-------+
| 1,2,3 |
+-------+
1 row in set (0.00 sec)

So, SET does NOT output results (as it should not), while SELECT output results (as it should), no matter what assignments you do in it. What is the problem here?
[15 Feb 2012 14:57] Roberto Caiola
I think variable assignments should not output an query recordset.

See the following MS SQL Server query example:
------------------------------------
declare @i varchar (50);

select @i = 1;

select @i;
------------------------------------

The query example only generates only one recordset in the query.

The variable assignment does not generate any kind of output, as expected.
[16 Feb 2012 10:28] Valeriy Kravchuk
Maybe you should just use DO (see http://dev.mysql.com/doc/refman/5.5/en/do.html) instead of SELECT then?
[16 Feb 2012 11:44] Roberto Caiola
The SQL statement using DO does not work the same way as SELECT.

It seems that I cannot use FROM when using DO.

See the example:

SET @ids = '';

DO @ids := CONCAT(@ids,CAST(id_agency AS CHAR),',')
FROM (
SELECT 1 as id_agency
UNION
SELECT 2 as id_agency
UNION
SELECT 3 as id_agency
) as agencies;

SET @ids := LEFT(@ids,LENGTH(@ids)-1);

SELECT @ids as ids;
[16 Feb 2012 12:26] Valeriy Kravchuk
Why not just to go one extra step further:

mysql> SET @ids = '';
Query OK, 0 rows affected (0.00 sec)

mysql> DO @ids := (select CONCAT(@ids,CAST(id_agency AS CHAR),',')
    -> FROM (
    -> SELECT 1 as id_agency
    -> UNION
    -> SELECT 2 as id_agency
    -> UNION
    -> SELECT 3 as id_agency
    -> ) as agencies);
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> SET @ids := LEFT(@ids,LENGTH(@ids)-1);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT @ids as ids;
+------+
| ids  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
[16 Feb 2012 12:35] Roberto Caiola
Your work-around solution with "DO" does not work because NULL is returned, instead of "1,2,3,".
[16 Feb 2012 12:36] Valeriy Kravchuk
OK, not one step, maybe several... Like this:

mysql> DO @ids := (select GROUP_CONCAT(CAST(id_agency AS CHAR))
    -> FROM (
    -> SELECT 1 as id_agency
    -> UNION
    -> SELECT 2 as id_agency
    -> UNION
    -> SELECT 3 as id_agency
    -> ) as agencies);
Query OK, 0 rows affected (0.00 sec)

mysql> select @ids as ids;
+-------+
| ids   |
+-------+
| 1,2,3 |
+-------+
1 row in set (0.00 sec)

In any case, I do not see any reason to fix anything. Existing features allows to solve the problem, IMHO.
[16 Feb 2012 12:37] Roberto Caiola
It there any server configuration to disable variable assignment output?