List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 17 2011 3:06am
Subject:Re: User defined Variables
View as plain text  
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
Thread
User defined VariablesKailash R16 Oct
  • Re: User defined VariablesDan Nelson16 Oct
    • Re: User defined VariablesKailash R16 Oct
      • Re: User defined VariablesDan Nelson17 Oct
        • Re: User defined VariablesKailash R17 Oct