Bug #27689 GROUP_CONCAT does not report error with sql_mode="STRICT_ALL_TABLES"
Submitted: 6 Apr 2007 19:47 Modified: 6 Apr 2007 21:31
Reporter: Kevin Regan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Errors Severity:S2 (Serious)
Version:5.0.37 OS:Linux
Assigned to: CPU Architecture:Any

[6 Apr 2007 19:47] Kevin Regan 
Description:
When setting "sql_mode" to "STRICT_ALL_TABLES", GROUP_CONCAT() should produce an error when it attempts to create a string longer than "group_concat_max_len".  However, I am seeing only a warning being generated.
 

How to repeat:
The following test creates a table with a large number of strings and then attempts to GROUP_CONCAT() them together:

SET SESSION group_concat_max_len=1024;
SET SESSION sql_mode='STRICT_ALL_TABLES';

DROP TABLE IF EXISTS my_foo_table;

CREATE TABLE my_foo_table(s varchar(128));

DROP PROCEDURE IF EXISTS fill_my_foo_table;

DELIMITER //

CREATE PROCEDURE fill_my_foo_table
()
BEGIN
    DECLARE i INTEGER;

    SET i = 0;

    WHILE i < 1000 DO
        INSERT INTO my_foo_table(s) VALUES(CONCAT("This is string #", i));
        SET i = i + 1;
    END WHILE;
END//

DELIMITER ;

CALL fill_my_foo_table;

SELECT @@session.group_concat_max_len, @@session.sql_mode;
SELECT CHAR_LENGTH(GROUP_CONCAT(s)), SUM(CHAR_LENGTH(s)) FROM my_foo_table;

Suggested fix:

GROUP_CONCAT() should generate an error when the result would be larger than "group_concat_max_len" and "sql-mode" is set to "STRICT_ALL_TABLES".
[6 Apr 2007 19:51] Kevin Regan 
I get the same result if "Engine=InnoDB" for the "my_foo_table" table.
[6 Apr 2007 20:40] Giuseppe Maxia 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

STRICT_ALL_TABLES will kick if you are modifying something in a table. if you try to insert the result of your last query into a table, strict_all_table will refuse it.

drop table if not exists t2;
create table t2 (x int, y int);

INSERT INTO t2 
    SELECT CHAR_LENGTH(GROUP_CONCAT(s)), SUM(CHAR_LENGTH(s)) 
    FROM my_foo_table;
ERROR 1260 (HY000): %d line(s) were cut by GROUP_CONCAT()
[6 Apr 2007 21:31] Kevin Regan 
Thanks for the response.  I understand the behavior now.

By the way, is there a bug associated with the "%d line(s)" error message that is displayed?
[2 Sep 2010 9:35] MySQL Verification Team 
Kevin, yes we have bug #56473 for the %d in the error.