From: Dan Nelson
Date: October 17 2011 3:06am
Subject: Re: User defined Variables
List-Archive: http://lists.mysql.com/mysql/226073
Message-Id: <20111017030648.GC47386@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Oct 16), Kailash R said:
> Nice input Dan. Let me run some checks. My query is as follows:
> 
> select group_concat(Field1), field2, field3 from table1 group by field2, field3 into str;
> @sql = concat("select blah ... where field1 in ' ,str);
> prepare stmt from @sql;
> execute stmt;
> deallocate prepare stmt;

Did you maybe get a warning on your first SELECT statement?  On a test table
of dictionary words:

mysql> select group_concat(word) from words into @a;
Query OK, 1 row affected, 1 warning (0.70 sec)

mysql> show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 146 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> select length(@a);
+------------+
| length(@a) |
+------------+
|       1024 |
+------------+
1 row in set (0.01 sec)


From the documentation, GROUP_CONCAT has a default 1024-byte limit:

http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

    The result is truncated to the maximum length that is given by the
    group_concat_max_len system variable, which has a default value of 1024. 
    The value can be set higher, although the effective maximum length of
    the return value is constrained by the value of max_allowed_packet.  The
    syntax to change the value of group_concat_max_len at runtime is as
    follows, where val is an unsigned integer:

    SET [GLOBAL | SESSION] group_concat_max_len = val;

-- 
	Dan Nelson
	dnelson@stripped